Between Dates returning a value of Day

[Guest]

Question



I have a database for travel expenses. One of my tables is tblTrips. I have
created one field in the form for TripStartDate (using the MS Date & Time
Picker Control Version 6.0) and another field for TripEndDate (using the MS
Date & Time Picker Control Version 6.0). I need to calculate the expenses,
i.e., breakfast, lunch, dinner, cab, etc. for EACH day. I have a subform
with all the expenses for each day, but I need to generate a record for each
day in between the dates specified as TripStartDate and TripEndDate
automatically. Each trip will have a different amount of days. For example,
if the TripStartDate was 01/01/05 and the TripEndDate was 01/15/05, I need to
calculate the number of days between the days and then return a record, which
includes the daily expenses, for Jan. 1, Jan. 2, Jan. 3, etc. for example.

Thanks for any help.
--
S


Hi Sharon,

In Access Help find "Between"

HTH
--
-Larry-
--



I did look in the Help field under dates and between. I see how you can
choose how many "Sundays" occur in the time period of my between dates.
However, I want the values to List ALL days of my between dates. For
instance. If my start dates is May 5, 2005 and my end date is May 15, 2005,
the value that I want returned is:

Thursday 5
Friday 6
Saturday 7
Sunday 8
Monday 9
Tuesday 10
Wednesday 11
Thursday 12
Friday 13
Saturday 14
Sunday 15

Am I missing this somewhere? I have also looked under the MVP websites and
everywhere else I can think of. If you could be just a little bit more
specific I would appreciate it. Thanks.

--
S


:

Click to show or hide original message or reply text.

 

[John Vinson]

Question



I have a database for travel expenses. One of my tables is tblTrips. I have
created one field in the form for TripStartDate (using the MS Date & Time
Picker Control Version 6.0) and another field for TripEndDate (using the MS
Date & Time Picker Control Version 6.0). I need to calculate the expenses,
i.e., breakfast, lunch, dinner, cab, etc. for EACH day. I have a subform
with all the expenses for each day, but I need to generate a record for each
day in between the dates specified as TripStartDate and TripEndDate
automatically. Each trip will have a different amount of days. For example,
if the TripStartDate was 01/01/05 and the TripEndDate was 01/15/05, I need to
calculate the number of days between the days and then return a record, which
includes the daily expenses, for Jan. 1, Jan. 2, Jan. 3, etc. for example.

It would help here to have a little auxiliary table. I routinely have
a table named Num, with one integer field N, filled with values from 0
to 10000 or so. You can use Excel to "fill down" and Import the
spreadsheet into a new Table to quickly create this.

Create a Query with your tblTrips and Num, with NO join line. Put a
criterion on N of

<= DateDiff("d", [TripStartDate], [TripEndDate])

and include a calculated field

EachDay: DateAdd("d", [N], [TripStartDate])

John W. Vinson[MVP]

 

[Guest]

John,

I have copied and pasted the documenter page for this query as you outlined
below. Can you tell where I have gone wrong? I created an Excel spreadsheet
with one column with fields from 0-10005 as an integer. I then imported this
into Access as a table named NUM. I then ran a query with all my trip
information and with the field N having the criteria <=DateDiff("d",
[TripBeginDate], [TripEndDate]. I then created a calculated field
EachDay:AddDate("d", [N], [TripBeginDate]). I don't get any values returned
at all. I appreciate your help.

SQL
SELECT TripDetailstbl.TripBeginDate, NUM.N, DateAdd("d",[N],TripBeginDate])
AS EachDay FROM TripDetailstbl, NUM WHERE
(((NUM.N)<=DateDiff("d",[TripBeginDate],[TripEndDate])));



Name Type Size
TripBeginDate Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 3252
DataUpdatable: False
Format: Short Date
GUID: {guid {76E85E69-88E8-444E-B45D-91855FD4539C}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 0
Required: False
SourceField: TripBeginDate
SourceTable: TripDetailstbl
N Integer 2
AllowZeroLength: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: {guid {99374A5A-E779-45FE-B0E0-F5CE8D698C8E}}
OrdinalPosition: 1
Required: False
SourceField: N
SourceTable: NUM

F:\EXPENSES_2005-05-31.mdb Wednesday, June 01, 2005
Query: TripDetailstbl Query Page: 2
EachDay Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
CollatingOrder: General
DataUpdatable: False
OrdinalPosition: 2
Required: False

--
S

Question



I have a database for travel expenses. One of my tables is tblTrips. I have
created one field in the form for TripStartDate (using the MS Date & Time
Picker Control Version 6.0) and another field for TripEndDate (using the MS
Date & Time Picker Control Version 6.0). I need to calculate the expenses,
i.e., breakfast, lunch, dinner, cab, etc. for EACH day. I have a subform
with all the expenses for each day, but I need to generate a record for each
day in between the dates specified as TripStartDate and TripEndDate
automatically. Each trip will have a different amount of days. For example,
if the TripStartDate was 01/01/05 and the TripEndDate was 01/15/05, I need to
calculate the number of days between the days and then return a record, which
includes the daily expenses, for Jan. 1, Jan. 2, Jan. 3, etc. for example.

It would help here to have a little auxiliary table. I routinely have
a table named Num, with one integer field N, filled with values from 0
to 10000 or so. You can use Excel to "fill down" and Import the
spreadsheet into a new Table to quickly create this.

Create a Query with your tblTrips and Num, with NO join line. Put a
criterion on N of

<= DateDiff("d", [TripStartDate], [TripEndDate])

and include a calculated field

EachDay: DateAdd("d", [N], [TripStartDate])

John W. Vinson[MVP]

 

[John Vinson]

SQL
SELECT TripDetailstbl.TripBeginDate, NUM.N, DateAdd("d",[N],TripBeginDate])
AS EachDay FROM TripDetailstbl, NUM WHERE
(((NUM.N)<=DateDiff("d",[TripBeginDate],[TripEndDate])));

You're missing a square bracket before TripBeginDate in the DateAdd
expression. Try:

SELECT TripDetailstbl.TripBeginDate,
NUM.N,
DateAdd("d",[N],[TripBeginDate]) AS EachDay
FROM TripDetailstbl, NUM
WHERE (((NUM.N)<=DateDiff("d",[TripBeginDate],[TripEndDate])));



John W. Vinson[MVP]