QUERY 1

[lmanuelab]

i want to use criteria expression to make date ranges. let's say i need only dates from Feb. 25 to March 5. what expression should i use

 

[THWatson]

In the query, in criteria for the date column enter...
Between #2/25/05# And #3/5/05#

However, every time you want to change the dates you will have to go in and change the date values in the query. So a better way would be to have prompts for the user to enter the date values. In the query, in the criteria for the date column, put...
Between [Enter Start Date] And [Enter End Date]

Tom

 

[dhookom]

To take Tom's reply one step further, use controls on forms to set your criteria. For additional information, check this page

http://www.fontstuff.com/access/acctut08.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

I agree entirely with Duane. Setting criteria through a form is the preferred method. I was only trying to take things one small step at a time, and once you understand how parameters work in query criteria it's easy to take it the next step to setting up a form based parameter query.

Tom

 

[lmanuelab]

now i need to do the same thing for my reports. is there any way of doing that.

 

[THWatson]

If your report is based on a query that contains parameter criteria, those prompts will occur automatically when you run the report.

Alternatively, if you want to make a form and feed the criteria from there, and also run the report from there, here's what to do...

1. Create an unbound form with 2 controls. Let's call the form frmDateSelector. Since you are working with Date fields, let's call them txtStartDate and txtEndDate.
2. In the Date column in your query, enter the following as criteria
Between Forms!frmDateSelector!txtStartDate And Forms!frmDateSelector!txtEndDate
3. Create your report using the query as the record source.
3. On your form frmDateSelector, use the wizard to create a command button to Preview the report.

Then when you enter the StartDate and EndDate values, and press the Preview button, it all happens without further prompting.

If you want, you can also put on a command button to Print the report.

Tom

 

[dhookom]

Great replies from Tom (THWatson). I am somewhat of a "form control criteria evangelist". Using parameter prompts is very limited and provides a very poor user interface.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ProgramError]

Dhookom

What a nice little web site. It's been added to my growing tips websites I've been collating. Thanks to all the knowledgable TEK_TIPS members.


Program Error
Programmers do it one finger at a time!