IsNumeric Question As Part of Expression

[CharlesWhiteman]

I have a query in my Db which contains a value in a column such as

"1234 - ABC Company Limited"

In my query I have two expressions using the Mid function to create separate columns to give 1234 and in another column ABC Company Limited

I then want in my column with the 1234 to use IsNumeric ("Number") in the criteria but with that criteria set when I try and run the query I just get a value box pop up referring to the main data column.

Any suggestions?

 

[plog]

Yes, post your SQL.

 

[apr pillai]

Use Mid() function enveloped in Val() to convert the extracted number in text form "1234" to change into numeric.

VAL(MID([FIELDNAME],1,4))

If extracted 4 characters are not numeric digits then the VAL() function will return 0.

You can test for >0 condition in the criteria row.

 

[vbaInet]

I then want in my column with the 1234 to use IsNumeric ("Number") in the criteria but with that criteria set when I try and run the query I just get a value box pop up referring to the main data column.

Any suggestions?

You shouldn't be using that in the Criteria because it will only use one value of Number. Also, you shouldn't be using Number as the name of the field, unless you mentioned this for explanation purposes.

Put IsNumeric([Number]) in a new field in the query and enter True in the Criteria under this field. Notice that there are no quotes surrounding [Number].

 

[CharlesWhiteman]

Many thanks all for your advice and posts which is much appriciated.

 

[vbaInet]

No problemo CharlesWhiteman!