null in date field

[darkwraith]

Hi, I have to insert/update a record in my table.
Sometimes it happens that the value of some fields are null.
I use iif(isnull(myfield), "", myfield.value) in my sql instruction when the field type is string.
It doesn't work with dates (an error occurs: type conversion error).
Which is the correct statement?
Thanx,
Elena

 

[LarryDeLaruelle]

Elena:

Try

iif([myfield] is null, "", myfield.value)

The syntax for the IsNull function is slightly different in an immediate If statement.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[JeremyNYC]

I would use the nz() function. It does the same thing, but it's a lot prettier.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[darkwraith]

Thanks, I'll try with is null and nz() function instead of my code,
Elena

 

[TonyJollans]

Hi darkwraith,

Jeremy is right. Use the Nz function - it's really just a neater way of saying iif(isnull...

But I think the reason for your problem is that you are supplying an empty string as the 'default' date when you should be providing a date.

Enjoy,
Tony