how to tell access 'last week'

[shutzy]

i have a combo box that enables the user to quickly select time frames. at the minute i have 1 week, 2 weeks, 1 month, 3 months, 6 months, 1 year and All. i would like to add to this list

last week
last year
this year
this week

i dont really know how to tell access to do this. at the minute i have set property. on selection of the combo box i have macros that set the property of txtDateFrom and txtDateTo eg

AfterUpdate: If comboDateSelection="1 Week" then
SetProperty: Item: txtDateFrom
Expression: DateAdd(("ww",-1,Date())
SetProperty Item: txtDateTo
Expression: Date()

as you can probably tell i have done this in a macro so i would like to keep it that way.

is it possible?

 

[John Big Booty]

Have a look at the DatePart() function.

For last week you could use the following criteria;

DatePart("ww" ,[YourDateField]) = DatePart("ww", Date())-1 And DatePart("yyyy", [YourDateField])= DatePart("yyyy", Date())

The rest you should be able to formulate yourself.

 

[shutzy]

i would of like to say a 'BIG THANKS' but there is no button for that. never even heard of DatePart(). it looks a little more complicated for me to write myself so ill stick to what you have done and keep editing.

thanks again

 

[John Big Booty]

i would of like to say a 'BIG THANKS' but there is no button for that. never even heard of DatePart(). it looks a little more complicated for me to write myself so ill stick to what you have done and keep editing.

thanks again

There's always the Reputation Button, that's the little scales button in the bottom left hand corner of each post

 

[shutzy]

hi john, sorry but im starting to mess with it and im not getting the date i want. i am trying to put it in a macro. i have date from and date to txt boxes so in the date from txt box i have

DatePart("ww",Date(),1)

is this right and how would i do the last day of the week for the date to txt box?

thanks

 

[shutzy]

sorry its giving me uk format 05-feb-1900

 

[John Big Booty]

Sorry I'm not sure what you are trying to do now

This code;

DatePart("ww",Date(),1)

Will simply return the Week Number (of the year) for the current date (with the week start falling on Sunday (which is the default anyway so the ,1 portion is somewhat superfluous)

 

[John Big Booty]

... just to expand on my first post. The code presented would be used in a query (as criteria) and would return all records that fell in the previous week based on the current date.

 

[shutzy]

basically im trying to get the date for the first day of last week. i have 2 txt boxes that are the criteria for a query. one is txtDateFrom and the other is txtDateTo.

have i just messed everything up.

i did try for txtDateTo DateAdd("ww",6,DatePart("ww",Date(),1)) and that gave me 11-feb-1900. so something is working its just giving me 1900 and feb for some reason. also i have looked at my windows calendar and 5-feb-1900 is a monday!

 

[shutzy]

sorry just got your second post. its in a macro.

if [combo]='Last Week' then
Set Property: [txtDateFrom]
Expression: ??????????

 

[shutzy]

ive just thought, am i going to have to do many macros. ie

If [combo]='Last Week' then
If Weekday(Date())=1 then
SetProperty: [txtDateFrom]
Expression: DateAdd("dd",-7,Date())
ElseIf Weekday(Date())=2 then
SetProperty: [txtDateFrom]
Expression: DateAdd("dd",-8,Date()) etc,etc,etc,

the only problem i see with this is when you get to months. they all have different days. i dont mind things being labour intensive if what i am doing will work in the end. but for months it will be a hell of a lot of work, not to mention years.

there must be a better way of doing this surely?

 

[shutzy]

sorry. i have been typing SetProperty. i mean SetValue.

 

[John Big Booty]

If all you wish to do is determine the first day of the previous week (with Sunday as day 1 of the week), use;

[YourDateField]-[URL="http://www.techonthenet.com/access/functions/date/weekday.php"]Weekday[/URL]([YourDateField])-6

Which is based on info available here.

 

[shutzy]

hi john, thanks for that it works great. now for doing the months and years and quarter ive looked at the link that you supplied but the DateSerial(Year(Date()), Month(Date())-1,1) dosnt seem to work in my macro for the first day of the previous month.

i think for me to be able to do all this on my own without someone spoon feeding me is to understand what is happening more. the link is great for a resource but it dosnt explain why?

would you mind telling me why

Date()-Weekday Date())-6 returns a value of the first day of last week.

also and if im not pushing too much tell me why
DateSerial(Year(Date()), Month(Date())-1,1) would give me the first day of last month or in my case should but dosen't. i know the obvious parts like 'Weekday', 'Date()' and Month.

thanks again john

 

[John Big Booty]

hi john, thanks for that it works great. now for doing the months and years and quarter ive looked at the link that you supplied but the DateSerial(Year(Date()), Month(Date())-1,1) dosnt seem to work in my macro for the first day of the previous month.

i think for me to be able to do all this on my own without someone spoon feeding me is to understand what is happening more. the link is great for a resource but it dosnt explain why?

would you mind telling me why

Date()-Weekday Date())-6 returns a value of the first day of last week.

...

Date() - WeekDay(Date()) + 1

The WeekDay() function returns the day (number) of the week of the current day, so subtracting that number (plus one) from the current date will will return the first day of that week. So my adjustment to that to return the first day of the previous week was to simply subtract a further 6 days from the result. Remembering that to get the first day of the current week we needed to add one to the current day number so to get the first day of the previous week we simply need to subtract a further 6 days.

...

also and if im not pushing too much tell me why
DateSerial(Year(Date()), Month(Date())-1,1) would give me the first day of last month or in my case should but dosen't. i know the obvious parts like 'Weekday', 'Date()' and Month.

thanks again john

How do you mean the code does not work

 

[shutzy]

i put the code in my setvalue of my macro and it came up with errors.

how bloody frustrating. i must of done something wrong cause when i went back to access to recreate the error. i did the macro again, selected the 'Last Month' option and it worked. sorry john i must of done something wrong.

thanks again for your help.

 

[Pat Hartman]

Maybe it's time to switch to VBA. It will actually be simpler and easier to read since most of these calculations are just one-liners.

Select Case MyCombo
    Case "Last Week"
        MyDate = Date()-Weekday Date())-6    
    Case "Last Year"
        MyDate = cDate("1/1/" & Year(Date()) - 1)   
    Case ...
 End Select