Date issue with Between [] and []

[Mac]

I have the following set up

Between [From Date] And [To Date]

What I want it to do is automatically fill in the from and two dates based
off the following...
We are in a current week...At anytime during the current week, I want to be
able to click a button or run a macro or query...something....without having
to manually look up or enter dates in the From-To fields.

I need the [From Date] to read the Previous week's Monday's date and the [To
Date] to read the previous week's Friday's Date. If I can't do this with the
Between...And statement, is there SQL commands that would work? Can I use a
date picker in the parameters where your prompted to enter in a date?

 

[fredg]

I have the following set up

Between [From Date] And [To Date]

What I want it to do is automatically fill in the from and two dates based
off the following...
We are in a current week...At anytime during the current week, I want to be
able to click a button or run a macro or query...something....without having
to manually look up or enter dates in the From-To fields.

I need the [From Date] to read the Previous week's Monday's date and the [To
Date] to read the previous week's Friday's Date. If I can't do this with the
Between...And statement, is there SQL commands that would work? Can I use a
date picker in the parameters where your prompted to enter in a date?

If you always want the full previous weeks data (Sunday through
Saturday), why not just:
Add a new column to the grid.

WeekOfatePart("ww",[DateField])

As criteria on this column, write:
DatePart("ww",Date())-1

If run during the week of 5/11 through 5/17/2008, it will return all
records from 5/4 through 5/10.

 

[AkAlan via AccessMonster.com]

I think this might help.

Dim datLastMonday As Date

If DatePart("w", (Date - 7), vbMonday) = 1 Then
datLastMonday = Date - 7
Else
Debug.Print DatePart("w", (Date - 7), vbMonday)
datLastMonday = Date - (DatePart("w", Date, vbMonday) + 6)

End If

I have the following set up

Between [From Date] And [To Date]

What I want it to do is automatically fill in the from and two dates based
off the following...
We are in a current week...At anytime during the current week, I want to be
able to click a button or run a macro or query...something....without having
to manually look up or enter dates in the From-To fields.

I need the [From Date] to read the Previous week's Monday's date and the [To
Date] to read the previous week's Friday's Date. If I can't do this with the
Between...And statement, is there SQL commands that would work? Can I use a
date picker in the parameters where your prompted to enter in a date?

 

[Mac]

Where would I paste this info? I'm still learning the ins and outs of
access, and everyone around me is talking SQL and VB language....over my head.

I think this might help.

Dim datLastMonday As Date

If DatePart("w", (Date - 7), vbMonday) = 1 Then
datLastMonday = Date - 7
Else
Debug.Print DatePart("w", (Date - 7), vbMonday)
datLastMonday = Date - (DatePart("w", Date, vbMonday) + 6)

End If

I have the following set up

Between [From Date] And [To Date]

What I want it to do is automatically fill in the from and two dates based
off the following...
We are in a current week...At anytime during the current week, I want to be
able to click a button or run a macro or query...something....without having
to manually look up or enter dates in the From-To fields.

I need the [From Date] to read the Previous week's Monday's date and the [To
Date] to read the previous week's Friday's Date. If I can't do this with the
Between...And statement, is there SQL commands that would work? Can I use a
date picker in the parameters where your prompted to enter in a date?

 

[Mac]

I don't want the full previous week, just the previous mon-fri.

I have the following set up

Between [From Date] And [To Date]

What I want it to do is automatically fill in the from and two dates based
off the following...
We are in a current week...At anytime during the current week, I want to be
able to click a button or run a macro or query...something....without having
to manually look up or enter dates in the From-To fields.

I need the [From Date] to read the Previous week's Monday's date and the [To
Date] to read the previous week's Friday's Date. If I can't do this with the
Between...And statement, is there SQL commands that would work? Can I use a
date picker in the parameters where your prompted to enter in a date?

If you always want the full previous weeks data (Sunday through
Saturday), why not just:
Add a new column to the grid.

WeekOfatePart("ww",[DateField])

As criteria on this column, write:
DatePart("ww",Date())-1

If run during the week of 5/11 through 5/17/2008, it will return all
records from 5/4 through 5/10.

 

[John Spencer]

You might try the following expression as the criteria


Between DateAdd("d",2-Weekday(Date()),Date())-7 and
DateAdd("d",2-WeekDay(Date()),Date())-3

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================