help with dates

[beginner]

hello,

ive created an access database and i want to retrieve records between a
start [which will be entered manually] and 90days.

 

[Douglas J. Steele]

Create a query that returns all of the fields you want.

In the criteria cell under the date field in query, put

Between [Start Date (mm/dd/yyyy)] And DateAdd("d", 90, [Start Date
(mm/dd/yyyy)])

Make sure [Start Date (mm/dd/yyyy)] is typed identically in both cases
(copy-and-paste is a good idea)

Note that even if your regional settings are set to dd/mm/yyyy, you must use
mm/dd/yyyy format (or an unambiguous format such as yyyy-mm-dd or dd mmm
yyyy)

 

[KenSheridan via AccessMonster.com]

Doug:

Formatting the value entered is not necessary with a parameter in my
experience, only with date literals, and even then only in SQL view as in
design view Access correctly interprets a date in dd/mm/yyyy format if the
system date is that format (it will automatically convert it if you switch to
SQL view). So entering #12/07/2008# as a criterion in design view will
return rows on 12 July, not 7 December on systems using a dd/mm/yyyy short
date format. Its prudent to declare parameters of date/time data type,
however, to avoid the value entered being misinterpreted as an arithmetical
expression:

PARAMETERS [Start date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate BETWEEN [Start Date:]
AND DATEADD("d", 90, [Start Date:];

or unless the MyDate column is barred by a validation rule from accepting
date/time values with a non-zero time-of-day:

PARAMETERS [Start date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate >= [Start Date:] AND
MyDate < DATEADD("d", 91, [Start Date:];

to catch any rows with a value on the last day of the range with a non-zero
time-of-day.

Ken Sheridan
Stafford, England

Create a query that returns all of the fields you want.

In the criteria cell under the date field in query, put

Between [Start Date (mm/dd/yyyy)] And DateAdd("d", 90, [Start Date
(mm/dd/yyyy)])

Make sure [Start Date (mm/dd/yyyy)] is typed identically in both cases
(copy-and-paste is a good idea)

Note that even if your regional settings are set to dd/mm/yyyy, you must use
mm/dd/yyyy format (or an unambiguous format such as yyyy-mm-dd or dd mmm
yyyy)

hello,

ive created an access database and i want to retrieve records between a
start [which will be entered manually] and 90days.

 

[KenSheridan via AccessMonster.com]

Oops! Missing closing parentheses:

AND DATEADD("d", 90, [Start Date:]);
and:
MyDate < DATEADD("d", 91, [Start Date:]);

Ken Sheridan
Stafford, England

Doug:

Formatting the value entered is not necessary with a parameter in my
experience, only with date literals, and even then only in SQL view as in
design view Access correctly interprets a date in dd/mm/yyyy format if the
system date is that format (it will automatically convert it if you switch to
SQL view). So entering #12/07/2008# as a criterion in design view will
return rows on 12 July, not 7 December on systems using a dd/mm/yyyy short
date format. Its prudent to declare parameters of date/time data type,
however, to avoid the value entered being misinterpreted as an arithmetical
expression:

PARAMETERS [Start date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate BETWEEN [Start Date:]
AND DATEADD("d", 90, [Start Date:];

or unless the MyDate column is barred by a validation rule from accepting
date/time values with a non-zero time-of-day:

PARAMETERS [Start date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate >= [Start Date:] AND
MyDate < DATEADD("d", 91, [Start Date:];

to catch any rows with a value on the last day of the range with a non-zero
time-of-day.

Ken Sheridan
Stafford, England

Create a query that returns all of the fields you want.

[quoted text clipped - 14 lines]

ive created an access database and i want to retrieve records between a
start [which will be entered manually] and 90days.

 

[beginner]

hello Doug and Ken,

Thanks a million for your help, it works.

ps: ive been battling for weeks now.

Create a query that returns all of the fields you want.

In the criteria cell under the date field in query, put

Between [Start Date (mm/dd/yyyy)] And DateAdd("d", 90, [Start Date
(mm/dd/yyyy)])

Make sure [Start Date (mm/dd/yyyy)] is typed identically in both cases
(copy-and-paste is a good idea)

Note that even if your regional settings are set to dd/mm/yyyy, you must use
mm/dd/yyyy format (or an unambiguous format such as yyyy-mm-dd or dd mmm
yyyy)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

hello,

ive created an access database and i want to retrieve records between a
start [which will be entered manually] and 90days.