Missing Operator

[syaworski]

Thanks in Advance.
I'm trying to pass an an expression into a query but it just keeps telling me I'm missing an operatot in the string expression.

Private Sub Command3_Click()
' Pointer to error handler
'On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDate As String
Dim strSQL As String
Dim TotalProfit 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("qryblankqry") Then
Set qdf = db.CreateQueryDef("qryblankqry")
Else
Set qdf = db.QueryDefs("qryblankqry")
End If
' Get the values from the combo boxes
If IsNull(ComboEarned) Then
strDate = " Like '*' "
Else
strDate = "ComboEarned"
End If
TotalProfit = "[Table1.Earned]-[Table1.Spent]" _

' Build the SQL string
strSQL = "SELECT Table1.DateEarned " & "TotalProfit" & _
"FROM Table1 " & "TotalProfit" & _
"WHERE Table1.DateEarned" & _
"AND TotalProfit" _
' 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, "qryblankqry") = acObjStateOpen Then
DoCmd.Close acQuery, "qryblankqry"
End If
' Open the query
DoCmd.OpenReport "3070 Daily Earned Report", acViewNormal, , , acWindowNormal
Command3_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
DoCmd.Close acForm, "Form1"
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 Command3_Click_exit
DoCmd.Close acForm, "Form1"
End Sub

[matthewspatrick]

' Build the SQL string
strSQL = "SELECT Table1.DateEarned, " & TotalProfit & _
" FROM Table1"

[CreganTur]

At first blush I agree with Matthew- you need to put commas between all of your different field and table names.

However, when I hard-coded a SQL string into VBA it wouldn't allow me to separate the lines using the underscore. It forced me to leave it all in a single unbroken line (which is annoying, but it works).

[syaworski]

Thanks.
It makes it past the build query now. However now it tells me there is a type mismatch if I try to open the query or report. If i remove the connection to strDate it works but the report opens with all the data from the table not just for the date picked in the dropdown. Any thoughts

Private Sub Command3_Click()
' Pointer to error handler
'On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDate As String
Dim strSQL As String
'Dim TotalProfit 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("qryblankqry") Then
Set qdf = db.CreateQueryDef("qryblankqry")
Else
Set qdf = db.QueryDefs("qryblankqry")
End If
' Get the values from the combo boxes
If IsNull(Me.ComboEarned.Value) Then
strDate = " Like '*' "
Else
strDate = "='" & Me.ComboEarned.Value & "' "
End If
'TotalProfit = "[Table1.Earned]-[Table1.Spent]" _
' Build the SQL string
' Build the SQL string
strSQL = "SELECT Table1.* , " & "[Table1.Earned]-[Table1.Spent] as [TotalProfit]" & _
"FROM Table1 " & _
"WHERE Table1.DateEarned" & strDate _
'"AND TotalProfit" _
' 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, "qryblankqry") = acObjStateOpen Then
DoCmd.Close acQuery, "qryblankqry"
End If
' Open the query
'Either way the from or query wont open with strDate passed into the query. It just says type mismatch error
DoCmd.OpenQuery "qryblankqry"
DoCmd.OpenReport "3070 Daily Earned Report", acViewPreview
Command3_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
DoCmd.Close acForm, "Form1"
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 Command3_Click_exit
DoCmd.Close acForm, "Form1"
End Sub

[CreganTur]

It's because you haven't set the WHERE statement.

"WHERE Table1.DateEarned" = strDate

And you might need to bracket the tblName.fldName here as well...not sure though...try it both ways.