Date in previous week

[rainbowruthm]

Hi,

I have spent a long time making a query that calculates samples due to be sent out after one month then every two months for two years for a study I am setting up.
The query is as follows:

FUDueThisWkorEND: IIf(DatePart("ww",(IIf([Follow-upMonth]<=1,((DateAdd("m",([Follow-upMonth]+1),[Baseline]))),IIf([Follow-upMonth]>23,"11/11/1111",((DateAdd("m",([Follow-upMonth]+2),[Baseline])))))))=DatePart("ww",Date()) And Year((IIf([Follow-upMonth]<=1,((DateAdd("m",([Follow-upMonth]+1),[Baseline]))),IIf([Follow-upMonth]>23,"11/11/1111",((DateAdd("m",([Follow-upMonth]+2),[Baseline])))))))=Year(Date()),"Follow-upDue",Null)

Where [Baseline] is the date of the first sample and [Follow-upMonth] is the months after baseline that the previous sample can be (which is 0, 1, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22 and 24).

To make the Follow-upDue statement come in the current week I used the microsoft help idea -
Contain dates that fall during the current week
DatePart("ww", [SalesDate]) = DatePart("ww", Date()) and Year( [SalesDate]) = Year(Date())


The problem is, that we actually need to know a sample is due the week before and not the current week, as that is leaving it too late.

I would therefore like to change my coding into this format (again from the microsoft help):
Contain dates that fell during the previous week
Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1

But it is too complicated and I can't...


Would anyone be able to change my query so it showed dates in the previous week rather than the current week??


THANKS

 

[midmented]

I hate complicated queries so sometimes I will send one of the fields to a function to process it. I made a new module and put this function in it.

Function GetLastWeek(dteSalesDate As Date)
    GetLastWeek = DatePart("ww", dteSalesDate) - 1
End Function

In your query, make a new field control source of:
LastWeek: GetLastWeek([SalesDate])

Worked for me.

 

[namliam]

To get the next weeknumber, you simply have to find a date somewhere in next week.

You can do this by adding 7 days to todays date
Either use the DateAdd function
or simply do Date() + 7

 

[midmented]

Whoops, I guess you're looking for next week and not the previous week. Just change the - 1 to + 1 in the function.
If you don't want to use the function method, just use the same code for the current week and add the +1 or -1 to get the previous or next week.
What I have found out in working with queries with long calculations is they tend to give false data a lot easier than using a function to process the answers.

 

[rainbowruthm]

OK - I just added "+ 7" every time I had Date() so it was (Date() + 7) and that seems to have worked.
Thanks