Reserved word in SELECT statement
CurrentDb.Execute "SELECT ZONE.CITY as ORIGIN_CITY ZONE.PROVINCE as ORIGIN_PROVINCE ZONE.CPCOMBO, ZONE.[ROUTING TERMINAL], ZONE.TCOMBI, ZONE.[ZONE], ZONE.[MIN], ZONE.[LTL], ZONE.[500], ZONE.[1M], ZONE.[2M], ZONE.[5M], ZONE.[10M], ZONE.[20M] INTO getOriginBeyond FROM [ZONE] WHERE ZONE.CPCOMBO=" & Chr(34) & cpccombo & Chr(34) & " AND ZONE.[ZONE]=" & Chr(34) & "BEYOND" & Chr(34), dbFailOnErrorHi,
I am getting Error3141: The select statement includes a reserved word or an argument name...
I tried breaking my head over 30 mins on this. No clue where there is a reserved word is being used. I already put "[ ]" over reserved words such as "Min" and such.
Any help is much appreciated!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Amazing also that ZONE is a Reserved Word ... but I doubt that is the problem.
SELECT ZONE.CITY as ORIGIN_CITY, ZONE.PROVINCE as ORIGIN_PROVINCE ZONE.CPCOMBO,
ZONE.[ROUTING TERMINAL],
ZONE.TCOMBI,
ZONE.[ZONE],
ZONE.[MIN],
ZONE.[LTL],
ZONE.[500],
ZONE.[1M],
ZONE.[2M],
ZONE.[5M],
ZONE.[10M],
ZONE.[20M]
INTO getOriginBeyond
FROM [ZONE]
WHERE ZONE.CPCOMBO=" & Chr(34) & cpccombo & Chr(34) & "
AND ZONE.[ZONE]=" & Chr(34) & "BEYOND" & Chr(34),
SELECT ZONE.CITY as ORIGIN_CITY, ZONE.PROVINCE as ORIGIN_PROVINCE ZONE.CPCOMBO,
ZONE.[ROUTING TERMINAL],
ZONE.TCOMBI,
ZONE.[ZONE],
ZONE.[MIN],
ZONE.[LTL],
ZONE.[500],
ZONE.[1M],
ZONE.[2M],
ZONE.[5M],
ZONE.[10M],
ZONE.[20M]
INTO getOriginBeyond
FROM [ZONE]
WHERE ZONE.CPCOMBO=" & Chr(34) & cpccombo & Chr(34) & "
AND ZONE.[ZONE]=" & Chr(34) & "BEYOND" & Chr(34),
Another comment ...
... CurrentDb.Execute can't be used for a SELECT statement. Use CurrentDb.OpenRecordset
... CurrentDb.Execute can't be used for a SELECT statement. Use CurrentDb.OpenRecordset
>> INTO getOriginBeyond
Easy to miss ...
mx
Easy to miss ...
mx
OP has posted a couple of times already ... and the INTO seems easy to miss ... but it's not a Select query.
mx
mx
Sorry ... I missed the INTO keyword :-s ... so .Execute is good :) ...
In this case, I would likely alias the ZONE source, and drop the source qualifier for your fields since you don't really need that when you have a singular source.
SELECT CITY as ORIGIN_CITY,
PROVINCE as ORIGIN_PROVINCE,
CPCOMBO,
[ROUTING TERMINAL],
TCOMBI,
[ZONE],
[MIN],
[LTL],
[500],
[1M],
[2M],
[5M],
[10M],
[20M]
INTO getOriginBeyond
FROM [ZONE] As aZone
WHERE CPCOMBO="somestring"
AND [ZONE]="BEYOND"
{note: that above is a sample of the resolved SQL statement after the concatenation expression}
In this case, I would likely alias the ZONE source, and drop the source qualifier for your fields since you don't really need that when you have a singular source.
SELECT CITY as ORIGIN_CITY,
PROVINCE as ORIGIN_PROVINCE,
CPCOMBO,
[ROUTING TERMINAL],
TCOMBI,
[ZONE],
[MIN],
[LTL],
[500],
[1M],
[2M],
[5M],
[10M],
[20M]
INTO getOriginBeyond
FROM [ZONE] As aZone
WHERE CPCOMBO="somestring"
AND [ZONE]="BEYOND"
{note: that above is a sample of the resolved SQL statement after the concatenation expression}
I'm pretty sure the missing comma is the issue ..
SELECT ZONE.CITY as ORIGIN_CITY ZONE.PROVINCE as ORIGIN_PROVINCE ZONE.CPCOMBO, ZONE.
no comma here ..........................
SELECT ZONE.CITY as ORIGIN_CITY ZONE.PROVINCE as ORIGIN_PROVINCE ZONE.CPCOMBO, ZONE.
no comma here ..........................
Mx ... yes I caught the INTO from your second post (36551837) :) ... I failed to scroll horizontally :-s
I HATE the code window for that reason ... No Word Wrap. Hello !!!!!!
mx
mx
Mx,
I would think the error would be different if the comma was the issue, maybe 'Missing Operator" or something like that.
I would think the error would be different if the comma was the issue, maybe 'Missing Operator" or something like that.
Well ... the missing comma is just before ZONE ... who knows ....?
mx
mx
ASKER
It worked. Comma is the problem. I've used this before for different query and it worked fine which is what made me go crazy :/
Thanks all!
Have a great weekend!
Thanks all!
Have a great weekend!
Yeah ... somehow ... the missing comma got wrapped into the ZONE reserved gig ... go figure.
mx
mx
True ... AND ... I just created a Table object named Zone with a Field named Zone and did a SELECT .. INTO with CurrentDb.Execute {and made no provisions for treating Zone as a reserved word} in the hopes of duplicating the error message ... but nope ... all worked just fine when running from VBA (A2010/64bit).
.. So .. by adding the comma and treating Zone as a reserved word will hopefully solve the issue for the OP.
.. So .. by adding the comma and treating Zone as a reserved word will hopefully solve the issue for the OP.
Cool! ... Glad you got it sorted! ... amazing how things manifest themselves!
In general, I think the Reserved Word gig is a bit overrated ... at least in SQL ... ie, doesn't seem to cause that many problems per se.
mx
mx
http://support.microsoft.com/kb/321266