|
 |
access thread: Error 3141: Select statement includes a reserved word or argument ...
Message #1 by "Christopher Mohr" <cmohr@b...> on Mon, 25 Feb 2002 16:39:32
|
|
I have the following code that comes up with the following error message:
Here is the error message:
Run-time error '3141':
The SELECT statment includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.
Here is my code:
Dim db As DAO.Database
Dim rsContact As DAO.Recordset
Dim sSQL As String
sSQL = "SELECT Contacts.CompanyID, Company.CompanyName,
Contacts.ContactID, Contacts.ContactName," & _
"FROM Company INNER JOIN Contacts ON Company.CompanyID =
Contacts.CompanyID," & _
"WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
"ORDER BY ContactName ASC"
Set rsContact = CurrentDb().OpenRecordset(sSQL, dbOpenDynaset,
dbConsistent, dbPessimistic)
With rsContact
Me.CompanyName = !CompanyName
End With
Company ID is a number, not text.
Thanks,
Chris
Message #2 by "Phillip Johnson" <phillip.johnson@e...> on Mon, 25 Feb 2002 16:46:53
|
|
I think the problem is that the control on the form should be outside the
SQL statement.
Try changing:
"WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
"ORDER BY ContactName ASC"
To:
"WHERE Contacts.CompanyID=" & Forms!frmCompany!CompanyName & " ORDER
BY ContactName ASC"
Hope it helps, Phillip
> I have the following code that comes up with the following error message:
>
> Here is the error message:
>
> Run-time error '3141':
> The SELECT statment includes a reserved word or an argument name that is
> misspelled or missing, or the punctuation is incorrect.
>
> Here is my code:
>
> Dim db As DAO.Database
> Dim rsContact As DAO.Recordset
> Dim sSQL As String
>
> sSQL = "SELECT Contacts.CompanyID, Company.CompanyName,
> Contacts.ContactID, Contacts.ContactName," & _
> "FROM Company INNER JOIN Contacts ON Company.CompanyID =
> Contacts.CompanyID," & _
> "WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
> "ORDER BY ContactName ASC"
>
> Set rsContact = CurrentDb().OpenRecordset(sSQL, dbOpenDynaset,
> dbConsistent, dbPessimistic)
>
> With rsContact
> Me.CompanyName = !CompanyName
> End With
>
> Company ID is a number, not text.
>
> Thanks,
>
> Chris
Message #3 by "Christopher Mohr" <cmohr@b...> on Mon, 25 Feb 2002 17:08:24
|
|
Phillip,
Thanks for the advice. I did need that, but I am still having the same
problem.
Chris
> I think the problem is that the control on the form should be outside
the
> SQL statement.
>
> Try changing:
>
> "WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
> "ORDER BY ContactName ASC"
>
> To:
>
> "WHERE Contacts.CompanyID=" & Forms!frmCompany!CompanyName & " ORDER
> BY ContactName ASC"
>
> Hope it helps, Phillip
Message #4 by Omar Chaudry <OChaudry@b...> on Mon, 25 Feb 2002 17:05:13 -0000
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1BE1E.96C936B0
Content-Type: text/plain
You need the criteria enclosed in single quotes so try this
"WHERE Contacts.CompanyID='" & Forms!frmCompany!CompanyName & "' ORDER
BY ContactName ASC"
HTH
OMAR
-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: 25 February 2002 16:47
To: Access
Subject: [access] Re: Error 3141: Select statement includes a reserved word
or argument ...
I think the problem is that the control on the form should be outside the
SQL statement.
Try changing:
"WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
"ORDER BY ContactName ASC"
To:
"WHERE Contacts.CompanyID=" & Forms!frmCompany!CompanyName & " ORDER
BY ContactName ASC"
Hope it helps, Phillip
> I have the following code that comes up with the following error message:
>
> Here is the error message:
>
> Run-time error '3141':
> The SELECT statment includes a reserved word or an argument name that is
> misspelled or missing, or the punctuation is incorrect.
>
> Here is my code:
>
> Dim db As DAO.Database
> Dim rsContact As DAO.Recordset
> Dim sSQL As String
>
> sSQL = "SELECT Contacts.CompanyID, Company.CompanyName,
> Contacts.ContactID, Contacts.ContactName," & _
> "FROM Company INNER JOIN Contacts ON Company.CompanyID =
> Contacts.CompanyID," & _
> "WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
> "ORDER BY ContactName ASC"
>
> Set rsContact = CurrentDb().OpenRecordset(sSQL, dbOpenDynaset,
> dbConsistent, dbPessimistic)
>
> With rsContact
> Me.CompanyName = !CompanyName
> End With
>
> Company ID is a number, not text.
>
> Thanks,
>
> Chris
DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
Message #5 by Barry Martin Dancis <bdancis@c...> on Mon, 25 Feb 2002 12:32:03 -0500
|
|
Chris,
In the line
"WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName"
Forms!frmCompany!CompanyName will appear as text and not as the value in the
control. Try the following instead:
"WHERE Contacts.CompanyID= " & Forms!frmCompany!CompanyName
Note that the text ends after the equal sign and the use of the "&" to
append the value of the control to the string. In general, it is a good idea
when debugging query texts in a form to print out the value of the text
with:
Debug.print sSql
and then take the text from the debug window and get it to work in an actual
query. It's a lot easier than trying to debug the query while running a
form.
Enjoy!
Barry
----- Original Message -----
From: "Christopher Mohr" <cmohr@b...>
To: "Access" <access@p...>
Sent: Monday, February 25, 2002 11:39 AM
Subject: [access] Error 3141: Select statement includes a reserved word or
argument ...
> I have the following code that comes up with the following error message:
>
> Here is the error message:
>
> Run-time error '3141':
> The SELECT statment includes a reserved word or an argument name that is
> misspelled or missing, or the punctuation is incorrect.
>
> Here is my code:
>
> Dim db As DAO.Database
> Dim rsContact As DAO.Recordset
> Dim sSQL As String
>
> sSQL = "SELECT Contacts.CompanyID, Company.CompanyName,
> Contacts.ContactID, Contacts.ContactName," & _
> "FROM Company INNER JOIN Contacts ON Company.CompanyID
> Contacts.CompanyID," & _
> "WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
> "ORDER BY ContactName ASC"
>
> Set rsContact = CurrentDb().OpenRecordset(sSQL, dbOpenDynaset,
> dbConsistent, dbPessimistic)
>
> With rsContact
> Me.CompanyName = !CompanyName
> End With
>
> Company ID is a number, not text.
>
> Thanks,
>
> Chris
>
>
Message #6 by Barry Martin Dancis <bdancis@c...> on Mon, 25 Feb 2002 13:35:59 -0500
|
|
Chris,
Send us a copy of the value in sSql
Barry
----- Original Message -----
From: "Christopher Mohr" <cmohr@b...>
To: "Access" <access@p...>
Sent: Monday, February 25, 2002 12:08 PM
Subject: [access] Re: Error 3141: Select statement includes a reserved word
or argument ...
> Phillip,
>
> Thanks for the advice. I did need that, but I am still having the same
> problem.
>
> Chris
>
> > I think the problem is that the control on the form should be outside
> the
> > SQL statement.
> >
> > Try changing:
> >
> > "WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName" & _
> > "ORDER BY ContactName ASC"
> >
> > To:
> >
> > "WHERE Contacts.CompanyID=" & Forms!frmCompany!CompanyName & " ORDER
> > BY ContactName ASC"
> >
> > Hope it helps, Phillip
>
>
Message #7 by "Christopher Mohr" <cmohr@b...> on Mon, 25 Feb 2002 19:18:57
|
|
Barry,
This is what I get if I use debug.print sSQL:
SELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
Contacts.ContactName,FROM Company INNER JOIN Contacts ON Company.CompanyID
= Contacts.CompanyID,WHERE Contacts.CompanyID= ORDER BY ContactName
ASCSELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
Contacts.ContactName,FROM Company INNER JOIN Contacts ON Company.CompanyID
= Contacts.CompanyID,WHERE Contacts.CompanyID=40 ORDER BY ContactName ASC
Thanks,
Chris
> Chris,
>
> In the line
>
> "WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName"
>
> Forms!frmCompany!CompanyName will appear as text and not as the value in
the
> control. Try the following instead:
>
> "WHERE Contacts.CompanyID= " & Forms!frmCompany!CompanyName
>
> Note that the text ends after the equal sign and the use of the "&" to
> append the value of the control to the string. In general, it is a good
idea
> when debugging query texts in a form to print out the value of the text
> with:
>
> Debug.print sSql
>
> and then take the text from the debug window and get it to work in an
actual
> query. It's a lot easier than trying to debug the query while running a
> form.
>
> Enjoy!
>
> Barry
>
Message #8 by "John Ruff" <papparuff@c...> on Mon, 25 Feb 2002 11:42:55 -0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0001_01C1BDF1.91585DA0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Take your SQL statement and place it in a query. So far I found the
following errors and I don't have these tables to check everything.
You have two select statement here
4th line - you start a second select statement, should not
ASCSELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
have everything after ASC deleted (this is the start of the second
select statement).
I have found the following errors:
2nd line - remove the comma after Contacts.ContactName, WHERE should be
Contacts.ContactName WHERE
3rd line - remove the comma after = Contacts.CompanyID, WHERE should be
Contacts.CompanyID WHERE
3rd line - Contacts.CompanyID= ORDER BY is incorrect. What is
Contacts.CompanyID= suppose to equal
I believe you want your SQL statement to look like this:
SELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
Contacts.ContactName FROM Company INNER JOIN Contacts ON
Company.CompanyID = Contacts.CompanyID WHERE Contacts.CompanyID=40 ORDER
BY ContactName ASC
John Ruff - The Eternal Optimist J
Always looking for Contract Opportunities
9306 Farwest Dr SW
Lakewood, WA 98498
papparuff@c...
-----Original Message-----
From: Christopher Mohr [mailto:cmohr@b...]
Sent: Monday, February 25, 2002 7:19 PM
To: Access
Subject: [access] Re: Error 3141: Select statement includes a reserved
word or- argument ...
Barry,
This is what I get if I use debug.print sSQL:
SELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
Contacts.ContactName,FROM Company INNER JOIN Contacts ON
Company.CompanyID
= Contacts.CompanyID,WHERE Contacts.CompanyID= ORDER BY ContactName
ASCSELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
Contacts.ContactName,FROM Company INNER JOIN Contacts ON
Company.CompanyID
= Contacts.CompanyID,WHERE Contacts.CompanyID=40 ORDER BY ContactName
ASC
Thanks,
Chris
> Chris,
>
> In the line
>
> "WHERE Contacts.CompanyID= Forms!frmCompany!CompanyName"
>
> Forms!frmCompany!CompanyName will appear as text and not as the value
> in
the
> control. Try the following instead:
>
> "WHERE Contacts.CompanyID= " & Forms!frmCompany!CompanyName
>
> Note that the text ends after the equal sign and the use of the "&" to
> append the value of the control to the string. In general, it is a
> good
idea
> when debugging query texts in a form to print out the value of the
> text
> with:
>
> Debug.print sSql
>
> and then take the text from the debug window and get it to work in an
actual
> query. It's a lot easier than trying to debug the query while running
> a form.
>
> Enjoy!
>
> Barry
>
Message #9 by "Christopher Mohr" <cmohr@b...> on Mon, 25 Feb 2002 20:18:21
|
|
John,
You are a Genius. I cannot believe how simple that was. Thank you for all
of your help.
Chris
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0001_01C1BDF1.91585DA0
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> Take your SQL statement and place it in a query. So far I found the
> following errors and I don't have these tables to check everything.
>
> You have two select statement here
>
> 4th line - you start a second select statement, should not
>
> ASCSELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
> have everything after ASC deleted (this is the start of the second
> select statement).
>
> I have found the following errors:
>
> 2nd line - remove the comma after Contacts.ContactName, WHERE should be
> Contacts.ContactName WHERE
> 3rd line - remove the comma after = Contacts.CompanyID, WHERE should be
> Contacts.CompanyID WHERE
> 3rd line - Contacts.CompanyID= ORDER BY is incorrect. What is
> Contacts.CompanyID= suppose to equal
>
> I believe you want your SQL statement to look like this:
>
> SELECT Contacts.CompanyID, Company.CompanyName, Contacts.ContactID,
> Contacts.ContactName FROM Company INNER JOIN Contacts ON
> Company.CompanyID = Contacts.CompanyID WHERE Contacts.CompanyID=40 ORDER
> BY ContactName ASC
>
>
>
> John Ruff - The Eternal Optimist J
>
> Always looking for Contract Opportunities
>
>
>
> 9306 Farwest Dr SW
>
> Lakewood, WA 98498
>
> papparuff@c...
|
|
|
View Cart
