I have a query where i am trying to update field by taking the left number of characters before a space. I'm not very good with Access VBA, so I'm trying to do this via a query.
my data is a list of SKUs, where I want to update the same field (sku) with a shorter SKU number, by using the Left$ function along with the InStr function to take all characters to the left of a space in the number.
test sku
E349CAJ6 OBROBRO
E357CAJ6 OBROSID
E329CAJ6 OWHIBRO
E358CAJ6 ONO SID
Note that the space isn't always in position 9, sometimes it varies. I was trying to use the following Query update value: Left$([IMPORT - EFF ORDERS]![SKU], InStr([IMPORT - EFF ORDERS]![SKU]," ",1))
The InStr, identifies the starting position based on the space, to use for the Left function.
The SKU field is a Short Text type field.
However, when I run the query, I get a "Type Conversion" error and none of the records will update.
I have wracked my brain to try to figure this one out and would appreciate an expert's fresh eyes on it.
Thank you so much in advance !
my data is a list of SKUs, where I want to update the same field (sku) with a shorter SKU number, by using the Left$ function along with the InStr function to take all characters to the left of a space in the number.
test sku
E349CAJ6 OBROBRO
E357CAJ6 OBROSID
E329CAJ6 OWHIBRO
E358CAJ6 ONO SID
Note that the space isn't always in position 9, sometimes it varies. I was trying to use the following Query update value: Left$([IMPORT - EFF ORDERS]![SKU], InStr([IMPORT - EFF ORDERS]![SKU]," ",1))
The InStr, identifies the starting position based on the space, to use for the Left function.
The SKU field is a Short Text type field.
However, when I run the query, I get a "Type Conversion" error and none of the records will update.
I have wracked my brain to try to figure this one out and would appreciate an expert's fresh eyes on it.
Thank you so much in advance !