Null Date

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I'm trying to handle a Date field if it's left blank with SQL.
I've looked around but I still can't find the solution.
Any help appreciated.
Thanks
DS

CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
"" & Nz("#" + Me.TxtDate + "#", "Null") & ")"
 
Try this instead
CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIF(IsNull(Me.TxtDate), Null, "#" & Me.TxtDate "#") & ");"

The Nz statment you have would never give you a value of Null because
if Me.TextDate was null, you would still have "##" as the text you were
evaluating. That string of course is not null.

Hope that helps!
 
Jeff L said:
Try this instead
CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIF(IsNull(Me.TxtDate), Null, "#" & Me.TxtDate "#") & ");"

The Nz statment you have would never give you a value of Null because
if Me.TextDate was null, you would still have "##" as the text you
were evaluating. That string of course is not null.
Click to expand...

May I propose a small correction? You need the literal "Null" -- but
without the quotes -- to be embedded into the SQL string, not the value
Null itself. Try this:

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIF(IsNull(Me.TxtDate), "Null", "#" & Me.TxtDate & "#") & ");"

Although it's a good idea to explicitly format the date to avoid
ambiguity:

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIF(IsNull(Me.TxtDate), _
"Null", _
Format(Me.TxtDate, "\#mm/dd/yyyy\#") & _
");"
 
Jeff said:
Try this instead
CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIF(IsNull(Me.TxtDate), Null, "#" & Me.TxtDate "#") & ");"

The Nz statment you have would never give you a value of Null because
if Me.TextDate was null, you would still have "##" as the text you were
evaluating. That string of course is not null.

Hope that helps!
Click to expand...
Thanks, it works! The thing was driving me crazy!
DS
 
Dirk said:
May I propose a small correction? You need the literal "Null" -- but
without the quotes -- to be embedded into the SQL string, not the value
Null itself. Try this:

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIF(IsNull(Me.TxtDate), "Null", "#" & Me.TxtDate & "#") & ");"

Although it's a good idea to explicitly format the date to avoid
ambiguity:

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIF(IsNull(Me.TxtDate), _
"Null", _
Format(Me.TxtDate, "\#mm/dd/yyyy\#") & _
");"
Click to expand...
Better yet!
Thanks Dirk!
DS
 
DS said:
Better yet!
Thanks Dirk!
DS
Click to expand...
Dirk, I see and understand what you are saying, but I can't seem to get
the thing to work. Heres what I have...its all in Red!

CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ",IIF(IsNull(Me.TxtDate),"Null", " & _
"Format(Me.TxtDate, "\#mm/dd/yyyy\#")");"

Thanks
DS
 
DS said:
CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ",IIF(IsNull(Me.TxtDate),"Null", " & _
"Format(Me.TxtDate, "\#mm/dd/yyyy\#")");"
Click to expand...

That's not what I posted. I suggest you copy and paste exactly what I
posted, and report on the results of that.
 
Dirk said:
That's not what I posted. I suggest you copy and paste exactly what I
posted, and report on the results of that.
Click to expand...
Dirk I copied and pasted your above code...it's in red. So I moved it
to 3 lines...this is what I have...still in red.

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ",IIF(IsNull(Me.TxtDate),"Null",
Format(Me.TxtDate, "\#mm/dd/yyyy\#")");"

Is it punctuation?
Thanks
DS
 
DS said:
Dirk I copied and pasted your above code...it's in red. So I moved it
to 3 lines...this is what I have...still in red.

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ",IIF(IsNull(Me.TxtDate),"Null",
Format(Me.TxtDate, "\#mm/dd/yyyy\#")");"

Is it punctuation?
Click to expand...

Your transformation was messed up, but the original fault was mine, as I
left out a closing parenthesis for the IIf() function call. Try this:

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!txtID & ", " & _
IIf(IsNull(Me.TxtDate), _
"Null", _
Format(Me.TxtDate, "\#mm/dd/yyyy\#")) & _
");"
 
Dirk said:
Your transformation was messed up, but the original fault was mine, as I
left out a closing parenthesis for the IIf() function call. Try this:

CurrentDb.Execute _
"INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!txtID & ", " & _
IIf(IsNull(Me.TxtDate), _
"Null", _
Format(Me.TxtDate, "\#mm/dd/yyyy\#")) & _
");"
Click to expand...
Thanks Dirk!
It works most brilliantly!!!

CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
IIf(IsNull(Me.TxtDate), "Null", _
Format(Me.TxtDate, "\#mm/dd/yyyy\#")) & ");"

DS
 
Back
Top