MS Access - return list of dates with no record

Posted on 2009-05-16
Medium Priority
Last Modified: 2013-11-27
I have a SQL Server database with an Access front end.  The database is of event bookings.  I would like to design a query that lists Saturday and Sunday dates with no events booked.

The end object is to publish it to the web so I can create last minute deals.
Question by:les28
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24404300
what you need is a calendar table, with (all) the dates.
once you have that (and believe me, that table WILL serve later also), you can fetch, from there, easily all the dates that are a saturday/sunday:
datepart(weekday, your_date_field) in (6,7)
the values 6 and 7 are if the DATE_FIRST setting is set to monday.

once you have that query, you just left join to your table.
"just" fill in your table and column names as fit:
SELECT cd.date_value
  FROM your_calendar_table cd
  LEFT JOIN your_boooking_table b
    ON b.booking_date = cd.date_value
 WHERE datepart(weekday, cd.date_value) in (6,7)
   AND b.booking_date IS NULL  ---- < this is the part that will make the query to return the dates with no bookings

Open in new window


Author Closing Comment

ID: 31582284
Thanks very much.  I'll give it a try

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question