Report help

[Ternick]

I made a program to track invoices and the commission paid on them, but hit a snag with my reports. I am asking for help with setting a list box to show all reports, beable to filter the reports by date ranges, Customer and/or driver. I have fount a few ways to do the date range, but all of them are for single reports.

I also need help with a macro so when I click the payroll button it will pull up all invoices not paid with a cut off of the last friday (most likey I will just have to put in last day of pay period and limit off that date), run the report, then change all invoices listed as paid with the next fridays date (also will most likey do it where I put in that pay day date). It would be nice if I can bring up a box that asks if I want to run payroll.

[Ternick]

Ok so I found a way to filter by driver, provider and date all at the same time, but the problem is if any field is left blank it returns no result. I want it so if a field is left blank it won't omit any results based off that field.

[ItsMe]

Ok so I found a way to filter by driver

When you use criteria, you need to specify whether or not you want to retrieve fields that are Null. One approach would be something like this.
WHERE DriverID = 12 AND DriverID IS NULL

[Ternick]

I was using the criteria field in the query. From what you are saying is on the 2nd criteria box do something like "= null"

[Ternick]

ok didn't work like i tryed. So i will use drivers as my example... In the query under the driver i have for criteria box 1 "= [form]![reports]![combdriver]" (it pulls a list from my driver table so only drivers in the system are shown). I put in box 2 "if [form]![reports]![combdriver] = null then show all" On the invoices the driver must be listed, but we need to do searches that are not based off of the driver.

[ItsMe]

I am not sure exactly what you are after. I believe I made a mistake using the AND operator. If you want to retrieve all of the driver ID's that do not have a value and also all of the driver ID's that are equal to 12, you would type the following into the criteria field.
=12 OR Is Null

[Ternick]

I think I explained how my tables are and how im trying to search. Under my invoices there is a driver field. This field has to be filled in or it won't let you finish the invoice. I need a way to search all the invoices between dates (already set that), sometimes to one driver and sometimes by another field. With how I have the search right now, if you don't put in a driver you want to search for, lets say I want all invoices billed to XYZ company, then nothing comes up as all the invoices have data in the driver field. So i would have to put in the search for company XYZ driver ABC, then run it again for company XYZ driver def.....I need it easyer

[June7]

If you are using dynamic parameterized query (I NEVER do), what you want won't be easy if search includes non-text type fields.

Here is my preferred method http://www.allenbrowne.com/ser-62.html

[ItsMe]

Yeah, I am still not understanding the criteria you are after. That Is why I posted a couple of examples, to maybe spark a thought or two. I suspect your best bet is to create a search form as recommended by June. If there is a specific criteria you need help with or adding a value to your criteria from a specific control, let us know.

[Ternick]

i'll check into the link June. Thank you for the help so far. I suck at macros and still have to figure out how to do the payroll part

[ItsMe]

You can always build a query object using the Query Designer. Hard code values into the criteria field. You can open a table in datasheet view to determine a value you want to hard code into your query as criteria. Afterwards, you can look at the SQL of your query object by changing from Design View to SQL View. If you post some SQL that contains your criteria here, you might be able to ask a question to a specific part of your problem. Post #6 has an example of an expression you can type into a criteria field.

[Ternick]

Thank you all. The link June gave me does most of my search I need. I think I can build on it to add search my filters. Im going to mark as solved and make a new post for my payroll question. I would really like to say thank you to Itsme for dealing with my confussing posts and really trying to help.