Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

The Statement includes a reserved word Error 3141 HELP! :) 1

Status
Not open for further replies.
tbonehwd

tbonehwd

IS-IT--Management
Joined
Jul 28, 2005
Messages
45
Location
US
I checked this code over an over and cannot figure out what is wrong I have used basically this same code in another query and its is fine thanks to "PHV" for helping me but I cannot figure out what is wrong with this one...

strSQL = "SELECT Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0))AS UNIT_SHP, Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0))AS UNIT_RET " & _
"(FROM PROOLN_M INNER JOIN CDSADR_M ON PROOLN_M.SHP_CTM=CDSADR_M.CTM_NBR) INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM " & _
"WHERE CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' And PROOLN_M.OLN_STA = 'Z' " & _
"AND PROOLN_M.SHP_CTM " & strADVACCT & _
"AND PROORD_M.ACT_DTE " & strFRMDATE & _
"AND PROORD_M.ACT_DTE " & strTODATE & _
";"

Thanks in advance,

Tbonehwd
 
Sort by date Sort by votes
What's in the strADVACCT, strFRMDATE and STRTODATE variables?

John
 
Upvote 0 Downvote
strADVACCT is where a user can enter an account number
strFRMDATE Begining Date
strTODATE Ending Date

Here is more of the overall code


strADVACCT = "='" & Me.cmbADVACCT.Value & "' "

If IsNull(Me.txtFRMDATE.Value) Then
MsgBox "You must specify a Start Date. Press OK and enter the Start Date"
Exit Sub
Else
strFRMDATE = ">=#" & Me.txtFRMDATE.Value & "# "
End If
If IsNull(Me.txtTODATE.Value) Then
MsgBox "You must specify a End Date. Press OK and enter the End Date"
Exit Sub
Else
strTODATE = "<=#" & Me.txtTODATE.Value & "# "
End If


Hope this helps
 
Upvote 0 Downvote
Can you debug.print all of them immediately above the strSQL= line so you can see what is in them then copy and paste them into a post here. Press Ctrl G to open the debug window to see what is shown.

Reason is the rest of the code looks OK, its probably something in one of those three variables.

John
 
Upvote 0 Downvote
I am not sure what you are asking I am sending the entire piece of code. I created this by copying and pasting the form to a new name and change the SQL code to give me different results.

Here is the entire piece of code:

Private Sub btnEXECUTE_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strADVACCT As String
Dim strFRMDATE As String
Dim strTODATE As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryJG9PLUS") Then
Set qdf = db.CreateQueryDef("qryJG9PLUS")
Else
Set qdf = db.QueryDefs("qryJG9PLUS")
End If
' Get the values from the combo boxes
If IsNull(Me.cmbADVACCT.Value) Then
MsgBox "You must specify an Account Number. Press OK and enter the Account Number"
Exit Sub
Else
If (Me.CheckLST.Value = True) Then
strADVACCT = "IN(" & Me.cmbADVACCT.Value & ") "
Else
If (Me.cmbADVACCT.Value = "BN") Then
strADVACCT = "IN ('000000777777','000080410980','000080740385','000080740387','000080791774','000080851104','000080918031','000080965290','000080965292','000070036325') "
Else
If (Me.cmbADVACCT.Value = "LEVY") Then
strADVACCT = "= '000000775986'"
Else
If (Me.cmbADVACCT.Value = "WALD") Then
strADVACCT = "= '000070036335'"
Else
If (Me.cmbADVACCT.Value = "AMERCH") Then
strADVACCT = "= '000000685361'"
Else
If (Me.cmbADVACCT.Value = "TALLEN") Then
strADVACCT = "= '000070068299'"
Else
If (Me.cmbADVACCT.Value = "INGRAM") Then
strADVACCT = "= '000070047796'"
Else
If (Me.cmbADVACCT.Value = "AMS") Then
strADVACCT = "= '000070048683'"
Else
If (Me.cmbADVACCT.Value = "BTOL") Then
strADVACCT = "= '000070037501'"
Else
If (Me.cmbADVACCT.Value = "BORD") Then
strADVACCT = "IN ('000000601248','000080369130','000000601133')"
Else
If (Me.cmbADVACCT.Value = "AMW") Then
strADVACCT = "= '000000604974'"
Else
If (Me.cmbADVACCT.Value = "KOEN") Then
strADVACCT = "= '000000600529'"
Else
If (Me.cmbADVACCT.Value = "PART") Then
strADVACCT = "= '000070048061'"
Else
If (Me.cmbADVACCT.Value = "SRET") Then
strADVACCT = "= '000080051869'"
Else
If (Me.cmbADVACCT.Value = "BOOKZ") Then
strADVACCT = "= '000070046822'"
Else
If (Me.cmbADVACCT.Value = "FOLL") Then
strADVACCT = "= '000080024775'"
Else
If (Me.cmbADVACCT.Value = "NLEAF") Then
strADVACCT = "= '000000602396'"
Else
If (Me.cmbADVACCT.Value = "HAST") Then
strADVACCT = "= '000080026237'"
Else
If (Me.cmbADVACCT.Value = "AMZ") Then
strADVACCT = "IN ('000080184038','000080693879','000080693880','000080693881')"
Else
If (Me.cmbADVACCT.Value = "PHAM") Then
strADVACCT = "IN ('000080991906','000081156629')"
Else
If (Me.cmbADVACCT.Value = "ABBY") Then
strADVACCT = "= '000090297399'"
Else
If (Me.cmbADVACCT.Value = "BENF") Then
strADVACCT = "= '000080502609'"
Else
If (Me.cmbADVACCT.Value = "BOOKS") Then
strADVACCT = "= '000000609434'"
Else
If (Me.cmbADVACCT.Value = "BOOKW") Then
strADVACCT = "= '000080090957'"
Else
If (Me.cmbADVACCT.Value = "BPDI") Then
strADVACCT = "= '000000701350'"
Else
If (Me.cmbADVACCT.Value = "BROD") Then
strADVACCT = "= '000000601118'"
Else
If (Me.cmbADVACCT.Value = "NEWG") Then
strADVACCT = "IN ('000000741614','000080083331','000000725214','000080466957','000081084862','000080702276','000080650189','000080040329')"
Else
If (Me.cmbADVACCT.Value = "ANEWS") Then
strADVACCT = "IN ('000080328280','000080291972','000000740071','000000704601','000080317502','000080332322','000080028698','000080219488','000080101798','000080119800','000080479597','000000740394','000080045644','000080089787','000000737063','000000782379','000000748880','000080382653','000080422536','000080052398','000080119747','000080562294','000080091055','000080119742','000080119785','000000795627','000080119706','000080091050','000080091058','000080479638','000080065432','000080547860','000000777499','000080029458','000080038894','000080051948','000080097377','000080119680','000080119681','000080119685','000080119688','000080119700','000080119872','000080120248','000080219492','000080265549','000080282591','000080291985','000080317502','000080332322','000080332323','000080348610','000080479638')"
Else
strADVACCT = "='" & Me.cmbADVACCT.Value & "' "
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
If IsNull(Me.txtFRMDATE.Value) Then
MsgBox "You must specify a Start Date. Press OK and enter the Start Date"
Exit Sub
Else
strFRMDATE = ">=#" & Me.txtFRMDATE.Value & "# "
End If
If IsNull(Me.txtTODATE.Value) Then
MsgBox "You must specify a End Date. Press OK and enter the End Date"
Exit Sub
Else
strTODATE = "<=#" & Me.txtTODATE.Value & "# "
End If

' Build the SQL string
strSQL = "SELECT Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0))AS UNIT_SHP, Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0))AS UNIT_RET " & _
"(FROM PROOLN_M INNER JOIN CDSADR_M ON PROOLN_M.SHP_CTM=CDSADR_M.CTM_NBR) INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM " & _
"WHERE CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' And PROOLN_M.OLN_STA = 'Z' " & _
"AND PROOLN_M.SHP_CTM " & strADVACCT & _
"AND PROORD_M.ACT_DTE " & strFRMDATE & _
"AND PROORD_M.ACT_DTE " & strTODATE & _
";"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryJG9PLUS") = acObjStateOpen Then
DoCmd.Close acQuery, "qryJG9PLUS"
End If
' Open the query
DoCmd.OpenQuery "qryJG9PLUS"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub
 
Upvote 0 Downvote
You really need to learn how to model your business calculations in data rather than huge nested "If" statements. At least learn to use Select Case rather than a ton of nested IF...Else...

Did you ever check the resulting SQL view of your query qryJG9PLUS?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Upvote 0 Downvote
two things at first glance.

1) there are no spaces between the end of the sum and the AS statement

2) I never put semicolons. Maybe that's a bad thing on my end.

Try those also debug and see what the values of the string variables are (todate, fromdate, etc ..)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

serino
  • Locked
  • Question Question
Help with a Query.
Replies
13
Views
1K
strongm
strongm
C
  • Locked
  • Question Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
609
OzFoxy
OzFoxy
BugZap13
  • Locked
  • Question Question
Need help creating a date from two fields to be used in where clause
Replies
5
Views
872
mikrom
mikrom
mhshane
  • Question Question
Is this the best way to do this query?
Replies
9
Views
604
mhshane
mhshane
C
  • Locked
  • Question Question
Query expression negative to a 0
Replies
6
Views
748
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top