Wednesday, December 15, 2010

Access 2007: how to use WeekdayName

So I'm working on this little project that calls for a column containing the day of the week. Odd, I know, but I thought it would be nice for Access to pass that along. There are a number of ways it can be done, but I thought it would be interesting to use WeekdayName because, well, it returns a name.

Of course, given that it's an Access function, Microsoft has little documentation and no examples for it. (Some time ago, Microsoft gave up providing useful examples for most of their functions ... of course they're not the only ones to do that. Adobe's examples for ColdFusion help are just as bad as Macromedia's were: they just show you what the syntax is without showing you results.) A quick Google didn't show me anything definite, and in fact showed me one suggestion that was flat-out wrong (that you can't control how it returns the day).

Now, that suggestion did have a good point – use a different function because of how this one works – but I'll show you anyway.

Here's the call: WeekdayName(weekdayvalue,abbreviate,firstdayofweek). See the problem? You're not passing a date, you're passing a Weekday, which means that it's simply telling you that the "first" day of the week is Monday (or whatever).

Anyway, the first value is a weekday value, not a date. The second value (optional), is a Boolean that specifies whether you want a three-letter abbreviation returned instead of the full weekday. (No apparent option to modify that.) The third value (optional) is the day of the week that you will designate as the first, with 1 being Sunday, 2 being Monday, etc. If you don't specify it, then you get whatever the system setting is, which may or may not be what you want.

Why did Microsoft make WeekdayName use an ordinal value instead of a date value? Because they're dumb. Anyway, if you want to use it, it looks like this:

WeekdayName(Weekday(#12/15/2010#),True,1) returns Wed. (Yes it does. I just tested it.) WeekdayName(Weekday(#12/15/2010#),False,1) returns Wednesday.

There you go ... but use this instead: Format(#12/15/2010#, "ddd").

No comments:

Post a Comment

There was an error in this gadget