converting a null value to an empty string?

[nickjar2]

I am developing an app that uses a function to update a table.
When first inserting, i set all fields on the form to "". However, when i come to edit a record that has a blank value, and try to pass this null value to a function (which is expecting a numeric value), I get a Type Mismatch error. So i created a little routine that scanned each control,and if a null was found, set its value to "". This doesn't work.

What is the best way of dong this???

Cheers for any help! Nick (Everton Rool OK!)

 

[Trevoke]

I wish I was more fluent in access - someone will tell you exactly how it works, but I believe there is an "iz" function in access that returns an empty string if you have a null one there.. Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam.

 

[oharab]

Close Trevoke!
the function is nz

it converts a null string into anything you want it to:

nz([fieldname]) will convert all null strings into empty strings. nz([fieldname],0) will convert all empty strings into 0. nz([fieldname],"Nothing Here. Nada, empty zilch&quot will convert any null strings to the text string "Nothing Here. Nada, empty zilch"

HTH

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[nickjar2]

The nz function was what I tried initially, but I got an error. That is when i thought i could check for nulls first.

Maybe i could try nz(txtField,0). I will give that a go in work tomorrow. I kept trying nz(field,"&quot. That would work fine for a dtring, but cause my func is expecting a numeric value, that must be why it is falling over. The stange thing is though, when i initially create the new record (by setting all text fields to "&quot it works when i pass this empty value to my function. Maybe somebody could enlighten me.

Cheers! Nick (Everton Rool OK!)