Hi,

I have created a Form in Access. I have used a parameter query so when the user runs a report for a specific quarter, they can enter the dates they want. The problem is that none of the records are showing up when the date parameters are set.

Another issue I am having with reports, is that on the same form, I have departments set up as a combo box. I want to be able to filter the various departments on the reports. I know I need to set up queries for this, but how exactly would I do this based on a combo box?

Thanks to all who respond.

zip it up and we will have a look -
is your underlying qry loking at the form you run the report from ?

i have mine look at two fields from and to and then a filter on a combo box all on the same form - works ok -

Hi,

I have created a Form in Access. I have used a parameter query so when the user runs a report for a specific quarter, they can enter the dates they want. The problem is that none of the records are showing up when the date parameters are set.

Another issue I am having with reports, is that on the same form, I have departments set up as a combo box. I want to be able to filter the various departments on the reports. I know I need to set up queries for this, but how exactly would I do this based on a combo box?

Thanks to all who respond.

I would not use a Parameter Query as such - I prefer to write a standard SQL Query to return all relevant data - on the form put the two text boxes for the date entry 'from-to' fields.

Add a command button to preview/print the report - using the following call :

DoCmd.OpenReport strReportName, acPreview, , strWhere

Where Report name is 'the name of the report' and the strWhere param - is the WHERE condition based upon the form data entered by the user in the date fields and the combo box selection for the department filter. Simple!


Let us know how you get on if this is of interest.

I would not use a Parameter Query as such - I prefer to write a standard SQL Query to return all relevant data - on the form put the two text boxes for the date entry 'from-to' fields.

Add a command button to preview/print the report - using the following call :

DoCmd.OpenReport strReportName, acPreview, , strWhere

Where Report name is 'the name of the report' and the strWhere param - is the WHERE condition based upon the form data entered by the user in the date fields and the combo box selection for the department filter. Simple!


Let us know how you get on if this is of interest.

Thanks for your help, I have another question, what exactly will the SQL Query look like, the coding I mean. And is the command button on the report I have already built or on the form??

Hi,

I have created a Form in Access. I have used a parameter query so when the user runs a report for a specific quarter, they can enter the dates they want. The problem is that none of the records are showing up when the date parameters are set.

Another issue I am having with reports, is that on the same form, I have departments set up as a combo box. I want to be able to filter the various departments on the reports. I know I need to set up queries for this, but how exactly would I do this based on a combo box?

Thanks to all who respond.

Assuming you already have the form created.

You will have 2 date fields:
From [txtfromdatefield] To [txttodatefield]

in your query, enter the following in the datefield criteria:
Between [Forms]![frmYourFormName]![txtfromDatefield] And [Forms]![frmYourFormName]![txtToDateField]


Then another one for [department]
let's say your field name is cbodepartment

In your query, create a new field and insert this:

[Department]=[Forms]![frmYourFormName]![cbodepartment] Or [Forms]![frmYourFormName]![cbodepartment] Is Null

Then in your form, create a new button to either print or preview report using the button wizard.

You should be all set.

Thanks for your help, I have another question, what exactly will the SQL Query look like, the coding I mean. And is the command button on the report I have already built or on the form??

If you attach a copy of your DB - I will have a look for you.

If you attach a copy of your DB - I will have a look for you.

Hi you can find the database


I also wanted to know how to make it so users can only select one check box and then there is the date issue about the report.

Thanks,

Jess

Jess

I have had a quick look at your database and (respectfully) it is evident that you are a novice DB programmer(?) The design of your database shows that you have not normalised your data. Whilst this you may not feel this important - it will be important if this product is to stand the test of time / and be managed/administrated in the longer term.

Ultimately, until you have made these important design changes to the structure of your tables etc. I would not suggest proceeding further.

These are many postings on Normalising of Data and Database design on this site to help you.

Good luck!

Jess

I have had a quick look at your database and (respectfully) it is evident that you are a novice DB programmer(?) The design of your database shows that you have not normalised your data. Whilst this you may not feel this important - it will be important if this product is to stand the test of time / and be managed/administrated in the longer term.

Ultimately, until you have made these important design changes to the structure of your tables etc. I would not suggest proceeding further.

These are many postings on Normalising of Data and Database design on this site to help you.

Good luck!
Hi,

Thanks for your help, the database is only meant for the office to produce reports on information from the past 3 years. They have to enter in the information on the database, the database isnt meant to be used for current data, because they have a program for that. I was trying to build something they could use to easily obtain past information.

All I wanted was for them to be able to sort the dates on certain reports thats all. I am no DB programmer, my area of expertise is the server world and Microsoft Exchange!