Date ranges (2000(sr3))

Topic

New Reply

I have a report that needs to filter on a date field (or two) in order to choose active clients. There is a Start Date and an End Date and they are separated by 6 months. So if the start date is Jan 15 the end date is July 15. So if I am printing the report for January, the client is active and if I am printing for August, they are not. I cannot, for the life of me, wrap my head around the math so that I get the correct output.

Reply | Quote

Replies

Are Start Date and End Date both fields in the table? Or is Start Date a field in the table and should End Date be calculated from it? Or something else?

Reply | Quote

Currently they are both in the table. End is calculated in an afterupdate event from Start and again from Length if they change it from the default 6 months. I suppose there is no good reason to store end in the table now that you mention it and it certainly isn’t too late to change it if there’s a strong reason to do so.

Reply | Quote

If it is a calculated value, you shouldn’t store it in the table – you should calculate it on the fly in a query.

Reply | Quote

To get back to your original question, where does the date for the report come in? Is it always the current date, or is the user prompted for it, or can the user enter the date on a form, or …?

Reply | Quote

The user will ultimately enter it via a form for choosing reports to print and there will be a reference to that field in the query. Currently I am using a prompt as I haven’t decided on form design yet. Feel free to give me a formula for either. I can adapt it from there.
I take your point in your other post. Recording the end date seemed like a good idea at the time…

Reply | Quote

Enter your parameter prompt in the Field row in an empty column in the query, e.g. [Enter Date]
Access will change it to something like Expr1: [Enter Date] but you don’t have to worry about that.
Clear the Show check box for the column.
Enter an expression like this in the Criteria line for this column:

Between [Start Date] And DateAdd(“m”,[Length],[Start Date])

where Start Date is the name of the start date field and Length is the name of the duration field, assumed to be expressed in months.
If you use a form, replace the parameter [Enter Date] with a reference to the start date text box, e.g. [Forms]![frmParam]![txtStartDate]

Reply | Quote