Syntax Error in RUNSQL command

[MGP]

I'm trying to do a simple insert using a2k and can't seem
to figure out what the syntax error is ... help!

DoCmd.RunSQL "INSERT INTO Orders (OrderID, PaymentDate,
EntryDate) " & _
"VALUES (" & Me.OrderID & ", " &
Me.PymtDate & ", " & Date & ");"

Both date fields are defined as general date and non
mandatory. The PymtDate is null ... what am I doing wrong?

 

[Damien]

If you're inserting the data into an Access table, you'll
need to wrap the date values in # signs before you insert
them. If it's a SQL Server table, then youse the
ordingary text "'" wrapper. ie

DoCmd.RunSQL "INSERT INTO Orders (OrderID, PaymentDate,
EntryDate) " & _
"VALUES (" & Me.OrderID & ", #" &
Me.PymtDate & "#, #" & Date & "#);"

You'll have to very careful with your nulls; ie if the
PymtDate field is null, you need to insert Null in the
table, not '#Null#'.

Hope that helps !


Damien

 

[mgp]

Damien,

Thx for the reply ... I think you may be on to
something ... how quickly I forget about the #'s. The
problem occurs when it is a null, however I'm not using
the #'s when it is null yet it still gives me a syntax
error.

Here is the statement before the Docmd.

If IsNull(PayDate) Then PymtDate = Null Else PymtDate
= "#" & PayDate & "#"

Any suggestions ??

mgp