Date Query
in [Queries]
|
From: SamMexico via AccessMonster.com on 14 Apr 2010 11:03 Excellent - thanks very much! The last thing is to get the week numbers to show themselves in relation to the 21/12/2009 as being the start of week 1... Is this possible? -- Message posted via http://www.accessmonster.com
From: SamMexico via AccessMonster.com on 14 Apr 2010 11:09 SamMexico wrote: >Excellent - thanks very much! > >The last thing is to get the week numbers to show themselves in relation to >the 21/12/2009 as being the start of week 1... > >Is this possible? The extra problem being that there are dates before this that should be counted a Week -1 etc... -- Message posted via http://www.accessmonster.com
From: ghetto_banjo on 14 Apr 2010 11:58 Hmm you should be able to do that. But instead of the DatePart function, you will need to use the DateDiff function to calculate the number of weeks, whether it is positive or negative. I am using American Date formats, MM/DD/YYYY. DateDiff("ww", #12/21/2009#, [yourDateField], 2) That ,2 at the end just specifies we are using a Monday as the first day of a week. Now the problem is that there is no week 0, right? You want Dec 21 to be the start of week 1, meaning Dec 20 would be the day of week -1 correct? I am assuming this going forward. So you need an iif statement to figure out if you need to add 1 or not. IIf([yourDateField] < #12/21/2009#, DateDiff("ww", #12/21/2009#, [yourDateField], 2), DateDiff("ww", #12/21/2009#, [yourDateField], 2) + 1) Looks complicated, but all it is saying is if the Date is before Dec 21, take the true DateDiff value, otherwise, take the DateDiff value and add 1. You can use this formula in your Update query.
From: SamMexico via AccessMonster.com on 14 Apr 2010 14:55 I will try that tomorrow, your help has been invaluable - thank you ever so much! Sam -- Message posted via http://www.accessmonster.com
From: SamMexico via AccessMonster.com on 15 Apr 2010 05:16 Hi again... I did exactly what you advised and it has gone really well using: UPDATE Data SET Data.[Week Number] = DateDiff("ww",#12/21/2009#,[Date of Consent],2) WHERE (((Data.[Week Number])=IIf([Date of Consent]<#12/21/2009#,DateDiff("ww", #12/21/2009#,[Date of Consent],2),DateDiff("ww",#12/21/2009#,[Date of Consent] ,2)+1))); The slight problem is that the query that has the week number in (after update) has the 12th and 13th of this month recorded in Week 16 whereas I make it Week 17. I tried to change the date a week back from the 12/21/2009 to the 12/14/2009 but this tweak doesn't seem to work. I know it's a small thing... Any ideas? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Need to migrate phone no into the next record if the family Next: Average interpurchase times |