search form errors out

[littlecool]

Hi all,

I have a form that the user can use to sort records on another form. Using other examples found on here I made Coding to do so. From what I can see it should work, however when I run it I get the following message:

Run-Time error '3075:

Extra > in query expression '([Shift] = "") AND ([Equipment] = "Tailseal") AND ([Line] = "79*") AND ([Line] = "62*") AND ([RecordNumber] = )'.

I've tried various things to correct the error but I can't find what I'm missing. Here is my code. Any help or suggestions is greatly appreciated!

Private Sub cmdGo_Click()
 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboTeam) Then
        strWhere = strWhere & "([Team] = """ & Me.cboTeam & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboShift) Then
        strWhere = strWhere & "([Shift] = """ & Me.cboShift & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboWho) Then
        strWhere = strWhere & "([ION] = """ & Me.cboWho & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip1) Then
        strWhere = strWhere & "([Equipment] = """ & Me.cboEquip1 & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip2) Then
        strWhere = strWhere & "([Equipment] = """ & Me.cboEquip2 & """) AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip3) Then
        strWhere = strWhere & "([Equipment] = """ & Me.cboEquip3 & """) AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip4) Then
        strWhere = strWhere & "([Equipment] = """ & Me.cboEquip4 & """) AND "
    End If
    
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineA) Then
        strWhere = strWhere & "([Line] = """ & Me.cboLineA & """) AND "
    End If
    
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineB) Then
        strWhere = strWhere & "([Line] = """ & Me.cboLineB & "*"") AND "
    End If
    
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineC) Then
        strWhere = strWhere & "([Line] = """ & Me.cboLineC & "*"") AND "
    End If
    
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineD) Then
        strWhere = strWhere & "([Line] = """ & Me.cboLineD & "*"") AND "
    End If
        
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtRecord) Then
        strWhere = strWhere & "([RecordNumber] = " & Me.txtRecord & ") AND "
    End If
    
'    'Number field example. Do not add the extra quotes.
'    If Not IsNull(Me.cboFilterLevel) Then
'        strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ") AND "
'    End If
'        'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.cbxJobType) Then
'        strWhere = strWhere & "([ActType] Like ""*" & Me.cbxJobType & "*"") AND "
'    End If
    
    
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cbxJobType) Then
        strWhere = strWhere & "([ActType] = """ & Me.cbxJobType & "*"") AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cbxCauseType) Then
        strWhere = strWhere & "([CauseType] = """ & Me.cbxCauseType & "*"") AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboFollowUp) Then
        strWhere = strWhere & "([FollowUp] = """ & Me.cboFollowUp & "*"") AND "
    End If
    
    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
'    If Me.cboFollowUp = -1 Then
'        strWhere = strWhere & "([FollowUp] = True) AND "
'    ElseIf Me.cboFollowUp = 0 Then
'        strWhere = strWhere & "([FollowUp] = False) AND "
'    End If

    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.tbxFrmDate) Then
        strWhere = strWhere & "([Date] >= " & Format(Me.tbxFrmDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.tbxToDate) Then   'Less than the next day.
        strWhere = strWhere & "([Date] < " & Format(Me.tbxToDate + 1, conJetDate) & ") AND "
    End If

    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        DoCmd.OpenForm "frmLgBk", acNormal, "", strWhere

        Me.Filter = strWhere
        Me.FilterOn = True
    
    'DoCmd.OpenReport "Report1", acViewPreview, , strWhere
    End If
End Sub

 

[plog]

First, you do realize that no record can ever pass this:

([Line] = "79*") AND ([Line] = "62*")

If a line starts with 79 it fails the second test, If it starts with 62 it fails the first test, if it starts with neither it fails both, ergo it can never pass.

Second, isolate the error. Strip that code down until it always works, then add lines of code back to it one by one until it breaks. When it does, there's the culprit.

 

[vba_php]

From what I can see it should work, however when I run it I get the following message:

Run-Time error '3075:

Extra > in query expression '([Shift] = "") AND ([Equipment] = "Tailseal") AND ([Line] = "79*") AND ([Line] = "62*") AND ([RecordNumber] = )'.

is it possible that access does not like this?

    If Not IsNull(Me.cboShift) Then
        strWhere = strWhere & "([Shift] = """ & Me.cboShift & """) AND "
    End If

??? the error message says the arg for [shift] is a zero length string, but the error message also encapsulates it's SQL output with single quotes ('). your code has 3 doubles in it:

"""

is SHIFT a string type? if so, maybe try:

     If Not IsNull(Me.cboShift) Then
        strWhere = strWhere & "([Shift] = '" & Me.cboShift & "') AND "
    End If

if that works, you will have to change that for all your strWhere lines in your entire code too, as they are all incorrect.

 

[MajP]

Besides what plog pointed out I think you are trying to use LIKE but instead you are using equal
I doubt you have a line number equal to 79*
[Line] LIKE '79*'

 

[littlecool]

I'm years out of practice which doesn't help, but Thank you VBA_php. I see now that I didn't declare the variables ([Shift] etc)as I need to.

A new question to go with this. the Line field is a multi value field. Because of that it doesn't like using it in a where or having clause. What would be a work around for that?

Here's what I'm doing to make the reset of the code work.

 Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    Dim Team As String
    Dim Shift As String
    Dim ION As String
    Dim Equipment As String
    
    
    

    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboTeam) Then
        strWhere = strWhere & "(Team = """ & Me.cboTeam & """) AND "
    End If

'    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboShift) Then
        strWhere = strWhere & "(Shift = """ & Me.cboShift & """) AND "
    End If

 

[littlecool]

for clarification: My Line field has multiple lines in it ranging from 70-85, with some having letters in it as well. So I have records that may apply to a single line or multiple.

Not sure if this helps

 

[theDBguy]

Hi. Just curious, are you able to post a demo version of your db?

 

[littlecool]

Here's my DB. frmLgBk is the main form where records are added and reviewed. frmSortRecords is what sorts the displayed records of frmLgBk

 

[plog]

You need to reevaluate your relationships. No point making forms if your data isn't properly structured.

I am looking at the Relationship Tool of the database you posted. While the individual tables/fields don't seem to have any major issues that pop out, your relationships do. There should only be one way to trace a path between any two tables of your relationship, you've created loops in your relationships. For example:

50tblLgBk is related to 50tblAreaSub via [Sub Area] to [Sub Area]
50tblAreaSub is related to 50tblEquipment via [GroupID to [GroupID]
50tblEquipment is related to 50tblLgBk via [ID] to [Equipment]

That's incorrect and one of those has to go. And that's just a simple example, your whole relationship tool is a spider web of connected tables (50tblLine->50tblArea->50tblLgBk->50tblAreaSub->50tblEquipment->50tblLgBk_1->50tblLine).

You need to address your relationship issues before you proceed with your forms. Probably might make them simpler once you have your head around how your data properly relates.

 

[littlecool]

I Hope everyone is having a great Holiday season!

So I addressed the relationship issue, and now have them pathed properly (to the best of my knowledge). My Search form works ok. I do have issues with trying to search for multiple equipment. If I search with using only 1 of 4 equipment boxes it works fine. If I add additional equipment thru the other 3 combo boxes, it's random as to what it returns. I've tried mixing and's with or's with no improvement. Also, I would like to do the same searching by up to 4 line values, but line is a multi-value field and the coding I've seen that is suppose to work with those, have not worked for me. Any additional help would be appreciated.

Here is my current form/search coding:

Private Sub cmdGo_Click()
 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    Dim Team As String
    Dim Shift As String
    Dim ION As String
    Dim Equipment As String
    Dim Line As String
    Dim RecordNumber As String
    Dim ActType As String
    Dim CauseType As String
    Dim FollowUp As String
    Dim DaDate As String
    
    
    
    

    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboTeam) Then
        strWhere = strWhere & "(Team = """ & Me.cboTeam & """) AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboShift) Then
        strWhere = strWhere & "(Shift = """ & Me.cboShift & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboWho) Then
        strWhere = strWhere & "(ION = """ & Me.cboWho & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip1) Then
        strWhere = strWhere & "(Equipment = """ & Me.cboEquip1 & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip2) Then
        strWhere = strWhere & "(Equipment = """ & Me.cboEquip2 & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip3) Then
        strWhere = strWhere & "([Equipment] = """ & Me.cboEquip3 & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip4) Then
        strWhere = strWhere & "([Equipment] = """ & Me.cboEquip4 & """) AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineA) Then
        strWhere = strWhere & "(Line = """ & Me.cboLineA & """) AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineB) Then
        strWhere = strWhere & "([Line] = """ & Me.cboLineB & "*"") AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineC) Then
        strWhere = strWhere & "([Line] = """ & Me.cboLineC & "*"") AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboLineD) Then
        strWhere = strWhere & "([Line] = """ & Me.cboLineD & "*"") AND "
    End If

'        'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.txtRecord) Then
'        strWhere = strWhere & "(RecordNumber = " & Me.txtRecord & ") AND "
'    End If
'
'
'
'        'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.cbxJobType) Then
'        strWhere = strWhere & "(ActType = " & Me.cbxJobType & ") AND "
'    End If
''
''        'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.cbxCauseType) Then
'        strWhere = strWhere & "(CauseType = " & Me.cbxCauseType & ") AND "
'    End If
''
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboFollowUp) Then
        strWhere = strWhere & "(FollowUp = " & Me.cboFollowUp & ") AND "
    End If
'
'    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
''    If Me.cboFollowUp = -1 Then
''        strWhere = strWhere & "([FollowUp] = True) AND "
''    ElseIf Me.cboFollowUp = 0 Then
''        strWhere = strWhere & "([FollowUp] = False) AND "
''    End If
'
'    'Date field example. Use the format string to add the # delimiters and get the right international format.
'    If Not IsNull(Me.tbxFrmDate) Then
'        strWhere = strWhere & "(daDate >= " & Format(Me.tbxFrmDate, conJetDate) & ") AND "
'    End If
'
'    'Another date field example. Use "less than the next day" since this field has times as well as dates.
'    If Not IsNull(Me.tbxToDate) Then   'Less than the next day.
'        strWhere = strWhere & "(daDate < " & Format(Me.tbxToDate + 1, conJetDate) & ") AND "
'    End If

    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        DoCmd.OpenForm "frmLgBk", acNormal, "", strWhere

        Me.Filter = strWhere
        Me.FilterOn = True
    
    'DoCmd.OpenReport "Report1", acViewPreview, , strWhere
    End If
End Sub

 

[plog]

...
If Not IsNull(Me.cboEquip1) Then
        strWhere = strWhere & "(Equipment = """ & Me.cboEquip1 & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.cboEquip2) Then
        strWhere = strWhere & "(Equipment = """ & Me.cboEquip2 & """) AND "
    End If
...

If you input 2 different pieces of equipment it should return no results. This is very similar to the logical issue I pointed out in my initial post. If you enter 2 different piecees of equipment there is no way any data passes your filter string:

cboEquip = Hammer
cboEquip2 = Saw

strWhere = "...(Equipment = Hammer) AND (Equipment = Saw) AND ..."

Equipment cannot be two values at the same time. If a record has Hammer for its Equipment then it fails the second test because its not Saw. If a record has Saw for its Equipment then it failes the first test because its not Hammer. If its something else it fails both tests.

Most likely you need to incorporate ORs between Equipment tests and group them all together:

"...AND ((Equipment = Hammer) OR (Equipment = Saw)) AND..."

 

[littlecool]

I really appreciate the help/info everyone has been providing. One more issue I'm having is I can't sort my records between 2 dates. I'm still using the coding as seen here: http://allenbrowne.com/ser-62code.html

but I end up with a Runtime error 13: Mismatch. I'm not seeing where the mismatch could be. The field on the table is a date table. The controls on the search form are text boxes. Here is my actual code:

Private Sub cmdGo_Click()
 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
    Dim Team As String
    Dim Shift As String
    Dim ION As String
    Dim Equipment As String
    Dim Line As String
    Dim RecordNumber As String
    Dim ActType As String
    Dim CauseType As String
    Dim FollowUp As String
    Dim Area As String
    
    
    
    

    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    
 '   between Forms![ParameterForm]![txtBeginOrderDate] And Forms![ParameterForm]![txtEndOrderDate]
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.tbxFrmDate) Then
        strWhere = strWhere & "([Date] >= " & Format(Me.tbxFrmDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.tbxToDate) Then   'Less than the next day.
        strWhere = strWhere & "([Date] < " & Format(Me.tbxToDate + 1, conJetDate) & ") AND "
    End If
    


    
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboTeam) Then
        strWhere = strWhere & "(Team = """ & Me.cboTeam & """) AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboShift) Then
        strWhere = strWhere & "(Shift = """ & Me.cboShift & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboWho) Then
        strWhere = strWhere & "(ION = """ & Me.cboWho & """) AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboFollowUp) Then
        strWhere = strWhere & "(FollowUp = """ & Me.cboFollowUp & """) AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.CboArea) Then
        strWhere = strWhere & "(Area = """ & Me.CboArea.Column(1) & """) AND "
    End If


    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cboEquip1) Then
        strWhere = strWhere & "(Equipment = """ & Me.cboEquip1 & """) AND "
    End If
    
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cbxJobType) Then
        strWhere = strWhere & "(ActType = """ & Me.cbxJobType & """) AND "
    End If

        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cbxCauseType) Then
        strWhere = strWhere & "(CauseType = """ & Me.cbxCauseType & """) AND "
    End If



        'Another text field example. Use Like to find anywhere in the field.
'''    If Not IsNull(Me.cboLineA) Then
'''        strWhere = strWhere & "(Line = ""*" & Me.cboLineA.Column(2) & "*"") AND "
'''    End If
    

'''''''''If Not IsNull(Me.cboLineA) Then
'''''''''        For Each varItem In Me.cboLineA
'''''''''            strProjectIDList = strProjectIDList & "," & ctrl.ItemData(varItem)
'''''''''        Next varItem
'''''''''
'''''''''        ' remove leading comma
'''''''''        strProjectIDList = Mid(strProjectIDList, 2)
'''''''''
'''''''''        strCriteria = "EmployeeID IN" & _
'''''''''            "(SELECT EmployeeID FROM ProjectEmployees " & _
'''''''''            "WHERE ProjectID IN (" & strProjectIDList & "))"


'        'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.txtRecord) Then
'        strWhere = strWhere & "(RecordNumber = " & Me.txtRecord & ") AND "
'    End If
'
'
'
''
'
'    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
''    If Me.cboFollowUp = -1 Then
''        strWhere = strWhere & "([FollowUp] = True) AND "
''    ElseIf Me.cboFollowUp = 0 Then
''        strWhere = strWhere & "([FollowUp] = False) AND "
''    End If


    



    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        DoCmd.OpenForm "frmLgBk", acNormal, "", strWhere

        Me.Filter = strWhere
        Me.FilterOn = True
    
    'DoCmd.OpenReport "Report1", acViewPreview, , strWhere
    End If
End Sub