Grabbing numbers from within a text

[melika]

Hi,

I have a table with a field, [Product]. The Product data comes in the form, # Description. Examples: 00100 Strawberry, 001234 Kiwi, 012345 Apple

The info I want to pull from that field is the 100, 1234 and 12345, then I want to add on three 000 at the end, so the final result is 100000, 1234000, 12345000.

At first I had the following, but then I realized it doesn't produce the right results for all the products.

Format(Left([Product], 5) & "000", "General Number") AS [Item]

Thanks in advance!!

 

[KenHigg]

(cint(left(myFld, 6)))*1000

???
ken

 

[melika]

I thought it worked. But then my query said "Invalid Use of Null"

I'm not sure why... I eliminated other possible causes of the Null and came up with CInt function as causing the error message. I forgot to mention some of the Product values are Blank. Would that cause CInt to error?

 

[Rabbie]

Use the NZ function convert any null fields to Zero. This will stop it from erroring

 

[melika]

Thanks for the NZ tip... where does it go?

(cint(left(Nz([Product], '0'), 6)))*1000 ?

 

[Rabbie]

Thanks for the NZ tip... where does it go?

(cint(left(Nz([Product], '0'), 6)))*1000 ?

That should work for you

 

[melika]

Yay, it did! Thank you!