If record start with "M" return x, otherwise y 2

[Smoothcat74]

Ok, I apologize for having to start a thread for this, but I searched high and low and couldn't find the answer in the other postings. This is probably super simple, but unfortunately I'm super simple and can't figure it out.

I have a field in Access that contains order numbers of varying lengths. If the order number starts with "M", then I want VBA to truncate the field to the first 8 characters starting from the left. If the order number does not start with "M", then I want it to truncate only the first 7.

Please help?

 

[PHV]

NewOrderNumber = Left([Order number], IIf(Left([Order number], 1) = "M", 8, 7))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Smoothcat74]

Can I drop this into a SQL query? I tried but it gave me an error. Here's the syntax I attemtped:

DoCmd.RunSQL "UPDATE Shipping_Manifest SET Shipping_Manifest.PKGID = Left([PKGID], iif(left[PKGID],1) = 'M',8,7)"

 

[PHV]

it gave me an error
Which error ?
Anyway you missed the last closing parenthese.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Smoothcat74]

First it gives me the following error:

Syntax error (missing operator) in query expression 'Left([PKGID], iif(left[PKGID],1)= 'M',8,7)'.

I think the number of parenthses balance out. After I click "OK", it then presents a run-time error with the following statement:

|1 in query expression '|2'

I'm stumped.

 

[PHV]

DoCmd.RunSQL "UPDATE Shipping_Manifest SET PKGID=Left([PKGID],IIf(Left[!]([/!][PKGID],1)='M',8,7)[!])[/!]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Smoothcat74]

It certainly does. Thanks for all your help!