Problem: Insert an empty date to a date field

  • Thread starter Thread starter Min
  • Start date Start date
M

Min

Hi, using a textbox to accept a date to insert to a date field, if there is
a date in the textbox, say, #2004-11-27# it is fine. However, if the textbox
is empty, "", how to insert "an empty date" into the date field (no date in
that field) ? Because this is a query:
strSQL = "Insert Into Table1 (EndDate) Value (#" & txtEndDate & "#)"
How to write the query?
Thanks!
 
Not sure, but you might try

"INSERT Into Table1 (EndDate) Value (Null)"

or more generically

"INSERT INTO Table1 (EndDate) Value (" & IIF(IsNull(txtEndDate), "Null",
Format(TxtEndDate,"\#mm/dd/yyyy\#") & ")"
 
Hi, using a textbox to accept a date to insert to a date field, if there is
a date in the textbox, say, #2004-11-27# it is fine. However, if the textbox
is empty, "", how to insert "an empty date" into the date field (no date in
that field) ? Because this is a query:
strSQL = "Insert Into Table1 (EndDate) Value (#" & txtEndDate & "#)"
How to write the query?
Thanks!
Click to expand...

I'd suggest using an IF to handle the two alternatives:

If IsDate(Me!txtEndDate) Then
strSQL = "Insert Into Table1 (EndDate) Values (#" & txtEndDate & "#)"
Else
strSQL = "Insert Into Table1 (EndDate) Values (NULL)
End If

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks for reply!
I would like to use the more generic way as the query actually a little
long, but unfortunately, if the txtDate is null, the format() function won't
pass syntax, neither directly use #null#.
 
Thank you!
As the query is long, using If ... Else... makes the code longer, but I
think this is the only way to insert null or date into a date field.
 
Thank you!
As the query is long, using If ... Else... makes the code longer, but I
think this is the only way to insert null or date into a date field.
Click to expand...

If you're inserting multiple fields, the code would be simplified a
bit if you simply skip over the date field if the textbox is NULL. If
you don't insert anything at all, it will stay Null.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top