Date function voodoo in my query

[chris23892 via AccessMonster.com]

I'm pulling my hair out...In my query, I want to use the following criteria
to filter:

"Sort all records in the past 7 days from a specific date". I can not find
the right expression to do this. The one I'm stuck one works great from
TODAYS date:

=DateADD("WW"),-1,Date())

This works great! But I want to be able to specify a date and have it pull
the last work week from that specific date.

the more I search on here, the more confused I get. I'm sure there are
several ways to do this. I'm just looking for an expression to put in my
criteria, not a full angry VB code snippet to run.

Any words of wisdom I can try here? Thanks for all the help

 

[chris23892 via AccessMonster.com]

So...I'm thinking...I like the fact that the date add function will let me
subtract a working week from today's date (defined by the Date () )

How do I plop in a SPECIFIC date in place of the the Date () ?

Nothing seems to work right now....

I tried (I'll use 10/20/08 as an example

=Date Add("ww",-1,#10/20/2008#) with no results.

Basically, reading this expression:
ADD a negative Work week (7 days, negative denoted by the -1) to the date
10/20/08

Maddness, I tell you....This function works great with the date () for todays
date.....

 

[Dale Fye]

So, let me get this right.

You have a field [Todays date] in a table, and you want to query that table
and only return those records where [Todays date] (this is probably not a
good name for a field) is greater than or equal to a week prior to some date
that you enter when the query runs.

How about creating a parameter query:

PARAMETERS [Enter date] DateTime;
SELECT * FROM yourTable
WHERE [Your date] >= Dateadd("ww", -1, [Enter date])

If the field in your table is not named [Your date], change the above
accordingly.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[raskew via AccessMonster.com]

Here's an example you can play with. Copy/paste to a new query,
substituting table/field names as necessary. When prompted,
enter mm/dd/yyyy (e.g. 12/14/2008)

SELECT
tblDevProcess.startTime
FROM
tblDevProcess
WHERE
(((tblDevProcess.startTime)>DateAdd("d",7,[enter mm/dd/yyyy])));

HTH - Bob

So...I'm thinking...I like the fact that the date add function will let me
subtract a working week from today's date (defined by the Date () )

How do I plop in a SPECIFIC date in place of the the Date () ?

Nothing seems to work right now....

I tried (I'll use 10/20/08 as an example

=Date Add("ww",-1,#10/20/2008#) with no results.

Basically, reading this expression:
ADD a negative Work week (7 days, negative denoted by the -1) to the date
10/20/08

Maddness, I tell you....This function works great with the date () for todays
date.....

I'm pulling my hair out...In my query, I want to use the following criteria
to filter:

[quoted text clipped - 12 lines]

Any words of wisdom I can try here? Thanks for all the help

 

[Ron2006]

If you want to pull records for some other date than today's date then
you will have to put a date range in the selection criteria.


Let's just make it simple.

Today is 1/7/9
You want to select all records for the week of 12/14/2008.
change you criteria to

between [Enter Sunday of the week desired:] and [Enter Sunday of the
week desired:] + 7

or you could say

between [Enter Saturday of the week desired:] and [Enter Saturday of
the week desired:] - 7


You have to be clear which way to go.

or it can be

PARAMETERS [Enter date] DateTime;
SELECT * FROM yourTable
WHERE datepart("ww",[Your table date name]) = datepart("ww",[Enter the
date of any day in the week desired])

However, I am NOT sure that the above logic (using "ww") WILL work if
you want to pick all of the dates in the week of 12/28/2008 thru
01/03/2009 since I believe that the week for the first four days will
be 52 and the week for the last three will be 01.

Ron

 

[chris23892 via AccessMonster.com]

The specific date if from me. Basically, I have an excel sheet that is linked
to this and other queries. I connect to the query through data import in
excel.

I use the dateAdd function to run a report weekly. pretty slick, it just
takes the CURRENT date and pulls all records in the last seven days. I want
to use this same logic to pull historical data (like the same time perior
last year or from two months ago). I'll just type it in the expression to
pull the data, no need for a user prompt since this is all behind the scenes
due to the data being pulled into excel automatically from this query.


I'm thinking adding your AND statment may be the trick. It is indeed pull all
records from the specified date. I'll check it out and get back with the
results. thanks for all the help, I really appreciate it!

So...I'm thinking...I like the fact that the date add function will let me
subtract a working week from today's date (defined by the Date () )

[quoted text clipped - 11 lines]

Maddness, I tell you....This function works great with the date () for todays
date.....

Your dateadd looks correct, except that it will get all records since October
13, 2008 - including records from last week. If you want just records during
that week you'll need to specify that too, e.g.

= DateAdd("ww", -1, #10/20/2008#) AND <= #10/20/2008#

Where is the specific date coming from? A user prompt? a date entered in a
textbox on a form? a table field? And where are you using this expression: as
a criterion on some date field in a query? What's the context?

 

[chris23892 via AccessMonster.com]

Mahahahahhahaa.....

= DateAdd("ww", -1, #10/20/2008#) AND <= #10/20/2008#

Worked like a charm. Pull all records I needed. So, now, if I am curious
about a specific date and want the ALL records from a seven day period ending
with a specific date, I'll just use the above expression and substitute the
ending date.

Good stuff, I hope this can help others that are searching for solution like
this for their filter criteria in their query.

thanks again for all the help. May even bake this into a script to prompt in
our main DB if we use this enough. This is just too much fun. It's little
challenges like this for a specific task that make life great!!

The specific date if from me. Basically, I have an excel sheet that is linked
to this and other queries. I connect to the query through data import in
excel.

I use the dateAdd function to run a report weekly. pretty slick, it just
takes the CURRENT date and pulls all records in the last seven days. I want
to use this same logic to pull historical data (like the same time perior
last year or from two months ago). I'll just type it in the expression to
pull the data, no need for a user prompt since this is all behind the scenes
due to the data being pulled into excel automatically from this query.

I'm thinking adding your AND statment may be the trick. It is indeed pull all
records from the specified date. I'll check it out and get back with the
results. thanks for all the help, I really appreciate it!
[quoted text clipped - 11 lines]

textbox on a form? a table field? And where are you using this expression: as
a criterion on some date field in a query? What's the context?