avoid #error

[geebee]

hi,

i have a query which contains a field to convert text to number:
CLng(Mid([fieldname]], 1, 8))

However, I am getting an #error for some of the rows in the query because
either the fieldname contains something like 'Led24 or doesnt contain a value
at all. how can i amend to solve this?


thanks in advance,
geebee

 

[John Spencer]

I would try

IIF(IsNumeric(Mid([fieldname],1,8)),CLng(Mid([fieldname],1,8)),Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Allen Browne]

i have a query which contains a field to convert text to number:
CLng(Mid([fieldname]], 1, 8))

However, I am getting an #error for some of the rows in the query
because either the fieldname contains something like 'Led24 or
doesnt contain a value at all. how can i amend to solve this?

CLng() cannot handle Nulls, so try an expression like this:
IIf([fieldname] Is Null, Null, Val(Left([fieldname], 8)))

That returns a Double; if you need a Long Integer:
IIf([fieldname] Is Null, Null, CLng(Val(Left([fieldname], 8))))