SQL INSERT Date is Null

[DSTR3]

I'm having a problem inserting a record if the date field empty, It only works if there is a Date in the Me.TxtDate, otherwise I get an Error.

I tried this but, it doesn't work.

Thanks
DS


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

 

[ProgramError]

Try this in the Microsoft:access Queries and JET SQL forum, you may get a reply.


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[TheAceMan1]

How are ya DSTR3 . . .

Perhaps this:

[blue]   Dim SQL As String
   
   SQL = "INSERT INTO Table1 (ID,DateF) " & _
         "VALUES(" & Forms!Form1!TxtID & ", " & _
                 Nz(Me.TxtDate, "Null") & ")"
   CurrentDb.Execute SQL[/blue]

See Ya! . . . . . .

 

[PHV]

#" & Me.TxtDate & "#" is NEVER null !
CurrentDb.Execute "INSERT INTO Table1 (ID,DateF) " & _
"VALUES(" & Forms!Form1!TxtID & ", " & _
"" & Nz("#" [!]+[/!] Me.TxtDate [!]+[/!] "#", "Null") & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DSTR3]

Aceman, it almost works....it returns time, not date.

Phv, Works perfectly!

Thanks
DS