Return Alphanumeric values only

[Sinoe]

Hi smart people,

Can anyone tell me how to write an expression/criteria in a query that will return only ID numbers with alpha numeric values. Example: My Id field contains both numeric 479621, 680530 and alpha numeric 132NAV100, 174NAV26a values. I want to run the query to return only the alpha numeric values.

Thanks.

P.S. I do not know SQL nor VBA.

 

[jdraw]

What , if anything, do you want returned from 132NAV100?

 

[Sinoe]

Thanks for responding so quickly. 132NAV100 is just one of the ID numbers in the table. I want to run the query to return ID numbers like this and not ID number that just have numbers, like 12345 or 7891, etc.

Thank you.

 

[jdraw]

Try

IsNumeric(ID)

see IsNumeric

 

[jdraw]

Try

IsNumeric(ID)

see IsNumeric

 

[Sinoe]

Thanks jdraw.

I tried IsNumeric but it did not work. It actually returned no result.

 

[jdraw]

Please post the SQL of the query you tried.
Go to query designer, click sql view, copy and paste what you see.

 

[Sinoe]

Here is the SQL statement. This returns result but include the numeric as well as the alpha numeric id numbers.

SELECT [Navigator data MAY 7-13].[Last Name], [Navigator data MAY 7-13].[First Name], [Navigator data MAY 7-13].[Assister ID], [Navigator data MAY 7-13].[Assister Org Name]
FROM [Navigator data MAY 7-13]
WHERE ((([Navigator data MAY 7-13].[Assister Org Name]) Is Not Null)) OR ((([Navigator data MAY 7-13].[Assister ID])=IsNumeric("ID")));

 

[jdraw]

I think this is what you want. However, the use of NOT gets you into
DeMorgans Law

SELECT [Navigator data MAY 7-13].[Last Name]
	,[Navigator data MAY 7-13].[First Name]
	,[Navigator data MAY 7-13].[Assister ID]
	,[Navigator data MAY 7-13].[Assister Org Name]
FROM [Navigator data MAY 7-13]
WHERE NOT (
		([Navigator data MAY 7-13].[Assister Org Name]) IS NULL
		OR (IsNumeric([Navigator data MAY 7-13].[Assister ID]))
		);

Do you want records where
OrgName is not Null and AssisterID is NOT Numeric?

Try the above sql. If it doesn't give hat you need, then change the OR to AND.

 

[Sinoe]

jdraw,

Thank you so much. It worked!!!!

 

[jdraw]

Glad you have it sorted. Happy to help.

 

[Galaxiom]

IsNumeric is a VBA function so processing it would require testing every record. Performance would be better by sticking with SQL.

This will return any record where the field holds alpha characters.

WHERE [Assister ID] Like "*[a-z]*"