473,322 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Need Help working with dates in MS ACCESS

In a table called Reservation I have an arrival date field and a departure date filed for reservations in several different vacation homes. To take new reservations I need to figure out a query that will return only the homes without days reserved between two given dates.
Jul 19 '07 #1
4 1375
RoseM
13
In a table called Reservation I have an arrival date field and a departure date filed for reservations in several different vacation homes. To take new reservations I need to figure out a query that will return only the homes without days reserved between two given dates.
If you're doing it through code, try something like this:

Expand|Select|Wrap|Line Numbers
  1.  SELECT tDetail.MA, tDetail.StartDate
  2. FROM tDetail
  3. WHERE (((tDetail.StartDate) Between #1/1/2001# And #1/1/2007#));
  4.  
If you're in query designer, just type "between #1/1/2001# and #1/1/2007# (depending on what your dates are, obviously)
Jul 19 '07 #2
hyperpau
184 Expert 100+
In a table called Reservation I have an arrival date field and a departure date filed for reservations in several different vacation homes. To take new reservations I need to figure out a query that will return only the homes without days reserved between two given dates.
on the date field of your query, enter the criteria "Is Null"
or if you have another field that says whether the house is "Reserved" or "Vacant", then put the criteria "Vacant" on that field's criteria of the query.
Jul 19 '07 #3
If you're doing it through code, try something like this:

Expand|Select|Wrap|Line Numbers
  1.  SELECT tDetail.MA, tDetail.StartDate
  2. FROM tDetail
  3. WHERE (((tDetail.StartDate) Between #1/1/2001# And #1/1/2007#));
  4.  
If you're in query designer, just type "between #1/1/2001# and #1/1/2007# (depending on what your dates are, obviously)
Thanks for the reply - if I understood well, and I apologize if I didn't, this query would return the vacation homes where the startdate is between the requested dates for example "between #1/1/2001# and #1/1/2007# . Right? The problem here is that the startdate can begin before the requested dates. For example: PropertyID 23 is booked form 7/25/2007 to 8/12/2007, and I receive reservation request for the same Property from 7/10/2007 to 7/30/2007 how can I show that property and all properties where the dates overlaps as "Not Available"?
Thanks in advance.
Eddie
Jul 19 '07 #4
RoseM
13
Sorry, I've got nothing....& nothing has worked for me at all today, so I won't mess you up further, you'll wind up with snakes coming out of your tables.
Jul 19 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

20
by: andy.rich | last post by:
I am getting the following error and I do not know why. Can anyone help? -------------------------------------------------------- this is what appears on the screen...
9
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then...
18
by: PC Datasheet | last post by:
An Access user saw my name in a newsgroup and sent me a request for help on a project. As part of the project, a list of the dates in a month was needed. For anyone needing a list of dates in a...
12
by: Dixie | last post by:
I am trying to calculate the number of workdays between two dates with regards to holidays as well. I have used Arvin Meyer's code on the Access Web, but as I am in Australia and my date format is...
9
by: Blarneystone | last post by:
Hi, I am using VB.NET and trying to pull data from two different tables in the database. I am using what I think is standard code. But the data I am pulling is like the following: Table1...
2
by: Bill | last post by:
I have a 200 record database that includes a date/time field, AnnivDate, for a wedding anniversary. AnnivDate has nulls and some incorrect year data. I have been creating the Access database...
12
by: jonathan184 | last post by:
Hi the purpose of the script I am trying to do is to access a dir and run the ls -l command which will show the files and the modified date, no w the script is to check two files in that dir , if the...
2
klarae99
by: klarae99 | last post by:
Hello, I am working in Access 2003 on an inventory database. Right now I am working on a report which will be used to fill in forms that our state requires periodically to collect taxes. Right...
8
by: mvdave | last post by:
Hello all & I hope I'm posting in the right place.. I need to load a temporary table with a range of sequential dates, passing it a beginning and an end date from a criteria form. I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.