SQL - insert into

[hardy1976]

SQL - insert into [SOLVED]

Hi,

I need to use....

CurrentDb.Execute "INSERT INTO payment

to insert a record into my payment table 4 values.... 2 of which are dates that may/may not be populated on my form. Normally I would create a relationship so these are viewable, however there is a need to add these date to another table as they can change independantly (thats keeping it short!).

When I've got nulls it not working... well its not inserting a record into the payment table at all... Can anyone help me please, I've tried nz() and IIf however no joy... do I need to do something with '#' or something HELP PLEASE!!!

CurrentDb.Execute "INSERT INTO payment (jaid, [job number], [A POSTING RATE], [A POSTING DATE], [B POSTING RATE], [B POSTING DATE]) VALUES (" & Me.JAID & "," & SQLtoRun + 1 & ",'" & Me.POSTAGE_RATE_A & "','" & IIf([Me.POSTING_DATE_A] Is Not Null, Me.POSTING_DATE_A , "") & "','" & Me.POSTAGE_RATE_B & "'," & IIf([Me.POSTING_DATE_B] Is Not Null,  Me.POSTING_DATE_B , "") & ");", dbFailOnError

 

[hardy1976]

SOLVED IT!!!

If Is Null(Me.txtDOB) Then
SetVal = "Null"
Else
SetVal = "#" & CDate(Me.txtDOB) & "#"
End If

And then drop SETVAL into the insert line... job done!