show criteria from form on report

[Caitlin11]

I have searched the posts on this site and cannot find anything that fully answers my quesiton/solves my problem.

I have a form that has all unbound text boxes on it. It has 12 different boxes. I then have a report that is based on a query. The query filters the data based on the info that is entered on the form with the unbound text boxes. The user may enter any combo of info in the boxes. For example - they can enter name, dob and country OR they could just enter Name.

All of that works fine. I have a button on my form to pull up the report and it filters the data correctly. My problem is that I want to be able to show the criteria that the user enterd on the report. I've seen lots about showing date ranges but this is a little different since the user may or may not enter data in all fields. If the user enters name and dob in the form - I would like the report header to show something like "where name = 'XXX' and DOB = mm/dd/yy" I'm just not sure how to tell it to show ONLY the info that the user filled in on the form. Any ideas???

Thanks in advance!
Caitlin

 

[RichO]

In your query criteria for each field you can use something like:

Like IIf(IsNull([Forms]![MyForm]![MyTextBox]),"*","*" & [Forms]![MyForm]![MyTextBox] & "*")

That way if the textbox is null, the query returns all records, otherwise it returns records containing the text in the referenced text box.

 

[Caitlin11]

Hi Rich,

thanks for the reply. I have that part working. I just want to be able to put the search criteria that the user enters on the report. But - I only want to show the criteria that they entered. In other words if they enter name but leave DOB blank - then I want the report to say "Report where name = XXX".
See what I'm saying?

Thanks for any help you can provide.

Caitlin

 

[RichO]

What you would need to do is write a piece of code beneath your form. Before the report opens, concatenate a string made up of the chosen criteria. I would add an unbound text box to your form and make it non-visible. Then you can store your string in there and reference it from your report. The code would be a bit tedious because you need an IF...END IF for each textbox, but something like this:

Dim str As String
str = "WHERE "
If Nz(Me.txtName) <> "" Then
     str = str & "Name = " & Me.txtName & " AND "
End If
If Nz(Me.txtDOB) <> "" Then
     str = str & "D.O.B. = " & Me.txtDOB & " AND "
End If

[i]etc...[/i]

If Len(str) = 6 then
     str = ""    [COLOR=DarkGreen]'No criteria chosen[/COLOR]
Else
     str = Left$(str, Len(str)-5)    [COLOR=DarkGreen]'Strip final ' AND ' from end of string[/COLOR]
End If
Me.txtCriteriaString = str    

DoCmd.OpenReport .....

 

[Caitlin11]

Hi Rich,

Thanks for the code. That should work perfectly.

Caitlin