Explanation needed

[thart21]

I have been asked to insert the following into an Access query. It is not working the way the user states that it should and I would like to know if someone could break it down for me and explain what I should expect to see in my query results based on the below function.

ORGACWEEK: Int(([ORGAC_DATE]-"20100101")/7)*7+"20100101"

The field 'ORGAC_DATE' is a date field formatted mm/dd/yyyy

The function in the query should return "ORGACWEEK" always as a Sunday. They are telling me it is working in other Access queries but I can't get it to work and don't see how it is calculating on a Sunday only. Any help would be appreciated.

What I am getting is i.e. ORGAC_DATE 10/15/2010, ORGACWEEK is coming back as 10/9/10 and should be 10/10/10

Also, if it helps, this works in Excel and returns the correct date, I just don't know how.

=INT((K2-"01/01/06")/7)*7+"01/01/06"



Thanks,

Toni

[jzwp11]

SQL which is the language on which queries are based does not work the same as Excel, so the expression will not work the same in the query. I cannot explain why it is working in other queries but not this one. SQL always evaluates dates as mm/dd/yyyy. I am guessing that it is probably having difficulty with the literal string "20100101". For SQL to correctly evaluate the date, it should be expressed as #01/01/2010#, but unfortunately it will not evaluate the expression correctly because it will yield the Friday prior to the ORGAC_DATE (for the dates I tested) which is what I think you suspected.

The other problem with the expression is that you might have to go in and update the query each year to change the #01/01/2010# to the corresponding year. In the limited testing I did, I got either the preceeding Friday or Saturday returned when entering some selected 2011 dates

To correctly handle this for any year, I would recommend the following expression:

ORGACWEEK: DateAdd("d",-1*Weekday(ORGAC_DATE)+1,ORGAC_DATE)

The above expression uses the weekday function to return the number of the day on which ORGAC_DATE falls (As I have shown it, Sunday is assumed to be day 1 of the week; you can change this with the optional parameter of the Weekday() function if you want something different). After the weekday() function returns a number, I multiply it by -1 since I will have to subtract it from the actual ORGAC_DATE, but if I don't add 1 day back it will give me the preceeding Saturday. As an example, let's say that ORGAC_DATE=10/15/2010. 10/15/2010 is a Friday or the 6th day in the week. The weekday() function will return the number 6. Multiplying it by -1 yields -6, now add 1, so we now have -5. Add -5 to 10/15/2010 and you get 10/10/2010.


When you add or subtract values from dates in Access, it is best to use the DateAdd() function rather than something like this [ORGAC_DATE]-#01/01/2010#.

[thart21]

Thank you so much for validating my suspicions and explaining how it works. Also, thanks for the correct expression, works perfectly and makes sense too!

Have a great weekend!

Toni

[jzwp11]

Glad to help out! Have a good weekend too!