Convert Character Field Value to Integer

[EdAROC]

I have to parse a character field and convert the result to an integer. Help.

So far I have been able to parse the field and end up with a character string of the numeric characters. Ex: Z12345-1--1 is parsed to 12345.

FYI - Once I do this I will use this query to link to a table where the primary key is the parsed value (int).

 

[rjoubert]

Wrap a CInt around your string value.

CInt("12345")

 

[EdAROC]

Maybe this bit of information needs to be added:
Not every record will contain a value, a value is always of the format Z12345-1--. If there is no ITEM_NO the field is Null (I checked).

Here is my latest expression:
SPEC_NO: IIf([ITEM_NO] Is Null, Null, CInt(Replace(Left([ITEM_NO], InStr([ITEM_NO], "-")-1), "Z", "")))

The records with a value show "#Error". The Null fields remain Null.

When I have: Replace(Left([ITEM_NO], InStr([ITEM_NO], "-")-1), "Z", "")) the values show OK - it's just they are a character string.
Hmmm.

 

[rjoubert]

Strange...I just added ran a test, using the same expression you posted (with the CInt), and it worked fine. Are you sure you have it exactly as you posted?

 

[PHV]

Replace CInt( with Val(

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EdAROC]

Val(...) worked - the values are displaying right justified.

Thanks.

BTW - rjoubert I copied/pasted to make sure you got what I was using.

Went to my "cheat sheet website for functions" -

http://www.techonthenet.com/access/functions/index.php

and looked up Val(...). That's what I needed.

What does CInt actually do. Above website states it returns the Integer of a value (does "value" mean numeric or alphanumeric?) [.5 rounds down!!!].

Thanks again!! Really helps me with this project I'm working on.