Access Date Field Clearing 1

[rkfox]

How does one clear a Date/Time field on an Access data base. IE. there is a date in the field already, the user clears the field on the related form and wants to clear the data base field. I can recognize that they cleared it on the form but what do i have to send to the data base to clear the entry there?

ie. gRS("Date1&quot = ???????

Thanks

 

[930driver]

Set it to Null "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[rkfox]

i have tried that but get a "data type mismatch in criteria expression" error

what would the code look like to change it to null

I am using a SQL Update command like this to construct the SQL command. The bottom half works if I have a date in the form field but the top half gives me the data type mismatch error.

If LEN(Request.Form("ConcurDate&quot) = 0 then

line below doesn't work
strSQL = strSQL & ", ConCurDate = '" & Null & "'"
Else
this line works to set the date field
strSQL = strSQL & ", ConCurDate = '" & EncodeSQL(tmpConCur) & "'"
End If


Thanks

 

[930driver]

For your example:

gRS("Date1&quot = ???????

it would be:

gRS("Date1&quot = Null

For your other example:

strSQL = strSQL & ", ConCurDate = '" & Null & "'"

it would be:

strSQL = strSQL & ", ConCurDate = Null"

Null is not a constant so you don't want to concatenate it like that. Also, when you surround it with single quotes it will be intrepreted as a string which it is not.

A very elementary update query in Access might look like the following:

UPDATE tblEmployeeExpenses SET tblEmployeeExpenses.EDate = Null;

This is about as far as I can help you. My shtick is Access, not ASP so the best I can tell you is that an unitialized Date value in Access should be Null.

Hope that helps.

"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"