Null dates in Insert Into statement

[paulquinlan100]

Hi

I'm trying to execute the following code:

sSQL = "insert into lkpChanges
(SiteRef,ProjectName,InvestmentType,FieldChanged,P reviousDate,NewDate,ChangeDate,CurrentRelaunch)
" & _
"values (" & sRef & ", """ & pName & """,""" & iType &
""",""" & fChange & """," & IIf(IsNull(pDate), Null, pDate) & "," &
IIf(IsNull(txtFSOS), Null, txtFSOS) & "," & Date & "," &
IIf(IsNull(txtFRelaunch), Null, txtFRelaunch) & ");"

DoCmd.RunSQL sSQL

which evaluates to:

insert into lkpChanges
(SiteRef,ProjectName,InvestmentType,FieldChanged,P reviousDate,NewDate,ChangeDate,CurrentRelaunch)
values (572, "Accrington","New","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(pDate), "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.comwrote in message
news:64**********************************@56g2000h sm.googlegroups.com...

Hi

I'm trying to execute the following code:

sSQL = "insert into lkpChanges
(SiteRef,ProjectName,InvestmentType,FieldChanged,P reviousDate,NewDate,ChangeDate,CurrentRelaunch)
" & _
"values (" & sRef & ", """ & pName & """,""" & iType &
""",""" & fChange & """," & IIf(IsNull(pDate), Null, pDate) & "," &
IIf(IsNull(txtFSOS), Null, txtFSOS) & "," & Date & "," &
IIf(IsNull(txtFRelaunch), Null, txtFRelaunch) & ");"

DoCmd.RunSQL sSQL

which evaluates to:

insert into lkpChanges
(SiteRef,ProjectName,InvestmentType,FieldChanged,P reviousDate,NewDate,ChangeDate,CurrentRelaunch)
values (572, "Accrington","New","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.comwrote:

>Hi

I'm trying to execute the following code:

sSQL = "insert into lkpChanges
(SiteRef,ProjectName,InvestmentType,FieldChanged, PreviousDate,NewDate,ChangeDate,CurrentRelaunch)
" & _
"values (" & sRef & ", """ & pName & """,""" & iType &
""",""" & fChange & """," & IIf(IsNull(pDate), Null, pDate) & "," &
IIf(IsNull(txtFSOS), Null, txtFSOS) & "," & Date & "," &
IIf(IsNull(txtFRelaunch), Null, txtFRelaunch) & ");"

DoCmd.RunSQL sSQL

which evaluates to:

insert into lkpChanges
(SiteRef,ProjectName,InvestmentType,FieldChanged, PreviousDate,NewDate,ChangeDate,CurrentRelaunch)
values (572, "Accrington","New","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","New","SOS",Null,#02/01/2008#,#30/05/2008#,Null);

or, you might have to change the date format:

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

should work.

Arch