Populate report with Multi-field user generated criteria. Where and OpenArgs question

[gottnoskill]

All my experience in Microsoft Access (2010) and VBA comes from creating this one database. I've learned a bit by just doing tutorials here and there, but beyond that I have just kind of found my way through the darkness to where I am now. Now I am stumped.

I have been attempting for about a week now to try and implement a where clause into my report. My goal is to create a report that is populated with user generated input. I am hoping that the way I can do it is equal or similar to how I have my search form setup so the users will find it easy to print the information they need.

After surfing through some peoples Q&A's on google I realized that there solution was very similar to how my buildFilter function is in my database for user searches on my table. So I thought the easiest way I could do this was try and run my buildFilter Function through my report (also the laziest way) and see if it works. Unfortunately I am not sure if it is working or not due to a syntax error I am getting and I can't seem to find the missing operator. Anyways here is the error.

Run-time error '3075':
Syntax error (missing operator) in query expression 'WHERE [VisitorName] like "John Smith*'"

And here is the code

---

Private Sub cmdPrint_Click()
DoCmd.OpenReport "printDesignFunction", acViewPreview, , BuildFilter
End Sub

---

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
Dim tmpW As String
tmp = """"
tmpW = "*"""
varWhere = Null

If Me.txtDate > "" Then
varWhere = varWhere & "[CurrentDate] like " & tmp & Me.txtDate & tmpW & " AND "
End If

If Me.txtTypeOfVisit > "" Then
varWhere = varWhere & "[TypeofVisit] like " & tmp & Me.txtTypeOfVisit & tmpW & " AND "
End If

If Me.txtVisitorsName > "" Then
varWhere = varWhere & "[VisitorName] like " & tmp & Me.txtVisitorsName & tmpW & " AND "
End If

If Me.txtHousing > "" Then
varWhere = varWhere & "[Housing] like " & tmp & Me.txtHousing & tmpW & " AND "
End If

If Me.txtNameOfInmate > "" Then
varWhere = varWhere & "[InmateName] like " & tmp & Me.txtNameOfInmate & tmpW & " AND "
End If

If Me.txtBooking > "" Then
varWhere = varWhere & "[Booking] like " & tmp & Me.txtBooking & tmpW & " AND "
End If

If Me.txtTimeIn > "" Then
varWhere = varWhere & "[TimeIn] like " & tmp & Me.txtTimeIn & tmpW & " AND "
End If

If Me.txtTimeOut > "" Then
varWhere = varWhere & "[TimeExit] like " & tmp & Me.txtTimeOut & tmpW & " AND "
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

---

Again this buildFilter function is made for my search form. It works perfectly fine the way it is for my search form, but when I place it on a report I get the operator missing error.

Two questions.

1. Will finding the missing operator make this function work for user generated criteria?
2. If not, then how should I go about properly creating a report that would allow users to narrow down their results so they can print off desired information.

I've looked into other peoples solutions on this subject and have attempted a few different ways, but have not got the desired results. It seems that the desired end-result can be worked in many different ways depending on how your database is setup.



Sorry for the long winded post, but any help on the subject will be appreciated. Thanks!

[June7]

How are you 'placing it on a report'?

How are you using the constructed string for form? Why is WHERE included in the construct? WHERE is used in SQL statements.

Use debug techniques described in link at bottom of my post.

One of them is to Debug.Print the constructed string to verify correct syntax.

[gottnoskill]

My report is linked to my table. The constructed string runs through a series of unbound boxes and with each input narrows down criteria until you have your desired result. Forgive me if I'm using the term VBA for SQL statements. I'm attempting to use a SQL statement. I'll look into Debug printing. This thread will take day by day communication. Not allowed internet access at work and I do not have Microsoft access at home, but I will debug and see what I can find when I get to work.

[rpeare]

If you're trying to dynamically build your criteria for the query driving your report that's one thing.

If you're trying to apply a filter that's another thing entirely.

when you open a form the me. is referring to the report itself NOT the form where you have your selected items you'd want something more like:

Private Function BuildFilter() As Variant
     Dim varWhere As Variant
     Dim tmp As String
     Dim tmpW As String
     tmp = """"
     tmpW = "*"""
     varWhere = Null
    
    If Me.txtDate > "" Then
     varWhere = varWhere & "[CurrentDate] like " & tmp & forms!formname!txtDate & tmpW & " AND "
     End If

    If Me.txtTypeOfVisit > "" Then
     varWhere = varWhere & "[TypeofVisit] like " & tmp & forms!formname!txtTypeOfVisit & tmpW & " AND "
     End If
     
    If Me.txtVisitorsName > "" Then
     varWhere = varWhere & "[VisitorName] like " & tmp & forms!formname!txtVisitorsName & tmpW & " AND "
     End If

    If Me.txtHousing > "" Then
     varWhere = varWhere & "[Housing] like " & tmp & forms!formname!txtHousing & tmpW & " AND "
     End If
     
    If Me.txtNameOfInmate > "" Then
     varWhere = varWhere & "[InmateName] like " & tmp & forms!formname!txtNameOfInmate & tmpW & " AND "
     End If
      
    If Me.txtBooking > "" Then
     varWhere = varWhere & "[Booking] like " & tmp & forms!formname!txtBooking & tmpW & " AND "
     End If
       
    If Me.txtTimeIn > "" Then
     varWhere = varWhere & "[TimeIn] like " & tmp & forms!formname!txtTimeIn & tmpW & " AND "
     End If
           
    If Me.txtTimeOut > "" Then
     varWhere = varWhere & "[TimeExit] like " & tmp & forms!formname!txtTimeOut & tmpW & " AND "
     End If
     
    If IsNull(varWhere) Then
         varWhere = ""
     Else
         varWhere = "WHERE " & varWhere

         If Right(varWhere, 5) = " AND " Then
             varWhere = Left(varWhere, Len(varWhere) - 5

End If
     End If
debug.print varWhere
     BuildFilter = varWhere

End Function

Notice I put in a DEBUG.PRINT statement before you exit the function, that way you can check to see if it's populating with the values you want

[June7]

The function builds a filter string. Depending on how you use the constructed string, the word "WHERE" might not be needed and could actually cause error.

rpeare, not sure what you mean by the Me. code is referencing report. Seems to me the function is behind form where the referenced controls are and the Me. qualifiers as originally used are fine.

[rpeare]

What I meant was that if he's using that code on a report and he's trying to reference fields on a form that code won't work.

I can't tell by his posts if this code is on the report (on load or on open) or the form (in an on click event or something) he may also be having trouble with quotes within quotes and I'd suggest using single ' marks to denote text within double quotes like:

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
Dim tmpDate as string
Dim tmpW As String
tmp = "'"
tmpW = "*'"

varWhere = Null

----<previous code>
        
If Me.txtVisitorsName > "" Then      
     varWhere = varWhere & "[VisitorName] like " & tmp & forms!formname!txtVisitorsName & tmpW & " AND "      
End If

----<subsequent code>

I would also like to know if your date/time fields are stored as strings.... if they aren't then this code likely won't work either

[June7]

If the code is behind report then the If Then lines also have to change the Me. reference. Hopefully, OP will provide answers to questions tomorrow.

[gottnoskill]

The code calls the function when the search button is pressed and runs it through a table form, but in any case I fixed the error today and the code ran as I wanted filtering the report and giving me the results i desired. I had just checked the help menu in Access on the docmd.openreport statement and it told me there shouldn't be a WHERE statement in whatever you're running. That was my issue. As stated above by June7. I completely took out the WHERE statement after the 'Else' for this part of my function and it runs perfectly in my report now.

Before:

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

After:

If IsNull(varWhere) Then
varWhere = ""
Else

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

I feel dumb after a week trying to figure it out and it was a simple as taking out the WHERE statement.

I appreciate the help! Now I gotta figure out validation rules which shouldn't be too hard and this database is set to go. Thank you so much!