type error when trying to update a table field name query (1 Viewer)

Dilbert99

New member
Local time
Yesterday, 20:58
Joined
Jan 9, 2020
Messages
27
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 !
 

Attachments

  • test.accdb
    516 KB · Views: 260

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:58
Joined
Aug 30, 2003
Messages
36,118
Your arguments are out of order. Try

UPDATE [IMPORT - EFF ORDERS] SET [IMPORT - EFF ORDERS].SKU = Left$([IMPORT - EFF ORDERS]![SKU],InStr(1, [IMPORT - EFF ORDERS]![SKU]," "))
WHERE ((([IMPORT - EFF ORDERS].SKU) Is Not Null));
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:58
Joined
Sep 21, 2011
Messages
14,048
Key to this is break it down into parts.
Your instr() does not work. Try putting he INSTR() component as a new field, and you will get errors.

This works
Code:
SELECT [IMPORT - EFF ORDERS].SKU, Left([SKU],InStr([SKU]," ")) AS Expr1, InStr([SKU]," ") AS Expr2
FROM [IMPORT - EFF ORDERS]
WHERE ((([IMPORT - EFF ORDERS].SKU) Is Not Null));

Then amend to suit ONCE you have your query/functions producing the correct results.

HTH

Edit:

You also have a few records with no spaces?, so perhaps also check where Instr() >0 ?

1600713523412.png
 

Dilbert99

New member
Local time
Yesterday, 20:58
Joined
Jan 9, 2020
Messages
27
Your arguments are out of order. Try

UPDATE [IMPORT - EFF ORDERS] SET [IMPORT - EFF ORDERS].SKU = Left$([IMPORT - EFF ORDERS]![SKU],InStr(1, [IMPORT - EFF ORDERS]![SKU]," "))
WHERE ((([IMPORT - EFF ORDERS].SKU) Is Not Null));


Thank you SO much!!! that fixed it! I really appreciate it!
 

Users who are viewing this thread

Top Bottom