DateDiff

[Sietze de Jong]

Hi,

I have a problem with my DateDiff function. I am wondering if it is possible
to calculate the amount of days between two different dates in the same
column of a table. It is for a reservation system that automatically
generates a bill and keeps a detailed agenda. In the reservations table I
have the following data:

Reservation ID (PK)
Dog ID
Cabin ID
Dog Name
Combine

Then I have a reservationbs extended table with the following data:

ID (PK)
Reservation ID
Date
Arrival/Departure (Drop down)

That means that for every reservation I have two rows in my reservation
details table where the dates are in the same column. The column
Arrival/Departure makes it able to separate the two dates for other queries.
But now I would like to make a DateDiff between the two dates in the same
column. I tried everything with queries but I just can't figure it out. Can
someone please help me out with this?

 

[John W. Vinson]

On Mon, 19 Apr 2010 13:48:02 -0700, Sietze de Jong <Sietze de

Hi,

I have a problem with my DateDiff function. I am wondering if it is possible
to calculate the amount of days between two different dates in the same
column of a table. It is for a reservation system that automatically
generates a bill and keeps a detailed agenda. In the reservations table I
have the following data:

Reservation ID (PK)
Dog ID
Cabin ID
Dog Name
Combine

Then I have a reservationbs extended table with the following data:

ID (PK)
Reservation ID
Date
Arrival/Departure (Drop down)

That means that for every reservation I have two rows in my reservation
details table where the dates are in the same column. The column
Arrival/Departure makes it able to separate the two dates for other queries.
But now I would like to make a DateDiff between the two dates in the same
column. I tried everything with queries but I just can't figure it out. Can
someone please help me out with this?

You can use a query, adding Reservations, and then adding ReservationBs
*twice*, joining both instances by ReservationID. Put a criterion on the first
instance to select Arrival, and on the second instance to select Departure.
Alias the tablenames for convenience; the SQL might be

SELECT Reservations.[Dog ID], <other fields>, Arrive.[Date], Depart.[Date],
DateDiff("d", [Arrive].[Date], [Depart].[Date]) AS DaysStay
FROM (Reservations INNER JOIN ReservationsBs AS Arrive
ON Reservations.[Reservation ID] = Arrive.[Reservation ID])
INNER JOIN ReservationBs AS Depart
ON Reservations.[Reservation ID] = Depart.[Reservation ID]
WHERE Arrive.[Arrival/Departure] = "Arrival"
AND Depart.[Arrival/Departure] = "Departure";


Some suggestions: Don't use the reserved word DATE as a fieldname, Access may
confuse it with the builtin Date() function; and I'd avoid using blanks or
punctuation such as / in fieldnames. If you do either you *must* always use
[brackets] around the fieldnames.

 

[ryguy7272]

Same column, huh? That's tricky. I think you have to modify your query a
bit. . .

This may give you some ideas:
http://forums.databasejournal.com/showthread.php?t=40845

Can you add another query? I've hit a wall many times, just because I tried
to do too many things in a single query. Sometimes you just have to break
these things into 2, or 3, or more parts, then reassemble in some kind of
summary-query.

 

[tbs]

try writing a query to list only arrivals and another query that list
departures. after that, write another query to link these 2 queries by
reservation ID. you should be able to calculate the datediff from there.

 

[John W. Vinson]

On Mon, 19 Apr 2010 13:48:02 -0700, Sietze de Jong <Sietze de

Hi,

I have a problem with my DateDiff function. I am wondering if it is possible
to calculate the amount of days between two different dates in the same
column of a table. It is for a reservation system that automatically
generates a bill and keeps a detailed agenda. In the reservations table I
have the following data:

Reservation ID (PK)
Dog ID
Cabin ID
Dog Name
Combine

Then I have a reservationbs extended table with the following data:

ID (PK)
Reservation ID
Date
Arrival/Departure (Drop down)

That means that for every reservation I have two rows in my reservation
details table where the dates are in the same column. The column
Arrival/Departure makes it able to separate the two dates for other queries.
But now I would like to make a DateDiff between the two dates in the same
column. I tried everything with queries but I just can't figure it out. Can
someone please help me out with this?

Hrm. I could sweare I answered this earlier today, but don't see the post.
Trying again:

You can do this by joining the ReservationBs table to the Reservations table
*twice*. It helps to alias the tablename: the SQL would be

SELECT <whatever fields you want to see>, DateDiff("d", [Arr].[Date],
[Dep].[Date]) AS DaysStayed
FROM (Reservations INNER JOIN ReservationsBs AS Arr
ON Reservations.[Reservation ID] = Arr.[Reservation ID])
INNER JOIN ReservationsBs AS Dep
ON Reservations.[Reservation ID] = Dep.[Reservation ID]
WHERE Arr.[Arrival/Departure] = "Arrival"
AND Dep.[Arrival/Departure] = "Departure";

with the appropriate values for the criteria (I don't know the datatype or
content of [Arrival/Departure]).

I would suggest not using the reserved word Date as a fieldname, and perhaps
also avoiding blanks and punctuation such as / in fieldnames.