Multiple Filters on a single Report

[steve711]

Hello Everyone,

Here's the situation.

I have a from that opens allowing my pilots to select there name and the date they wish to view a flight report (entire month). I have managed to make the filter so that only the selected pilot's flight sheet appears with his data listed in the report sorted by date.

Problem is right now the report shows ALL the data for that pilot, that means everything that is in the table with his name on it regardless of the date.

I really need it to use his name and the Month & Year he entered and display just that on the report.

For example Pilot X selected his name and typed in March 2004...The report would now show Pilot X and JUST March flights.

I hope this is clear and I am assuming I am overlooking something very easy here. I would prefer to do this in visual basic.

Thanks everyone.

 

[o1110010]

For example Pilot X selected his name and typed in March 2004...The report would now show Pilot X and JUST March flights.

Hey steve!

In the query, you can use the DatePart worksheet function to solve this problem.

I recommend having three combo boxes. One corresponding to pilotName, reportMonth, & reportYear. I'd have the pilotName combo box pull the names from a table. I'd have the months & year combo boxes have their row source come from a value list. Follow so far?

Then you can reference the month & year (easier when seperated) from form to use in your query expressions/criteria.

 

[steve711]

Hey steve!

In the query, you can use the DatePart worksheet function to solve this problem.



Thanks, however I'm not folllowing you exactly. I understand what you are getting at but I don't see how I can use this in Access. A worksheet function?

It may be a bit easier to break this out using 3 drop downs but I am trying to minimize the complications to the users as some of them are struggling just to figure out how to click on the arrow of a drop down. Long story.

Two combo boxes/dropdowns is what I'd like to use and coding something to break out the month and year would be fine. I just can't seem to wrap my hands around this solution.

 

[o1110010]

A worksheet function?

Sorry. Access Help refers to all those expression functions as worksheet functions. Date() DatePart() DateDiff() etc. It's probably just so they don't have to write a new Help file for the same function when it doesn't change much per application.

Perhaps an actual touch-n-feel example will help you understand what I meant better than words. I attached an Access 2000 database. It's definately nothing pretty since it's just an visual explanation.

 

[steve711]

Thanks for the help. I have not been able to get your example to really work for me the way I need it to. Part of the problem, I believe, is that I am not building the report off of a query but straight off of my table.

April is approaching, rapidly, I must get this resolved soon.