Date Calculations

[Paul Richards]

I need to retrieve data where SOMEDATE = TODAY'S DATE + 1 week.

What's the correct way to form the expression?

Thanks

 

[Tom Lake]

I need to retrieve data where SOMEDATE = TODAY'S DATE + 1 week.

What's the correct way to form the expression?

SOMEDATE = Date() + 7

Tom Lake

 

[Douglas J. Steele]

SOMEDATE = Date() + 7

Or DateAdd("w", 1, Date())

 

[Paul Richards]

Doug: thanks

Or DateAdd("w", 1, Date())

 

[Paul Richards]

Tom: thanks

SOMEDATE = Date() + 7

Tom Lake

 

[Guest]

Paul,

I'm assuming you want to use a query to retrieve your data, if so use the
following line in the date criteria for your query.

=Now()+7

This will return all records where the date is exactly seven days from the
current date, alternatively you can use the usual mathematical <>= symbols to
get the date before and after a certain date.

Alternatively if the date is variable, you could ask your users for the date
required, use:

[Enter Date]

in the date criteria for your query, this will display a small dialog box
and the query will halt until your users have entered the required date.

HTH

Neil
www.nwarwick.co.uk

 

[Douglas J. Steele]

Actually, Neil, it's unlikely that using =Now()+7 as a criteria will return
any records.

Now contains both date and time. If you've got =Now() + 7 as a criteria, it
will only return records that have the exact same time associated with them
for a date one week from today.

If the stored records have only dates in them, then = Date() + 7 would
return those records with a date of one week from today.

If the stored records have both dates and times in them, then Between Date()
+ 7 And Date() + 8 will return those records with a date of one week from
today.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Paul,

I'm assuming you want to use a query to retrieve your data, if so use the
following line in the date criteria for your query.

=Now()+7

This will return all records where the date is exactly seven days from the
current date, alternatively you can use the usual mathematical <>= symbols
to
get the date before and after a certain date.

Alternatively if the date is variable, you could ask your users for the
date
required, use:

[Enter Date]

in the date criteria for your query, this will display a small dialog box
and the query will halt until your users have entered the required date.

HTH

Neil
www.nwarwick.co.uk

I need to retrieve data where SOMEDATE = TODAY'S DATE + 1 week.

What's the correct way to form the expression?

Thanks

 

[Paul Richards]

Douglas - what if I wanted to return dates over a period of time - say
records where SOMEDATE is between 7 and 14 days from TODAY?

Thanks

 

[Douglas J. Steele]

Use

BETWEEN Date() + 7 AND Date() + 14

or

BETWEEN DateAdd("d", 7, Date()) AND DateAdd("d", 14, Date())

 

[Paul Richards]

Doug: many thanks.