Calendar Query Changes Problem!!!!

[Richard C]

I have an events calendar, which works relatively well. It
is made up of a Table, which stores all the events
details. It stores the Start Date, End Date (Date form
DD/MM/YYYY) and other details which are txt boxes. Then
there is a Query that gets its information from the same
events table, which is linked to a form, which has an
activeX calendar. Then when the user clicks on a specific
day on that calendar, the query runs and if any matches
are found it will display that event to the user!!!

The Main problem is, it will display the start and end of
the events. However if you have a multi day event and you
click a middle day, it will not recognize this. This means
events could be missed off and things could be double
booked!!!

I have been looking at some date manipulators, one of them
being DateDiff and
I would like to incorporate this into my query, but I
don't know how to do it!!!
As I mentioned before, the calendar runs from a query that
is linked to Activex Calendar. In the query under
DateStart & DateEnd, the Criteria is set to;

[Forms]![Events_Table]![txt2Date]

This links the query to my Form called Events_Table, the
start and end dates are compared to txt2Date, which is the
link with the activex Calendar!!! So obviously when the
calendar is clicked on a specific day, that day is checked
through the query and if a match is found, then the event
with the matching start or end dates are displayed simply
enough.

I need to modify theses expressions to take into account
the days between the start and end dates. This is where I
thought dateDiff my come in useful, it could check the
difference between the dates and then do a count when ever
the calendar is clicked!!!

Does anyone know how to do this? I have had a play with
altering the expression, but haven't got anything close
yet!!

Any Help would be great!!!

Rich

 

[Allen Browne]

You want yourr query to return every entry where the date in the control on
your form falls between the Start Date and End Date.

In the Field row of your query, enter:
[Forms]![Events_Table]![txt2Date] Between [Start Date] And [End Date]
In the Criteria row under this calculated field, enter:
True

As a side issue, since you are using a non-American date format, you may
need to take steps to ensure Access understands it correctly. See:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html