Error 3141 Cannot resolve

[TeeSee]

First let me wish "the group" a very Happy New Year. Lang may yer lum
reek!

The following SQL code was created using "SQL Formatter". Although a
different sequence than usually shown I don't see why it won't work.
I deliberately show two "WHERE" lines as I am also trying to get the
user to input the WHERE criteria as in a normal query. The "fs*j" came
out of the original query.
I also can't resolve the error 3141 thing. Using XP and MA2003. Any
assitance would be appreciated.

Private Sub cmdUpdateDiscount_Click()
'**************************************************************
Dim stDocName As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rsMMhist As DAO.Recordset
Dim strSQL As String
Dim strItem As String
Dim Message, Title
'**************************************************************
'Message = "Please input the filter string"
'Title = "Filter String Input"
'strItem = InputBox(Message, Title, Default, 5000, 3000)
'Set rs1 = db.OpenRecordset
strSQL = "SELECT tblMaterialMaster.Discount,"
strSQL = strSQL & "tblMaterialMaster.ListPrice,"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & " WHERE (((tblMaterialMaster.SISItemCode)= Like
""fs*j""))"
strSQL = strSQL & " WHERE (((tblMaterialMaster.SISItemCode)=
[Input Code]))" 'Like ""fs*j""))"
strSQL = strSQL & " ORDER BY tblMaterialMaster.Funds,"
strSQL = strSQL & "tblMaterialMaster.SISItemCode;"
strSQL = strSQL & " "
Set rs1 = CurrentDb.OpenRecordset(strSQL)
'<---------Problem code line
'(RUNTIME ERROR 3141)
' (The select statement includes a reserved word or an argument
'name that is misspelled or missing, or the punctuation is
incorrect.)

stDocName = "frmMaterialMaster"
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, ,
OpenArgs:="strSQL"
End Sub

 

[Dirk Goldgar]

First let me wish "the group" a very Happy New Year. Lang may yer lum
reek!

The following SQL code was created using "SQL Formatter". Although a
different sequence than usually shown I don't see why it won't work.
I deliberately show two "WHERE" lines as I am also trying to get the
user to input the WHERE criteria as in a normal query. The "fs*j" came
out of the original query.
I also can't resolve the error 3141 thing. Using XP and MA2003. Any
assitance would be appreciated.

Private Sub cmdUpdateDiscount_Click()
'**************************************************************
Dim stDocName As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rsMMhist As DAO.Recordset
Dim strSQL As String
Dim strItem As String
Dim Message, Title
'**************************************************************
'Message = "Please input the filter string"
'Title = "Filter String Input"
'strItem = InputBox(Message, Title, Default, 5000, 3000)
'Set rs1 = db.OpenRecordset
strSQL = "SELECT tblMaterialMaster.Discount,"
strSQL = strSQL & "tblMaterialMaster.ListPrice,"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & " WHERE (((tblMaterialMaster.SISItemCode)= Like
""fs*j""))"
strSQL = strSQL & " WHERE (((tblMaterialMaster.SISItemCode)=
[Input Code]))" 'Like ""fs*j""))"
strSQL = strSQL & " ORDER BY tblMaterialMaster.Funds,"
strSQL = strSQL & "tblMaterialMaster.SISItemCode;"
strSQL = strSQL & " "
Set rs1 = CurrentDb.OpenRecordset(strSQL)
'<---------Problem code line
'(RUNTIME ERROR 3141)
' (The select statement includes a reserved word or an argument
'name that is misspelled or missing, or the punctuation is
incorrect.)

stDocName = "frmMaterialMaster"
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, ,
OpenArgs:="strSQL"
End Sub

You can't have two WHERE clauses in a query, so I expect that's where your
error is coming from. If you are trying to specify two different criteria,
and have a record returned if it meets either of them, then use the OR
keyword to join the criteria:

strSQL = strSQL & _
" WHERE (((tblMaterialMaster.SISItemCode)= Like ""fs*j""))"
strSQL = strSQL & _
" OR (((tblMaterialMaster.SISItemCode)= [Input Code]))"

If you want to have a record returned only if it meets both of the
criteria -- which may not make sense in this case -- use the AND keyword
instead:

strSQL = strSQL & _
" WHERE (((tblMaterialMaster.SISItemCode)= Like ""fs*j""))"
strSQL = strSQL & _
" AND (((tblMaterialMaster.SISItemCode)= [Input Code]))"

 

[TeeSee]

First let me wish "the group" a very Happy New Year. Lang may yer lum
reek!

The following SQL code was created using "SQL Formatter". Although a
different sequence than usually shown I don't see why it won't work.
I deliberately show two "WHERE" lines as I am also trying to get the
user to input the WHERE criteria as in a normal query. The "fs*j" came
out of the original query.
I also can't resolve the error 3141 thing. Using XP and MA2003. Any
assitance would be appreciated.

Private Sub cmdUpdateDiscount_Click()
'**************************************************************
   Dim stDocName As String
   Dim db As DAO.Database
   Dim rs1 As DAO.Recordset
   Dim rsMMhist As DAO.Recordset
   Dim strSQL As String
   Dim strItem As String
   Dim Message, Title
'**************************************************************
    'Message = "Please input the filter string"
   'Title = "Filter String Input"
   'strItem = InputBox(Message, Title, Default, 5000, 3000)
   'Set rs1 = db.OpenRecordset
       strSQL = "SELECT tblMaterialMaster.Discount,"
   strSQL = strSQL & "tblMaterialMaster.ListPrice,"
   strSQL = strSQL & " FROM tblMaterialMaster"
   strSQL = strSQL & " WHERE (((tblMaterialMaster.SISItemCode)=
[Input Code]))"
   strSQL = strSQL & " ORDER BY tblMaterialMaster.Funds,"
   strSQL = strSQL & "tblMaterialMaster.SISItemCode;"
   strSQL = strSQL & " "
Set rs1 = CurrentDb.OpenRecordset(strSQL)
'<---------Problem code line
'(RUNTIME ERROR 3141)
' (The select statement includes a reserved word or an argument
'name that is misspelled or missing, or the punctuation is
incorrect.)

   stDocName = "frmMaterialMaster"
   DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, ,
OpenArgs:="strSQL"
End Sub

You can't have two WHERE clauses in a query, so I expect that's where your
error is coming from.  If you are trying to specify two different criteria,
and have a record returned if it meets either of them, then use the OR
keyword to join the criteria:

    strSQL = strSQL & _
        " WHERE (((tblMaterialMaster.SISItemCode)= Like ""fs*j""))"
    strSQL = strSQL & _
        " OR (((tblMaterialMaster.SISItemCode)= [Input Code]))"

If you want to have a record returned only if it meets both of the
criteria -- which may not make sense in this case -- use the AND keyword
instead:

    strSQL = strSQL & _
        " WHERE (((tblMaterialMaster.SISItemCode)= Like ""fs*j""))"
    strSQL = strSQL & _
        " AND (((tblMaterialMaster.SISItemCode)= [Input Code]))"

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Hi Dirk .... Thanks for the response but I think I confused you and
probably anyone else who may have read this. I wasn't actually trying
to use both lines of WHERE clause they were both there to illustrate
what I was trying to accomplish.
If you would be so good as to go back to my (amended) original post
this is how I ACTUALLY have the code and rhe errors and question(s)
still apply. Thanks again

 

[Dirk Goldgar]

Thanks for the response but I think I confused you and probably anyone
else who may have read this. I wasn't actually trying

to use both lines of WHERE clause they were both there to illustrate what I
was trying to accomplish. If you would be so good as to go back to my
(amended) original post this is how I ACTUALLY have the code and rhe errors
and question(s) still apply. Thanks again


Ah. Okay, I see a couple of problems: one bug that is probably biting you
now, and one that will bite you after you fix this one if you don't attend
to it. First, consider these lines:

strSQL = "SELECT tblMaterialMaster.Discount,"
strSQL = strSQL & "tblMaterialMaster.ListPrice,"
strSQL = strSQL & " FROM tblMaterialMaster"

In the second quoted line, you'll note that you have a comma in the SQL
string after the field name. But the next line is the FROM clause, and
there should be no comma between the last selected field name and the FROM
keyword. Your code should read:

strSQL = "SELECT tblMaterialMaster.Discount,"
strSQL = strSQL & "tblMaterialMaster.ListPrice"
strSQL = strSQL & " FROM tblMaterialMaster"

The second bug needs a bit more to fix it. Your SQL string contains a
reference to a parameter, [Enter Code]. Although this works with queries
opened via the Access user interface -- Access will prompt the user for the
parameter value -- it doesn't work with queries executed via DAO, because
DAO has no means to prompt for parameters. So what you need to do is use
your own InputBox or dialog form to prompt the user for the value, and then
embed the user's response into the SQL. Something like this:

Dim strCode As String

strCode = InputBox("Enter Code:")

' What if no code is entered? Maybe exit.
If Len(strCode ) = 0 Then
Exit Sub
End If

strSQL = "SELECT tblMaterialMaster.Discount,"
strSQL = strSQL & "tblMaterialMaster.ListPrice"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & _
" WHERE (tblMaterialMaster.SISItemCode='" & strCode & "')"
strSQL = strSQL & " ORDER BY tblMaterialMaster.Funds,"
strSQL = strSQL & "tblMaterialMaster.SISItemCode;"
Set rs1 = CurrentDb.OpenRecordset(strSQL)

That, or something like it, should solve that problem. Having read on
through the remaining lines of your original code:

stDocName = "frmMaterialMaster"
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, ,
OpenArgs:="strSQL"
End Sub

.... I have to say I don't understand why you are opening the recordset in
the first place, since you don't use it anywhere. Instead, I see you
passing the SQL string to your form via OpenArgs. Maybe you left some code
out, or maybe there's some further misunderstanding on your part or mine; I
can't say.