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?

On Mon, 19 Apr 2010 13:48:02 -0700, Sietze de Jong <Sietze de
(E-Mail Removed)> wrote:

>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.
--

John W. Vinson [MVP]

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/sh...ad.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.



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Sietze de Jong" wrote:

> 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?
>
>
>
>
>
>

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.

"Sietze de Jong" wrote:

> 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?
>
>
>
>
>
>

On Mon, 19 Apr 2010 13:48:02 -0700, Sietze de Jong <Sietze de
(E-Mail Removed)> wrote:

>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.
--

John W. Vinson [MVP]