Runtime error when creating a table with an SQL query

[Cevin]

Hi All,

In Access2003 I'm trying to create a table when the user clicks on a form
button.

The code is:

DoCmd.RunSQL ("SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE (((tblGages.CalDue) < " & dateDateDue & ") " & _
"And ((tblGages.Status) = 1) OR (tblGages.Status)=17) " & _
"ORDER BY tblTeams.Team;")

If I remove the
OR (tblGages.Status)=17)
it creates the table just fine. When the OR status is added I get
Run-time error '3141':
The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect

All items are declared in the option area.

Any ideas what I'm doing wrong, or am I trying to do something AccessVB
won't do?

Thanx in advance for your help.
Cevin

 

[Tom Ellison]

Dear Cevin:

I recommend this:

DECLARE strSQL string;
strSQL = "SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE tblGages.CalDue < #" & dateDateDue & "# " & _
"And tblGages.Status IN (1, 17) " & _
"ORDER BY tblTeams.Team;"
DoCmd.RunSQL (strSQL)

If you still have trouble, put a breakpoint on the last line above and view
strSQL in the immediate pane. You could post that text back here.

You could try running the SQL by copying and pasting this into the SQL View
of a new query. You could drop the INTO tbltmpDue from this and just try to
observe the results.

If you have the value of dateDateDue in a control, or if you create an
invisible control and put that value in it, then you can just put a
reference to the control in the query.

Tom Ellison

 

[Cevin]

Tom,

Thanx for your assistance - it worked great!

If I may push my luck...
....how would the part of the WHERE line "And tblGages.Status IN (1, 17) " &
_ look if I were to have a string?

Thanks again,
Cevin

Dear Cevin:

I recommend this:

DECLARE strSQL string;
strSQL = "SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE tblGages.CalDue < #" & dateDateDue & "# " & _
"And tblGages.Status IN (1, 17) " & _
"ORDER BY tblTeams.Team;"
DoCmd.RunSQL (strSQL)

If you still have trouble, put a breakpoint on the last line above and
view strSQL in the immediate pane. You could post that text back here.

You could try running the SQL by copying and pasting this into the SQL
View of a new query. You could drop the INTO tbltmpDue from this and just
try to observe the results.

If you have the value of dateDateDue in a control, or if you create an
invisible control and put that value in it, then you can just put a
reference to the control in the query.

Tom Ellison

Hi All,

In Access2003 I'm trying to create a table when the user clicks on a form
button.

The code is:

DoCmd.RunSQL ("SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE (((tblGages.CalDue) < " & dateDateDue & ") " & _
"And ((tblGages.Status) = 1) OR (tblGages.Status)=17) " & _
"ORDER BY tblTeams.Team;")

If I remove the
OR (tblGages.Status)=17)
it creates the table just fine. When the OR status is added I get
Run-time error '3141':
The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect

All items are declared in the option area.

Any ideas what I'm doing wrong, or am I trying to do something AccessVB
won't do?

Thanx in advance for your help.
Cevin

 

[Douglas J. Steele]

"And tblGages.Status IN ('A', 'D') " & _

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Tom,

Thanx for your assistance - it worked great!

If I may push my luck...
...how would the part of the WHERE line "And tblGages.Status IN (1, 17) "
& _ look if I were to have a string?

Thanks again,
Cevin

in message

Dear Cevin:

I recommend this:

DECLARE strSQL string;
strSQL = "SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE tblGages.CalDue < #" & dateDateDue & "# " & _
"And tblGages.Status IN (1, 17) " & _
"ORDER BY tblTeams.Team;"
DoCmd.RunSQL (strSQL)

If you still have trouble, put a breakpoint on the last line above and
view strSQL in the immediate pane. You could post that text back here.

You could try running the SQL by copying and pasting this into the SQL
View of a new query. You could drop the INTO tbltmpDue from this and
just try to observe the results.

If you have the value of dateDateDue in a control, or if you create an
invisible control and put that value in it, then you can just put a
reference to the control in the query.

Tom Ellison

Hi All,

In Access2003 I'm trying to create a table when the user clicks on a
form button.

The code is:

DoCmd.RunSQL ("SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE (((tblGages.CalDue) < " & dateDateDue & ") " & _
"And ((tblGages.Status) = 1) OR (tblGages.Status)=17) " & _
"ORDER BY tblTeams.Team;")

If I remove the
OR (tblGages.Status)=17)
it creates the table just fine. When the OR status is added I get
Run-time error '3141':
The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect

All items are declared in the option area.

Any ideas what I'm doing wrong, or am I trying to do something AccessVB
won't do?

Thanx in advance for your help.
Cevin

 

[Cevin]

Super!

Thanx for the help

Cevin

"And tblGages.Status IN ('A', 'D') " & _

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Tom,

Thanx for your assistance - it worked great!

If I may push my luck...
...how would the part of the WHERE line "And tblGages.Status IN (1, 17) "
& _ look if I were to have a string?

Thanks again,
Cevin

in message

Dear Cevin:

I recommend this:

DECLARE strSQL string;
strSQL = "SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE tblGages.CalDue < #" & dateDateDue & "# " & _
"And tblGages.Status IN (1, 17) " & _
"ORDER BY tblTeams.Team;"
DoCmd.RunSQL (strSQL)

If you still have trouble, put a breakpoint on the last line above and
view strSQL in the immediate pane. You could post that text back here.

You could try running the SQL by copying and pasting this into the SQL
View of a new query. You could drop the INTO tbltmpDue from this and
just try to observe the results.

If you have the value of dateDateDue in a control, or if you create an
invisible control and put that value in it, then you can just put a
reference to the control in the query.

Tom Ellison


in message Hi All,

In Access2003 I'm trying to create a table when the user clicks on a
form button.

The code is:

DoCmd.RunSQL ("SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE (((tblGages.CalDue) < " & dateDateDue & ") " & _
"And ((tblGages.Status) = 1) OR (tblGages.Status)=17) " & _
"ORDER BY tblTeams.Team;")

If I remove the
OR (tblGages.Status)=17)
it creates the table just fine. When the OR status is added I get
Run-time error '3141':
The SELECT statement includes a reserved word or an argument
name
that is misspelled or missing, or the punctuation is incorrect

All items are declared in the option area.

Any ideas what I'm doing wrong, or am I trying to do something AccessVB
won't do?

Thanx in advance for your help.
Cevin

 

[Tom Ellison]

Dear Cevin:

Just for your information, I believe the original problem was in the logic.
There is a precedence between the operators AND and OR, just like the
precedence between multiplication and addition.

For example:

3 * 5 + 2

The result is 17, not 30. Right?

Similarly:

A AND B OR C

could be seen two ways, ignoring precedence:

A AND (B OR C) which is what I believe you intended

(A AND B) OR C which is what I understand it is doing, which is always TRUE
whenever C is true, even though A is false!

Perhaps this explains your problem.

Using the IN operator eliminates the problem. It is definitely the case
that this is A AND (B OR C). In addition, it reads easily that this is what
it does. Anything that reads more clearly is good programming. It also
tends to be shorter, expecially if there are more than two arguments.

I expect you have a table that defines the status values with a text value.
For documentary purposes, using those text values would be good. Of course,
you're going to lose some performance in looking them up, but likely there
are few, and this operation would be mostly in memory. You could try it and
see what happens.

The values, like 1 and 17, are codes which may not be memorized for each and
every programmer who looks at the code. If the program is ever distributed
widely, the various codes, and their text descriptions, may be quite
different from one installation to another. The superior way of creating
the application would be to add a column to the table of Status values with
a boolean value. After JOINing to that table, the test would then be a test
of that boolean. That's the way to program in the most powerful, flexible
manner, and is extremely well documented.

Tom Ellison

Tom,

Thanx for your assistance - it worked great!

If I may push my luck...
...how would the part of the WHERE line "And tblGages.Status IN (1, 17) "
& _ look if I were to have a string?

Thanks again,
Cevin

in message

Dear Cevin:

I recommend this:

DECLARE strSQL string;
strSQL = "SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE tblGages.CalDue < #" & dateDateDue & "# " & _
"And tblGages.Status IN (1, 17) " & _
"ORDER BY tblTeams.Team;"
DoCmd.RunSQL (strSQL)

If you still have trouble, put a breakpoint on the last line above and
view strSQL in the immediate pane. You could post that text back here.

You could try running the SQL by copying and pasting this into the SQL
View of a new query. You could drop the INTO tbltmpDue from this and
just try to observe the results.

If you have the value of dateDateDue in a control, or if you create an
invisible control and put that value in it, then you can just put a
reference to the control in the query.

Tom Ellison

Hi All,

In Access2003 I'm trying to create a table when the user clicks on a
form button.

The code is:

DoCmd.RunSQL ("SELECT tblGages.CustCN AS CN, " & _
"tblGages.CustSN AS SN, " & _
"tblGages.GageFullDesc AS Gage, " & _
"tblGages.byteLocationID, " & _
"tblTeams.Team AS Team, " & _
"tblGages.Workcenter AS Workcenter, " & _
"tblGages.CalDue AS Due " & _
"tblGages.Status AS Status " & _
"INTO tbltmpDue " & _
"FROM tblTeams " & _
"INNER JOIN [tblGages] ON tblTeams.byteLocationID =
tblGages.byteLocationID " & _
"WHERE (((tblGages.CalDue) < " & dateDateDue & ") " & _
"And ((tblGages.Status) = 1) OR (tblGages.Status)=17) " & _
"ORDER BY tblTeams.Team;")

If I remove the
OR (tblGages.Status)=17)
it creates the table just fine. When the OR status is added I get
Run-time error '3141':
The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect

All items are declared in the option area.

Any ideas what I'm doing wrong, or am I trying to do something AccessVB
won't do?

Thanx in advance for your help.
Cevin