Syntax error (missing operator) in query expression

Question

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

Answer 1

Try removing '=':

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

Answer 2

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

Now it works.

Thanks,
Jessica