I am setting up some code for a report filter and am having a problem with a between function.

I am trying to get it to show partnumbers between a range that is selected by the user this function is at the bottom of the code, when I try to run it ai get the an error message "Syntax error (missing operator) in query expression '([partnumber] between the partnumber selected And the other partnumber selected)'.

Private Sub cmdGenerateReport_Click()
On Error GoTo Err_cmdGenerateReport_Click

Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.cboProductLine) Then
stWhere = "[ProductLineFK]=" & Me.cboProductLine & " And "
blnTrim = True
End If

If IsNull(Me.txtStartDate) And Me.txtStartDate = "" Then
If Not IsNull(Me.txtEndDate) And Me.txtEndDate <> "" Then
stWhere = stWhere & "[Date] <=" & Me.txtEndDate & "#"
blnTrim = False
End If
Else
If IsNull(Me.txtEndDate) And Me.txtEndDate = "" Then
If Not IsNull(Me.txtStartDate) And Me.txtStartDate <> "" Then
stWhere = stWhere & "[Date]>=" & Me.txtStartDate
blnTrim = False
End If
Else
If (Not IsNull(Me.txtStartDate) And Me.txtStartDate <> "") And (Not IsNull(Me.txtEndDate) Or Me.txtEndDate <> "") Then
stWhere = stWhere & "[Date] Between #" & Me.txtStartDate & "# And #" & Me.txtEndDate & "#"
blnTrim = False
End If
End If
End If




If IsNull(Me.cboPartNumberStart) And Me.cboPartNumberStart = "" Then
If Not IsNull(Me.cboPartNumberEnd) And Me.cboPartNumberEnd <> "" Then
stWhere = stWhere & "[PartNumber] <=" & Me.cboPartNumberEnd & "#"
blnTrim = False
End If
Else
If IsNull(Me.cboPartNumberEnd) And Me.cboPartNumberEnd = "" Then
If Not IsNull(Me.cboPartNumberStart) And Me.cboPartNumberStart <> "" Then
stWhere = stWhere & "[PartNumber]>=" & Me.cboPartNumberStart
blnTrim = False
End If
Else
If (Not IsNull(Me.cboPartNumberStart) And Me.cboPartNumberStart <> "") And (Not IsNull(Me.cboPartNumberEnd) Or Me.cboPartNumberEnd <> "") Then
stWhere = stWhere & "[PartNumber] Between " & [Forms]![frmCostCurrentReport]![cboPartNumberStart] & " And " & Me.cboPartNumberEnd & ""
blnTrim = False
End If
End If
End If

If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If
stDocName = [Forms]![frmCostCurrentReport]![lstReports]
DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_cmdGenerateReport_Click:
Exit Sub

Err_cmdGenerateReport_Click:
MsgBox Err.Description
Resume Exit_cmdGenerateReport_Click

End Sub

Are the part numbers all numerical?

Is the data type a number or a string?

Also:

stWhere = stWhere & "[PartNumber] Between " & [Forms]![frmCostCurrentReport]![cboPartNumberStart] & " And " & Me.cboPartNumberEnd & ""

You have two spaces after Between ^^^^

the data is a string numbers, letters, and dashes

the data is a string numbers, letters, and dashes

I've never used it with straight strings, numbers or date fields before...not sure how it will like dashes. You may not get the expected results when you try to do this with that type of data.

Not sure why you are getting that error though.

Does any one know how to do this I have searched and searched and I know there has to be a way

I have tried this code also with no luck and still get the same error

If Not IsNull(Me.cboPartNumberStart) Then
stWhere = stWhere & "([PartNumber] >=" & Me.cboPartNumberStart & ") And "
End If

If Not IsNull(Me.cboPartNumberEnd) Then
stWhere = stWhere & "([PartNumber] < " & Me.cboPartNumberEnd & ") And "
End If

I am trying something else and am getting a compile syntax error with the following code

stWhere = stWhere & "([PartNumber] Between " & [Forms]![frmCostCurrentReport]![cboPartNumberStart] & " And " & [Forms]![frmCostCurrentReport]![cboPartNumberEnd] & "")


Attached is a copy of the db