I am using a form with unbound fields to filter for a dozen different reports. It basically allows the user to enter a potential lastname, first name, start date and/or enddate. My problem is that I want the user to be able to enter in any one piece of information or any combination of them but as soon as one of the fields are left blank the data pulled is incorrect or blank. If I complete each field in my form the data pulled is perfect. I would like the user to be able to generate the reports i.e All records for a given user, All records for a given user with a given date range and/or all records for all users within a given date range. What am I doing wrong?

P.S. The place where I work does not allow us to download anything.

Thank you in advance for your assistance!!

-Tami

The problem you are having is that you are using the same query for all cases. That, as you have noticed, won't work.

What I do is develop separate queries for each case the users need. Then, I place code in the Preview Report button that I have placed on the form that looks to see what they have selected, opens the report in Design View, sets the report recordsource to the appropriate values and opens the report in Print Preview. You could, of course, have separate reports for each case as well.

Actually, it works kind of nice. I have a report menu with a list box that displays the different reports and instead of opening the actual report, it opens the user input form the allows for names and/or dates to be selected, then they click on the Print Report button that will print the highlighted report based on the fields completed in the unbound form. Each report has it's own query and I think my queries are inaccurate. Between [Forms]![Report Prompt Window]![Start] And [Forms]![Report Prompt Window]![End] AND IN THE NAME FIELDS Like [Forms]![Report Prompt Window]![LastName] ETC. But I'm using an AND statement, I would like the query to work if the user leaves the field blank but don't know how to do it. Am I way off track?

No, not way off track, but you'll have to fiddle with the query's WHERE clause to get what you want. This is best done via code.

After figuring out which query is going to be opened, you need to append to that query's SQL statement the WHERE clause that corresponds to what the users have clicked on the form.

So, if all they have clicked are the Start and End dates(?) then the WHERE clause would be something like:

"WHERE DateFieldNameGoesHere >= " & "#" & [Forms]![Report Prompt Window].[Start] & "#" AND DateFieldNameGoesHere <= " & "#" & [Forms]![Report Prompt Window].End & "#"

And it can get worse than this. Some of the operators you are used to in the Query window do not work in the code window.

You are wonderful! Thank you. I'll try it and make it work! THANKS AGAIN!

Something like this, using DAO ( I have not yet learned ADO):

Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim WhereClause As String
Dim SQLString As String
Dim WherePosition As Integer

Set db = CurrentDb()
Set qdef = db.QueryDefs("NameOfQueryGoesHere")

WherePosition = InStr(1, qdef.SQL, " WHERE ")
SQLString = left(qdef.SQL, WHERE - 1)

If Me.StartDate <> "" AND Me.EndDate <> "" THEN
WhereClause = "WHERE TableName.StartDate >=" & "#" & Me.StartDate & "#" & " AND " & "TableName.EndDate <= " & "#" & Me.EndDate & "#"

qdef.SQL = SQLString & WhereClause

Else
MsgBox "You must enter a start date and an end date"
End
End If