Run a report based on criteria selected on a form

[cantona]

HELP!!
I have produced a report where i need to limit the data displayed using parameters. Rather than use a query where the user has to type in values to a parameter pop up box, i want the user to select values from a combo box on a blank form, for example month and year. i then want my report to only display results for the month and year that the user has selected.

At the moment i have created a query to select the required information for the report. For the month and year field i have entered the following criteria;

Forms![ArchiveSelect]![ArchiveMonth]
Forms![ArchiveSelect]![ArchiveYear]

When the report is run, the report pulls in the correct information ignoring any records from months and years not selected, however in the report footer i have some summary calculations. I need to count the number of records, based on particular criteria. The formula seems to count every record genereated by the query, ignoring the month and year parameters. How do i get the formula to count records only where the month and year match what the user originally entered on the form?

The formula i have been using is;

=DCount("[Child_ID]","Archive Query","[Twin_track] = Yes&quot

This formula as it stands is counting too many records, i want it to only count records where the Month & Year in the query = the month & Year entered on the form by the user.

Can anyone offer any suggestions???

 

[essnrv]

1.Create a Query Say Query 1
In Query 1:
Select * from Table1 where month=Forms![ArchiveSelect]![ArchiveMonth] And Year=Forms![ArchiveSelect]![ArchiveYear]
2.Set Query1 As Recordsource for the report.
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[randysmid]

Hi,
Yes, you can simply add the additional criteria to your "DCount" statement. Here are a few examples, but pay particular attention to the single quote marks versus the double quote marks.

MONTH AND YEAR AS STRING EXAMPLE:
=DCount("[Child_ID]","Archive Query","[Twin_track] = Yes" & "[fldMonth] = '" & [Forms![ArchiveSelect]![ArchiveMonth] & "' & " & "[fldYear] = '" & Forms![ArchiveSelect]![ArchiveYear) & "'"

All of this presumes that the month and year are strings. If they are numeric, then they do not need the single quote marks, as such:

MONTH AND YEAR AS NUMERIC EXAMPLE:
=DCount("[Child_ID]","Archive Query","[Twin_track] = Yes" & "[fldMonth] = " & [Forms![ArchiveSelect]![ArchiveMonth] & " & " & "[fldYear] = " & Forms![ArchiveSelect]![ArchiveYear))

If this tip has been helpful, be sure to click on the "Mark this post as a helpful/expert tip!".

Randy Smith
California Teachers Association