Possible bug using Clng or iif function

[roy fisher]

I've linked in a table (in A97) into a 2K Access database.
This table contains a text field that I need as a long.

Simple enough, I thought, and created an expression in a
query using clng. First problem was that some records were
blank in that field so I got errors. I then changed the
expression to
iif(isnull([op number]),0,clng([op number])

and this seemed to work just fine with the values aligned
to the right hand side of the cell as numeric values do.

The trouble is that when I put in an expression in the
criteria cell such as >10 or even 10 and run the query I
get the values displayed correctly for a second or two
until it errors out with the message
Data Type mismatch in criteria expression

This can't be the case as the query builder accepts the
values as a long in the SQL WHERE but doesn't when the
query runs.

I've had a look on the web but haven't found any help as
yet. One thing my Access is on SR-1 but as I have to rely
on an IT dept to upgrade to SR-2 I need to convince them
first. Can anyone help?

Thanks

Roy Fisher

 

[Dale Fye]

my guess is that you have an entry that contains an empty string "",
which clng will not convert either.

TRY: iif(LEN(TRIM([op number]) & "") = 0, 0, clng([op number]))

--
HTH

Dale Fye


I've linked in a table (in A97) into a 2K Access database.
This table contains a text field that I need as a long.

Simple enough, I thought, and created an expression in a
query using clng. First problem was that some records were
blank in that field so I got errors. I then changed the
expression to
iif(isnull([op number]),0,clng([op number])

and this seemed to work just fine with the values aligned
to the right hand side of the cell as numeric values do.

The trouble is that when I put in an expression in the
criteria cell such as >10 or even 10 and run the query I
get the values displayed correctly for a second or two
until it errors out with the message
Data Type mismatch in criteria expression

This can't be the case as the query builder accepts the
values as a long in the SQL WHERE but doesn't when the
query runs.

I've had a look on the web but haven't found any help as
yet. One thing my Access is on SR-1 but as I have to rely
on an IT dept to upgrade to SR-2 I need to convince them
first. Can anyone help?

Thanks

Roy Fisher

 

[John Spencer (MVP)]

You might also try using the IsNumeric function. That will handle nulls, empty
strings, and strings with non-numeric characters (for instance, where someone
typed an OH vice a ZERO).

IIF(IsNumeric([opNumber]),Clng(OpNumber),0)