Query By Form

[geniechua]

Hi

I have a database that stores employees' records in a company. I would like to run a report using a form [fmQueryStaffListing], such that if I key in:



As at Date: 15 Aug 2011
Print for: Active
Select Employee Type: Full-Time
Select Employee Group: National Staff
Select Department: -blank-
Select Designation: -blank-

the report should reflect as per attachment (Report.jpg). Can anyone show me how that can be done?

[June7]

Check out tutorials here http://www.datapigtechnologies.com/AccessMain.htm

For one method look at:
'Build a Custom Filter in Your Form' under Access Forms:Tips and Techniques
You can apply that technique to the RecordSource of the report.

Alternative is to use the FilterName or WhereCondition argument of DoCmd.OpenReport. See
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[geniechua]

Hi June,

Thanks for that. However, the tutorials do not seem to have the solution I want. Not sure if my previous question was clear enough so I'll try to explain it again...

Say for example, I have Employee A who joined as an Admin Assistant in 10/01/10. He is then promoted to Admin Officer in 10/01/11. What I want to do is that when I run a query to find what position he was as at 30/12/10, the result I should get is Admin Assistant. How can this be done?

[June7]

This is still filtering records and the tutorials still offer a guide on how to pass criteria to query. Just that dates can be a little trickier. For one thing, you show dd/mm/yy format. Access convention is mm/dd/yyyy unless you set your system date format to dd/mm/yyyy, Access might adjust but I have seen postings where people have problem trying to query date criteria in dd/mm/yyyy.

The query criteria could be to return all of this employee's records dated earlier than 10/01/11, order by date descending. If Admin Assistant assignment was the last it should show at top of list.

You could have criteria to return records where 30/12/10 is >= promotion date field.