Syntax Error on INSERT command

I keep getting a syntax error when attempting to use the INSERT command on a
Access db using VB6. I've narrowed the issue down to the date field. In the
db I have the Date field set as a Date/Time property. My SQL statement looks
like this:

INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2', '102',
'2003-11-08 04:23:45')

I know the date portion is what is causing the failure because if I do this
it works fine:

INSERT INTO Orders ([Customer ID], [Product ID]) VALUES ('2', '102')

So I read around on the date/time property and I noted a comment where one
should format it with the #. I tried this then:

INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2',
'105887211482', #2003-11-08 04:23:45#)

No luck. Can someone point me in the right direction here? What am I doing
wrong? Is the date/time value formatted incorrectly? Any insight would be
helpful!!

Thanks,

JW

Re: Syntax Error on INSERT command

On Dec 18, 2:41 pm, "Jerry West" wrote:
> I keep getting a syntax error when attempting to use the INSERT command on a
> Access db using VB6. I've narrowed the issue down to the date field. In the
> db I have the Date field set as a Date/Time property. My SQL statement looks
> like this:
>
> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2', '102',
> '2003-11-08 04:23:45')
>
> I know the date portion is what is causing the failure because if I do this
> it works fine:
>
> INSERT INTO Orders ([Customer ID], [Product ID]) VALUES ('2', '102')
>
> So I read around on the date/time property and I noted a comment where one
> should format it with the #. I tried this then:
>
> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2',
> '105887211482', #2003-11-08 04:23:45#)
>
> No luck. Can someone point me in the right direction here? What am I doing
> wrong? Is the date/time value formatted incorrectly? Any insight would be
> helpful!!
>
> Thanks,
>
> JW

I would try
INSERT INTO Orders ([Customer ID], [Product ID], [Date]) VALUES (2,
105887211482, #2003-11-08 04:23:45#)

or

INSERT INTO Orders ([Customer ID], [Product ID], [Date]) VALUES ('2',
'105887211482', #2003-11-08 04:23:45#)
if CustomerID and ProductID actually are strings

As a last resort:
INSERT INTO Orders ([Customer ID], [Product ID], [Date]) VALUES ('2',
'105887211482', #11/08/2003 04:23:45#)

Re: Syntax Error on INSERT command

I am guessing the word 'Date' is causing the problem. Try changing it in the
table to OrderDate

Phil


"lyle" wrote in message
news:8318ed3a-83fc-4b62-9503-5117950f0242@e23g2000prf.google groups.com...
> On Dec 18, 2:41 pm, "Jerry West" wrote:
>> I keep getting a syntax error when attempting to use the INSERT command
>> on a
>> Access db using VB6. I've narrowed the issue down to the date field. In
>> the
>> db I have the Date field set as a Date/Time property. My SQL statement
>> looks
>> like this:
>>
>> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2',
>> '102',
>> '2003-11-08 04:23:45')
>>
>> I know the date portion is what is causing the failure because if I do
>> this
>> it works fine:
>>
>> INSERT INTO Orders ([Customer ID], [Product ID]) VALUES ('2', '102')
>>
>> So I read around on the date/time property and I noted a comment where
>> one
>> should format it with the #. I tried this then:
>>
>> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2',
>> '105887211482', #2003-11-08 04:23:45#)
>>
>> No luck. Can someone point me in the right direction here? What am I
>> doing
>> wrong? Is the date/time value formatted incorrectly? Any insight would be
>> helpful!!
>>
>> Thanks,
>>
>> JW
>
> I would try
> INSERT INTO Orders ([Customer ID], [Product ID], [Date]) VALUES (2,
> 105887211482, #2003-11-08 04:23:45#)
>
> or
>
> INSERT INTO Orders ([Customer ID], [Product ID], [Date]) VALUES ('2',
> '105887211482', #2003-11-08 04:23:45#)
> if CustomerID and ProductID actually are strings
>
> As a last resort:
> INSERT INTO Orders ([Customer ID], [Product ID], [Date]) VALUES ('2',
> '105887211482', #11/08/2003 04:23:45#)
>

Re: Syntax Error on INSERT command

On Tue, 18 Dec 2007 11:41:35 -0800, Jerry West wrote:

> I keep getting a syntax error when attempting to use the INSERT command on a
> Access db using VB6. I've narrowed the issue down to the date field. In the
> db I have the Date field set as a Date/Time property. My SQL statement looks
> like this:
>
> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2', '102',
> '2003-11-08 04:23:45')
>
> I know the date portion is what is causing the failure because if I do this
> it works fine:
>
> INSERT INTO Orders ([Customer ID], [Product ID]) VALUES ('2', '102')
>
> So I read around on the date/time property and I noted a comment where one
> should format it with the #. I tried this then:
>
> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2',
> '105887211482', #2003-11-08 04:23:45#)
>
> No luck. Can someone point me in the right direction here? What am I doing
> wrong? Is the date/time value formatted incorrectly? Any insight would be
> helpful!!
>
> Thanks,
>
> JW

At the very least,use [Date] (within the brackets) not Date (without
the brackets).
The use of #2003-11-08 04:23:45# is correct, though is that November
08 or August 11 you are inserting? I would suggest you use the
unambiguous format of #2008-Nov-08 ...# or Aug-11 if that is what it
is.
But do change the field name to something else, perhaps [OrderDate].
(* see below)

Also, what are the datatypes of [Customer ID] and [Product ID]?
As written you are inserting a text value, not a number value, into
each of those fields. If in fact they are Number datatypes then remove
the quotes from around the number values.

* Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Re: Syntax Error on INSERT command

Many thanks to all who responded. The issue was as two suggested....changing
the Date field name in the table --and then the corresponding SQL
statement-- to DateField solved the problem.

Man, that was driving me CRAZY!!

Thanks!

JW

"fredg" wrote in message
news:ywgewpxzqbm7$.1ttd9tupv4698.dlg@40tude.net...
> On Tue, 18 Dec 2007 11:41:35 -0800, Jerry West wrote:
>
>> I keep getting a syntax error when attempting to use the INSERT command
>> on a
>> Access db using VB6. I've narrowed the issue down to the date field. In
>> the
>> db I have the Date field set as a Date/Time property. My SQL statement
>> looks
>> like this:
>>
>> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2',
>> '102',
>> '2003-11-08 04:23:45')
>>
>> I know the date portion is what is causing the failure because if I do
>> this
>> it works fine:
>>
>> INSERT INTO Orders ([Customer ID], [Product ID]) VALUES ('2', '102')
>>
>> So I read around on the date/time property and I noted a comment where
>> one
>> should format it with the #. I tried this then:
>>
>> INSERT INTO Orders ([Customer ID], [Product ID], Date) VALUES ('2',
>> '105887211482', #2003-11-08 04:23:45#)
>>
>> No luck. Can someone point me in the right direction here? What am I
>> doing
>> wrong? Is the date/time value formatted incorrectly? Any insight would be
>> helpful!!
>>
>> Thanks,
>>
>> JW
>
> At the very least,use [Date] (within the brackets) not Date (without
> the brackets).
> The use of #2003-11-08 04:23:45# is correct, though is that November
> 08 or August 11 you are inserting? I would suggest you use the
> unambiguous format of #2008-Nov-08 ...# or Aug-11 if that is what it
> is.
> But do change the field name to something else, perhaps [OrderDate].
> (* see below)
>
> Also, what are the datatypes of [Customer ID] and [Product ID]?
> As written you are inserting a text value, not a number value, into
> each of those fields. If in fact they are Number datatypes then remove
> the quotes from around the number values.
>
> * Date is a reserved Access/VBA/Jet word and should not be used as a
> field name.
> For additional reserved words, see the Microsoft KnowledgeBase article
> for your version of Access:
>
> 109312 'Reserved Words in Microsoft Access' for Access 97
> 209187 'ACC2000: Reserved Words in Microsoft Access'
> 286335 'ACC2002: Reserved Words in Microsoft Access'
> 321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
>
> For an even more complete list of reserved words, see:
> http://www.allenbrowne.com/AppIssueBadWord.html
>
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

Re: Syntax Error on INSERT command

fredg wrote:


>The use of #2003-11-08 04:23:45# is correct, though is that November
>08 or August 11 you are inserting? I would suggest you use the
>unambiguous format of #2008-Nov-08 ...# or Aug-11 if that is what it
>is.

2003-11-08 is always yyyy-mm-dd. It's quite unambiguous.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/