Error Handling in Query

[mabris]

I am working on a database that has result values for testing stored as a string. The actual result can be either a string "Pass/Fail" or a number.
This has been working fine until when I'm making reports. I have a reports form which allows a user to enter a value and the choose comparison operators (<,>...). This works great if the table field type is a number, but when it is a string, 3.5 >25, etc.
I want to be able to differentiate between string and numerical values in a query. I have been able to create a column in the query that performs a CDbl conversion on the result value and returns either a number or #Error, depending on the result type. Lets just call the result of the conversion ConversionResult My original idea was to be able to exclude result values with a ConversionResult of #Error when my query is requested to do a numerical comparison operation (<,>...), but include them when the operation is "=".

My problem is that any attempts at reading if ConversionResult is #Error produces #Error itself. Even IsError(ConversionResult) produces #Error. How can I check if ConversionResult is #Error without having the check be #Error as well.

Even better, can anyone think of a good way to handle the larger issue of string and numbers being stored as strings and then differentiating them for numerical comparisons.

 

[PHV]

You may play with the Val function instead of CDbl.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mabris]

Thanks for the tip! Unfortunately Val, didn't work "out of the box" because result values of zero (0.00) returned the same as text values, but I figured out how to spot string characters in the result. Duh:
[ReplicateValue] Like ("*[!0-9,.]*")