Choices for report

[VeryNew]

Hello,
I have a report that the user would like to have quite flexible. sometimes they'd like it limited to a certain date the next time - all dates and only specific employees etc. I know how to do a parameter query - and i know how to have a report linked to a form so they can enter the data. What would be the best way to make this more flexible?

Is there any way to link the fields on the report to a form but if the field on the form is left blank to assume 'all'?

Hope this makes sense
Thanks so much for your time

 

[KenReay]

Hi

No problem with assuming all if blank, my usual technique is so:

For choices such as one Employee ot All, have a two column Combo box based on Employee table, colums are EmployeeId, EmployeeName, column widths 0;4cm.

Source for Combo is something like

SELECT EmployeeID, EmployeeName FROM tblEmployee
UNION
SELECT &quot;*&quot; As EmployeeId, &quot;<All>&quot; As EmployeeName FROM tblEmployee
ORDER BY EmployeeName

The criteria of the query on which the report is based is then LIKE cboEmployee

For Date ranges, I would usually casue it to default to a very early and very late date, causing all dates to be chosen, if User is picky and does not like taht, use hidden fields which default as above and are populated in the after update events of teh fields the user sees and updates. Base you query criteria on the hidden fields on the form.

Hope this helps Regards

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

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

UK

 

[VeryNew]

Thank you Ken.

Let me go try that and see if i can make it work.
I appreciate your help.

 

[VeryNew]

Ken
Can you explain what your SQL statement is doing? sorry - it isnt working but i am not getting an error so i am not sure what i have done wrong.

thanks

 

[KenReay]

Hi

It is meant to produce a selection from a table, plus a row containing an asterisk in col1, and <All> in col2

I assume you have used your own table and column names?

When you say 'not working' do you mean it returns no rows? Regards

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

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

UK

 

[VeryNew]

Thanks Ken
In the report - if on the form the user selected * - the report shows * instead of the field value (such as Division 2).
I found your faq post on this as well. I will try again.

Thanks