Criteria in Query Problem!

[liverpoolrdbest]

Hi,
I have a query but don't know what criteria to type in. I would like to return all fields with a date during the month and year selected. I have made a 'Date selector' form, with a month and year drop down menu. Then when you clik OK I would like the results of the query (for the chosen month and year) to come up.

I hope you can understand this, if not tell me and I will try to rewrite a bit better!.
Many thanks,
Grant

[pgirl]

Here's one way you could do it.

1. Create a query - let's say you call it qryEvents.
Add all the fields you want to display.

Let's say the date for the "event" is stored in event_date field. Add another two fields - Call one
Expr1: Month([event_date])
and the other
Expr2: Year([event_date])

This will extract the month and date for the event.

The criteria for month will be the value in the selected month in the form you've created:
[Forms]![frmEvents]![cboMonth]

The criteria for year will be the value in the selected year
[Forms]![frmEvents]![cboYear]

2. Create a report (rptEvents)
Add all the fields you want to display in your report. (you could also display them in the form if you want - the theory is the same).

Change the control source for the report to the query you created. (qryEvents)

3. Add a button to the form you created. If you use the button wizard you can choose REPORT OPERATIONS and choose rptEvents (or whatever your report is called).

Now when you open the form and choose a month and year you can click the button. This will launch the report and call on the query you created. It will read the month and date from the form and filter the query to display all the events in the report.

If you want to show all the records in the form it is almost identical. Let me know if you want to do this.

[liverpoolrdbest]

Thanks for the help, but when I press run I get an error saying, "Duplicate output alias 'Expr1'".
Any ideas why this could be?
Many thanks

[pgirl]

You may have another Expr1 - it's just a named expression. You can go into design view in the query and call it anything you like. Try renaming it.

[jimatqsi]

You might find it helpful to use the Format function to return a result to test your criteria against. Add a field to your query something like this:

 MMMYY: Format([datefieldname],"MMMYY")

This will return a value of "JUN06" for any date during the month of June 2006. So you could actually put "JUN06" for the criteria, but hopefully you will generalize it by putting

Format([someotherdatefield],"MMMYY")

in the criteria. The "someotherdatefield" can be a parameter from a table or maybe from a text box on a form. If you make a criteria like this:

Format (date(),"MMMYY")

then you will get only stuff for the current month.

Check out the Format function. Lots of cool stuff you can do with it.

Jim