question

JessicaDellario-6168 avatar image
0 Votes"
JessicaDellario-6168 asked JessicaDellario-6168 commented

Syntax error (missing operator) in query expression

I am trying to modify a form to use a date range instead of a single date.

Here is the original code:

Private Sub cmdprt_Click()
On Error GoTo Err_cmdprt_Click

 Dim stDocName As String, stCrit As String
 stCrit = "[id] <> 0"
    
 If Not IsNull(RecStart) And RecStart <> 0 Then
   stCrit = stCrit & " and [id] >= " & [RecStart]
 End If
 If Not IsNull(RecEnd) And RecEnd <> 0 Then
   stCrit = stCrit & " and [id] <= " & [RecEnd]
 End If
 If Not IsNull(EDDate) Then
   stCrit = stCrit & " and [Ed_Date] = #" & EDDate & "#"
 End If
      
    
 stDocName = "rptDisposalCheckList"
 DoCmd.OpenReport stDocName, acPreview, , stCrit

Exit_cmdprt_Click:
Exit Sub

Err_cmdprt_Click:
MsgBox Err.Description
Resume Exit_cmdprt_Click

End Sub

That works for the single date.

I changed this:

If Not IsNull(EDDate) Then
stCrit = stCrit & " and [Ed_Date] = #" & EDDate & "#"
End If

To this:

 If Not IsNull(EDDate) And Not IsNull(Range) Then
   stCrit = stCrit & " & [Ed_Date] = Between" & " " & "#" & [EDDate] & "#" & " " & "And" & " " & "#" & [Range] & "#"
 End If

I've also tried it like this:

 If Not IsNull(EDDate) And Not IsNull(Range) Then
   '[Ed_Date] = "Between" & " " & "#" & [EDDate] & "#" & " " & "And" & " " & "#" & [Range] & "#"
   stCrit = (stCrit) & (" & [Ed_Date] = Between" & " " & "#" & [EDDate] & "#" & " " & "And" & " " & "#" & [Range] & "#")
 End If

I get the same error either way.
"Syntax error (missing operator) in query expression '[id} <>0 & [Ed_Date] = Between #7/1/2021# And #7/31/2021#'.

Any ideas what I'm missing?

Thanks,
Jessica

windows-forms
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Removing the '=' throws a "Data type mismatch in criteria expression."

Any other ideas?

Thanks,
Jessica

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 JessicaDellario-6168 ·

It seems that the problem was solved.

Try determining the fields that cause the error. For example try a simple “[id] <> 0” condition, then a simple “[Ed_Date] Between #7/1/2021# And #7/31/2021#”. Maybe Id is not a number or Ed_Date is not a date? Show some details about these fields.

0 Votes 0 ·

The problem wasn't solved. ID and Ed_Date were part of the original code. That worked.

I added a text box to the form called Range to be able to select a date range and formatted the field to be the same as Ed_Date, Short Date format. I then tried to modify the code to make it work. That is where I am stuck.

ID is an AutoNumber.

On the form you entered a date or an ID and pressed the button and it ran a report for that date or ID. If you needed a range of IDs that option is there. There was no option for a date range so that is what I'm trying to fix.

Thanks,
Jessica

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Try removing '=':

[id] <> 0 And [Ed_Date] Between #7/1/2021# And #7/31/2021#


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JessicaDellario-6168 avatar image
0 Votes"
JessicaDellario-6168 answered

Had to change a & to And and remove the = after Ed_Date.

Now it works.

Thanks,
Jessica

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.