#error in calculated query field

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

I can't figure this one out:

I have a text field that contains either:
1. a double
2. a string beginning with a comparison operator followed by a double
3. null

I have created two functions that will nicely convert either (1)
or (2) above into two fields,
the operator (if there is one) and the number.

However, if the text field is null, I receive #error in both of
my new fields. I have tried returning an empty string from the
function that fills the operator field, or a null from the
function that fills the numeric field, but I still
get #error. THIS IS DRIVING ME NUTS!!

Here are the two little functions:

Function Ext_Qual(strField As String) As String
Ext_Qual = ""
If Not IsNull(strField) Then
If Not IsNumeric(strField) Then
Ext_Qual = Left(strField, 1)
End If
End If
End Function


Function Ext_Conc(strField As String) As Double
Ext_Conc = Null
If Not IsNull(strField) Then
If IsNumeric(strField) Then
Ext_Conc = Val(strField)
Else
Ext_Conc = Val(Right(strField, Len(strField) - 1))
End If
End If
End Function


Thank you,
Kate
 
Sorry, that second function should return a variant, not a
double, or an invalid use of null occurs.
 
I've solved the problem. When calling the functions from within
the query,
I need to use nz thus:
New_fld: Ext_qual(nz([field],""))
as both functions are expecting a string.

Thanks for listening,
Kate
 
Back
Top