Evaluate #Error??? (1 Viewer)

[gblack]

Does anyone know if there's a way to utilize the "#Error" message that comes up in queries as a result of data type issues?

Meaning is there a way to filter on or omit the data that results in a #Error?

I have a bunch of string values that denote, what they call, an Assetnum... although it's not a number at all it's actually a string.

Some of the values might look like: 004567, 123LLCC96, 12345-98-0... etc... etc... However, the vast majority of items are sequential numbers like 1110987654, 1110987655, 1110987656 etc..

So what I want to do, in my query, is convert the majority of Asstenums to a long integer, in order to evaluate which of these items have only number values and which do not...

I tried Clng(Assetnum) As AssetLong and this produced the #Error for those items that don't convert... but if I try to filter by > 1000... the query errors out...

My question is: Is the any way to evaluate the #Error in my query to either exclude all #Error(s) or include only #Error(s)? without creating more than one object in MS Access?

 

[Ranman256]

your field type must be set to text to have the LLCC, so are you performing math on this field?
What are you wanting to do with this field?
Is there a function you are using?

 

[plog]

No, an #Error isn't evalulated to anything, so when you try and apply criteria to it, it just breaks the query. Instead you set up your WHERE clause to exclude the bad data before it turns into #Errors.

If you only want to show records that have numeric Assetnums you would use this WHERE clause:

WHERE IsNumeric(Assetnum)=True