Null dates in Insert Into statement

[paulquinlan100]

Hi

I'm trying to execute the following code:

sSQL = "insert into lkpChanges
(SiteRef,Projec tName,Investmen tType,FieldChan ged,PreviousDat e,NewDate,Chang eDate,CurrentRe launch)
" & _
"values (" & sRef & ", """ & pName & """,""" & iType &
""",""" & fChange & """," & IIf(IsNull(pDat e), Null, pDate) & "," &
IIf(IsNull(txtF SOS), Null, txtFSOS) & "," & Date & "," &
IIf(IsNull(txtF Relaunch), Null, txtFRelaunch) & ");"

DoCmd.RunSQL sSQL

which evaluates to:

insert into lkpChanges
(SiteRef,Projec tName,Investmen tType,FieldChan ged,PreviousDat e,NewDate,Chang eDate,CurrentRe launch)
values (572, "Accrington","N ew","SOS",,02/01/2008,30/05/2008,);

However, i get the message "Syntax error in Insert Into statement"

I presume this is due to the null dates. Is there anyway around this?

Thanks
Paul

[Allen Browne]

You need the text "Null, " in your SQL string:

... & IIf(IsNull(pDat e), "Null, ", pDate) & ...

To aid with debugging, try adding:
Debug.Print sSQL

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa************ @hotmail.comwro te in message
news:64******** *************** ***********@56g 2000hsm.googleg roups.com...

Hi

I'm trying to execute the following code:

sSQL = "insert into lkpChanges
(SiteRef,Projec tName,Investmen tType,FieldChan ged,PreviousDat e,NewDate,Chang eDate,CurrentRe launch)
" & _
"values (" & sRef & ", """ & pName & """,""" & iType &
""",""" & fChange & """," & IIf(IsNull(pDat e), Null, pDate) & "," &
IIf(IsNull(txtF SOS), Null, txtFSOS) & "," & Date & "," &
IIf(IsNull(txtF Relaunch), Null, txtFRelaunch) & ");"

DoCmd.RunSQL sSQL

which evaluates to:

insert into lkpChanges
(SiteRef,Projec tName,Investmen tType,FieldChan ged,PreviousDat e,NewDate,Chang eDate,CurrentRe launch)
values (572, "Accrington","N ew","SOS",,02/01/2008,30/05/2008,);

However, i get the message "Syntax error in Insert Into statement"

I presume this is due to the null dates. Is there anyway around this?

Thanks
Paul

[Arch]

On Fri, 30 May 2008 06:22:17 -0700 (PDT), "pa************ @hotmail.com"
<pa************ @hotmail.comwro te:

>Hi

I'm trying to execute the following code:

sSQL = "insert into lkpChanges
(SiteRef,Proje ctName,Investme ntType,FieldCha nged,PreviousDa te,NewDate,Chan geDate,CurrentR elaunch)
" & _
"values (" & sRef & ", """ & pName & """,""" & iType &
""",""" & fChange & """," & IIf(IsNull(pDat e), Null, pDate) & "," &
IIf(IsNull(txt FSOS), Null, txtFSOS) & "," & Date & "," &
IIf(IsNull(txt FRelaunch), Null, txtFRelaunch) & ");"

DoCmd.RunSQL sSQL

which evaluates to:

insert into lkpChanges
(SiteRef,Proje ctName,Investme ntType,FieldCha nged,PreviousDa te,NewDate,Chan geDate,CurrentR elaunch)
values (572, "Accrington","N ew","SOS",,02/01/2008,30/05/2008,);

However, i get the message "Syntax error in Insert Into statement"

I presume this is due to the null dates. Is there anyway around this?

Thanks
Paul

You've got a few problems there. Dates need to be quoted with #. You
might have some trouble with your date format. You need placeholders
for the nulls.

>values (572, "Accrington","N ew","SOS",Null, #02/01/2008#,#30/05/2008#,Null);

or, you might have to change the date format:

>values (572, "Accrington","N ew","SOS",Null, #01/02/2008#,#05/30/2008#,Null);

should work.

Arch