Date Ranges

[nancybrown]

I have a visitor table with start date and end date fields. A "DAILY" report is generated by entering the new date in a parameter box, e.g., tomorrow's report would have 12/2/05 entered into the parameter box. All visitors for that day appear on the report.

Currently if we have a visitor that is here for 10 days, we have to manually enter each 10 days worth of dates in order for the dates to appear on the e DAILY report. Is there an easier way? I tried using an ActivX Calendar, but it doesn't add a new record to the table with each subsequent date, it only increases the start date by one.

Thanks in advance.

 

[Ian Mac]

HI,

Have StartDate and EndDate Fields,

Query would be:

SELECT YourInfo FROM YourTable WHERE StartDate <= DateYouAskFor AND EndDate >= DateYouAskFor;

With this type of problem it's always best to have both the Start and End dates in your table.

If a vistor had a start date but no end date you can use:

SELECT YourInfo FROM YourTable WHERE StartDate <= DateYouAskFor AND (EndDate >= DateYouAskFor OR EndDate IS NULL);

 

[nancybrown]

Hi Ian,

I do have fields for start date and end dates in my table. I have a data entry form that is linked to this table. In that form I need to enter these start and end dates, how do I connect the query you suggested to the start date and end date fields on the form. Sorry . . . new to Access.

 

[Ian Mac]

OO, now you've asked

I'll put something proper together later, I'm just going to work but want to let you know I will answer you.

There are a lot of ways to do this.

Firstly, are you going to be displaying the results on the same Form or are you simple using the Form for the Criteria and displaying the results elsewhere.

Another thing you might try is looking at the 'Solutions' Database which gets bundled with MS Office.
Search your C: Drive for Solutions.mdb.
This has a wealth of tables, queries, forms you can dissect, open it holding shiftdown for the juicy stuff.

Regards,