Null dates in Insert Into statement

[paulquinlan100@hotmail.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

[Allen Browne]

Re: Null dates in Insert Into statement

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.

<paulquinlan100 @hotmail.comwro te in message
news:642110c2-5155-4195-8d33-1414f611c71a@56 g2000hsm.google groups.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]

Re: Null dates in Insert Into statement

On Fri, 30 May 2008 06:22:17 -0700 (PDT), "paulquinlan100 @hotmail.com"
<paulquinlan100 @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