iif statment for null date

[vagelisr]

Hi to all

I have this sql
sSql = "UPDATE GUN_D SET GUND_DATEAPPLICATION = #" & Format(W_GUNL_DATEAPPLICATION, "mm/dd/yyyy") & _
"#, GUND_RELEASEDATE = #" & Format(W_GUNL_RELEASEDATE, "mm/dd/yyyy") & _
"#, GUND_EXPIRYDATE = #" & Format(W_GUNL_EXPIRYDATE, "mm/dd/yyyy") & "# WHERE GUND_ID = " & W_GUND_ID

I want to make a check for this 3 days and if it is empty pass null value.
I have to use the iif statment but after 1 hour :banghead::banghead::banghead: i didn't fount the correct syntax.

Thanks and regards.

 

[pbaldy]

I would either use a recordset to update, since it won't care, or build the SQL conditionally, only adding a field if the field has a date.

 

[vagelisr]

First of all thanks for your time.
The point is i want to update all the records regardless the has or not date.

 

[pbaldy]

Doesn't really change the concept. I'd use a recordset, but you can also conditionally build SQL. That SQL can update with a date or to Null depending on the content of each textbox.

 

[The_Doc_Man]

Paul's answer is correct, but understand that there can be issues in what you are doing if you are not careful. Did you really, REALLY, REALLY want to put nulls in those date fields, or do you simply want to put some easily recognized date that won't be confused with any modern dates?

Knowingly placing a null value into a field is CERTAINLY possible, don't get me wrong. But nulls tend to be ... difficult to handle later on. Comparisons to null don't work - you have to use IsNull(field) as a test or NZ(Field, Value) to assure that the math doesn't trip on its face.

However, you CAN set a date field to CDate(0), which will make it look like either 1-Jan-1900 or 31-Dec-1899, I always forget which one. Obviously, either one is a date before the creation of computers so would be just as valid as a null date to indicate "no date available." The difference is that you CAN compare a Cdate(0) to a modern date and not have the math barf on you. (Error "Invalid use of null" comes to mind for comparing anything to a null.)