Using a Form to Filter a Report

[clueless1376]

I am trying to generate a report based on selections that a user would make on a form.

I have 4 Fields that I would like to filter my reports by.

Date - I have the date a tasking is entered and I would like to give the option of a starting and ending date for the report to cover.

Person Tasked - Drop down box to select any one from the staff.

Completed - A checkbox that would filter by if the task is completed or not.

Location - A drop down box that would contain our forward office and our rear office.

Any help on writing the code for this would be creatly appreciated.

Thank you,

Clueless1376

 

[KenReay]

Hi

Should not really need and code

make you form , put the controls on (ie two text boxes to accept from/to date, combo box to accept Person Tasked etc)

Put a button on the form to run the report (Wizard will guide you thru this)

In the Query on which the report is based, set criteria eg on DateTaskingEntered put criteria of Between Forms!MyForm!txtDateFrom AND Forms!MyForm!txtDateTo, on Person Tasked put criteria Forms!MyForm!cboPersonTasked ...ect for other fields)

Using your own control and form names of course

Only limitation is that the Form MyForm must be open when the report runs (you can set its visible property to False if you do not want user to see it)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[clueless1376]

Thank you for the quick reply. I have made some head way with what you gave me.

I am running into a problem or two though.

The date portion is working fine.

The combobox selection is not working though. When I select the person, it just opens up a blank report.

Also, I was wondering how I write it to make a blank box return all of that type.

Thanks again for the help.

 

[KenReay]

Hi

You could extend your query criteria to be:

WHERE (PersonName = Forms!MyForm!cboPersonName) Or IsNull(Forms!MyForm!cboPersonName)

or (probably) better add an all option to the combo box, and use LIKE instead of equals

to add all option, you need to use a union query, something like

SELECT PersonId, PersonName FROM tblPeople
UNION
SELECT "*" as PersonId, "<All>" as PersonName FROM tblPeople
ORDER BY PersonName

using your own table and column names etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK