[Elaine Tucker]

Hey ya'll.

I've got an access db that has a field of "Date of Occurrance"

I need to query on date = This week based on comparing "today" (Now) with the Date of Occurrance.

I have calculated the weekday value using Weekday([Date of Occurrance}) and that works to get me the 1,2,3,4,5,6,or 7 value.

What now?

[DrewAllen]

Use the DateDiff() function. MS Access, Crystal Reports, and MS SQL Server all have this function, but they all use slightly different syntax. Use the MS Access Help to find the correct syntax.

DateDiff("w", {Date of Occurence}, Today) = 0

Drew

[mwittman]

Drew's solution is right on for "week" as in any "7 days" or "5 days" backward or forward from Date of Occurrence.

For (MS Access) "Are these two dates in the same calendar week":


IIf( (DatePart("ww", [Date of Occurence])) = (DatePart("ww", Now()), "Same Week", "Different Week" )

[DrewAllen]

Quote:

Originally Posted by mwittman

Drew's solution is right on for "week" as in any "7 days" or "5 days" backward or forward from Date of Occurrence.

For (MS Access) "Are these two dates in the same calendar week":


IIf( (DatePart("ww", [Date of Occurence])) = (DatePart("ww", Now()), "Same Week", "Different Week" )

This is why I recommended reading the documentation. MS Access has two different "week" date parts. "w" uses the weekday of the first date as the beginning of the week, whereass "ww" uses the system default (usually Sunday in the US) as the start of the week unless the optional parameter to set the start of the week is provided.

Crystal Reports has the same distinction as MS Access. On the other hand, MS SQL Server only ever uses the system default.

WARNING: mwittman's formula above fails when the Date of Occurrence and Today fall in the same calendar week, but in different calendar years. If you simply replace the "w" with a "ww" in my original formula, it will work.

Drew

[mwittman]

Quote:

Originally Posted by DrewAllen

This is why I recommended reading the documentation. MS Access has two different "week" date parts. "w" uses the weekday of the first date as the beginning of the week, whereass "ww" uses the system default (usually Sunday in the US) as the start of the week unless the optional parameter to set the start of the week is provided.

Crystal Reports has the same distinction as MS Access. On the other hand, MS SQL Server only ever uses the system default.

WARNING: mwittman's formula above fails when the Date of Occurrence and Today fall in the same calendar week, but in different calendar years. If you simply replace the "w" with a "ww" in my original formula, it will work.

Drew

Drew is correct - in your case, Elaine, the year must be checked as well (other use cases may intentional ignore the year b/c "same week # of given year" is what needs to be tested).

So, if you want to customize the firstDayOfWeek parameter, or othewise like writing and maintaining more code, use something like this (MS Access only):

IIf((Year([Date of Occurence]) & DatePart("ww",[Date of Occurence])) = (Year(Now()) & DatePart("ww",Now())),"Same Week","Different Week")

...

[Elaine Tucker]

Thanks so much Micah & Drew! I kept trying the DatePart and DateDiff yesterday, but kept getting an error message (of course I can't replicate it now... lol)... also I was thrown by the access "help" because the format I was prompted for was:

DateDiff («interval», «date1», «date2», «firstweekday», «firstweek») and for the life of me I couldn't figure out the firstwekday and the firstweek parameters.... yea that was a DUH on my part.

However the formula that ya'll came up with will work so much better. I haven't actually tried it yet, but it makes logical sense to me (at least at the moment).

Rock!

[Elaine Tucker]

IT Works! YAHOOOO********>!!!!!

Muchas Gracias!!!

[DrewAllen]

Quote:

Originally Posted by mwittman

Drew is correct - in your case, Elaine, the year must be checked as well (other use cases may intentional ignore the year b/c "same week # of given year" is what needs to be tested).

No, that's the point I'm trying to make. If you use the correct tool, you don't need to check the year. It's handled automatically. DateDiff() is the correct tool, DatePart() is not.

The formula should be

DateDiff("ww", [Date of Occurence],Now())

Drew

[Elaine Tucker]

Got it... I'll use the dynamic one -- with DateDiff

[mwittman]

Quote:

Originally Posted by DrewAllen

No, that's the point I'm trying to make. If you use the correct tool, you don't need to check the year. It's handled automatically. DateDiff() is the correct tool, DatePart() is not.

The formula should be

DateDiff("ww", [Date of Occurence],Now())

Drew

To quote Grey's Anatomy (the TV show) "This is a teaching hospital, Dr _______".

Ideas and examples written to this forum can bring insight to a whole class of problems and not just the one presented in the original question. Discussing more than one solution can facilitate the serendipity of applying information gleaned from challenge A to solving challenge B.

I'm all for persuading the community with the merits of a claim - sure! but what I don't agree with is the notion of "the correct tool". "Less verbose construct", "more readable code", "a tool with fewer dependencies on the system configuration", "applying Occam's razor..." — sprinkle those over an argument and now we've got a satisfying debate.

When I stated:

" the year must be checked as well (other use cases may intentional ignore the year b/c "same week # of given year" is what needs to be tested)"

"the year must be checked", whether internally with DateDiff() or externally with DatePart() - either can be used to give the correct result. The DatePart() method is more verbose code, and that's why I had wirtten the ironic, self-deprecating: use my solution "if you...like writing and maintaining more code".

Note-to-self: Work on my ironic delivery.

Anyway, my goal is not to start a flame war, but to explain myself. And I'll say it again: Drew is right; Elaine's specific problem is cleanly solved with DateDiff(), and you've confirmed that, Elaine. It's all good.

[Elaine Tucker]

I appreciate all the help on the forum - especially the explanation of the differences in the formulas that the "everyday" person can understand.

I struggled for quite a while trying to figure out on my own the difference between all the date formulas in crystal... (apparently I need to get one of the Nut books that is more up-to-date than my reference book...)

Since ya'll have practical experience in the real world it really helps to keep reminded of all the little details that can cause reports and formulas to go awry.