Between function for report filter problem

[tl mike]

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

 

[odin1701]

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 ^^^^

 

[tl mike]

the data is a string numbers, letters, and dashes

 

[odin1701]

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.

 

[tl mike]

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

 

[tl mike]

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

 

[tl mike]

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