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

[tbonehwd]

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

 

[jrbarnett]

What's in the strADVACCT, strFRMDATE and STRTODATE variables?

John

 

[tbonehwd]

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

 

[jrbarnett]

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

 

[tbonehwd]

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

 

[dhookom]

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]

 

[JoeSmoe55]

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 ..)