Trying to Query for a Calendar...

[mikebrewer]

Okay guys, apparently I've just got stupid but I can't seem to solve this one...

I'm trying to query a table that has an appointment start date and an appointment end date (ApptStart & ApptEnd, respectively).

I'm trying to write a query that would select all the records that would fall on a chosen date. So if a user selects today (4/14/2016), we would see any records where the appointments could come across that date. If the appointment start date is less than that date and the appointment end date is greater, i want to show it. if the end date falls on the date, i want to show it. if the appt start and end are on the same day, i want to show it. I keep writing myself in circles using multiple OR statements in my Query and I know it can't be as hard as I'm making it. I think I've just gone dense for the day.

Any suggestions?

:banghead::banghead:

 

[plog]

WHERE ApptStart<=[YourDateFieldHere] AND [ApptEnd]>=[YourDateFieldHere]

 

[mikebrewer]

The reason that doesn't work is for appointments that start and end on the same day. So using this:

SELECT table.*
FROM table
WHERE (((table.ApptStart)<=#4/14/2016#) AND ((table.ApptEnd)>=#4/14/2016#));

yields a bunch of records, except ones where say the apptstart = 4/14/2016 11:00 AM and apptend = 4/14/2016 12:00 PM

 

[plog]

Then you need to extract out just the Date value and exclude the time portion. I get my functions mixed up. You need wrap your fields in either the DateValue function (http://www.techonthenet.com/access/functions/date/datevalue.php) or CDate (http://www.techonthenet.com/access/functions/datatype/cdate.php).

 

[mikebrewer]

and I'm afraid of writing this:

SELECT table.*
FROM table
WHERE (((table.ApptStart)<=#4/14/2016#) AND ((table.ApptEnd)>=#4/14/2016#)) OR (((table.ApptStart)>=#4/14/2016#) AND ((table.ApptEnd)<=#4/15/2016#));

In this case, I don't think I'm going to get anything where the appointment starts today and goes until say tomorrow.

 

[mikebrewer]

ah, I'll give that a try. I'll get back to you!

 

[jdraw]

Mike,

You might get some ideas from this material.

Shows some options with sql.

 

[mikebrewer]

I think you were right! I have to do some more testing and have a phone conference to attend but so far it looks good!! Thank you for your help!