Null Date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DS - 31 Aug 2006 15:27 GMT

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") & ")"

Reply to this Message

Jeff L - 31 Aug 2006 16:21 GMT

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!

> 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.
[quoted text clipped - 5 lines]
> "VALUES(" & Forms!Form1!TxtID & ", " & _
> "" & Nz("#" + Me.TxtDate + "#", "Null") & ")"

Reply to this Message

Dirk Goldgar - 31 Aug 2006 16:30 GMT

> Try this instead
> CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 4 lines]
> if Me.TextDate was null, you would still have "##" as the text you
> were evaluating. That string of course is not null.

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\#") & _
");"

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

DS - 31 Aug 2006 17:56 GMT

>>Try this instead
>>CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 24 lines]
> Format(Me.TxtDate, "\#mm/dd/yyyy\#") & _
> ");"

Better yet!
Thanks Dirk!
DS

Reply to this Message

DS - 31 Aug 2006 18:06 GMT

>>> Try this instead
>>> CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 28 lines]
> Thanks Dirk!
> DS

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

Reply to this Message

Dirk Goldgar - 31 Aug 2006 18:21 GMT

>>> CurrentDb.Execute _
>>> "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 7 lines]
> "VALUES(" & Forms!Form1!TxtID & ",IIF(IsNull(Me.TxtDate),"Null", " & _
> "Format(Me.TxtDate, "\#mm/dd/yyyy\#")");"

That's not what I posted. I suggest you copy and paste exactly what I
posted, and report on the results of that.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

DS - 31 Aug 2006 18:55 GMT

>>>> CurrentDb.Execute _
>>>> "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 10 lines]
> That's not what I posted. I suggest you copy and paste exactly what I
> posted, and report on the results of that.

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

Reply to this Message

Dirk Goldgar - 31 Aug 2006 19:15 GMT

>>>>> CurrentDb.Execute _
>>>>> "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 20 lines]
>
> Is it punctuation?

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\#")) & _
");"

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

DS - 31 Aug 2006 19:33 GMT

>>>>>> CurrentDb.Execute _
>>>>>> "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 31 lines]
> Format(Me.TxtDate, "\#mm/dd/yyyy\#")) & _
> ");"

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

Reply to this Message

DS - 31 Aug 2006 17:55 GMT

> Try this instead
> CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
[quoted text clipped - 16 lines]
>>"VALUES(" & Forms!Form1!TxtID & ", " & _
>>"" & Nz("#" + Me.TxtDate + "#", "Null") & ")"

Thanks, it works! The thing was driving me crazy!
DS

Reply to this Message