By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Null dates in Insert Into statement

P: n/a
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
Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jun 27 '08 #2

P: n/a
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
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.