#error message

  • Thread starter Thread starter Chris Brotz
  • Start date Start date
C

Chris Brotz

I converted a text field to a numeric field in a query. The new field has
#error displayed for any text field that was blank. Is there a way to
convert this to a 0?

Thanks for your help!
Chris
 
It depends on the conversion method you used and the contents for the text
field. You might try:

Val(Nz(TextField,"0"))

as the conversion method. This would still have problems when textfield
couldn't be evaluated as a number. Another method

Val(IIF(IsNumeric(TextField),TextField,"0"))
 
I tried using the Nz function with the following logic:

insamt: (Val(Nz(Mid([21],4,"0"))))

to convert an insurance code like FLS100 to just 100 I need the Mid
function as well. Some fields are blank. Now all the values are 0.... do I
need to do this in separate steps?

Thanks for your help.
Chris
 
in creteria of the query under the field of interest place an iif statement
similar to this:

IIF([MyField] is null, 0, [Myfield])

It will check [MyField] for null values and replace them with 0, else it
keeps the non null value present. If the field is an expression then use the
'iif' in the formula for example

Myfield: [Thisfield] * IIF([Thatfield] is null , 0, [Thatfield]))

any help to ya?


Chris Brotz said:
I tried using the Nz function with the following logic:

insamt: (Val(Nz(Mid([21],4,"0"))))

to convert an insurance code like FLS100 to just 100 I need the Mid
function as well. Some fields are blank. Now all the values are 0.... do I
need to do this in separate steps?

Thanks for your help.
Chris

John Spencer (MVP) said:
It depends on the conversion method you used and the contents for the text
field. You might try:

Val(Nz(TextField,"0"))

as the conversion method. This would still have problems when textfield
couldn't be evaluated as a number. Another method

Val(IIF(IsNumeric(TextField),TextField,"0"))
Click to expand...
Click to expand...
 
Back
Top