The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Access (shudder): Convert dates?

Hi, all.

I've got a bunch of records with a date field in YYYYMMDDHHMMSS format.  For example, July 4th 2008 @ 9:15:23 PM would look like this:

I'm trying to write an Access VB function to give me the weekday, as a string, that one of these dates represents.  I figure that I can cast or convert the date into a VB Date object, and then retrieve the weekday as a property, but I'm at a loss as to how to accomplish that.

Any ideas?

Thanks for the help!
Thursday, July 03, 2008
Rough and ready:

Const TS As String = "20080704211523"
Dim DT As Date
Dim Day As Integer

DT = DateSerial(Left$(TS, 4), Mid$(TS, 5, 2), Mid$(TS, 7, 2))
Day = DatePart("w", DT, vbSunday)
MsgBox Array("Sunday", "Monday", "Tuesday", "Wednesday", _
            "Thursday", "Friday", "Saturday")(Day - 1)
Thursday, July 03, 2008
I didn't test this but it should work:

Public Function ConvertToWeekday(dateField As String) As String
    ConvertToWeekday = WeekdayName(Weekday(CDate(Mid(dateField, 5, 2) & "/" & Mid(dateField, 7, 2) & "/" & Left(dateField, 4))))
End Function

...and a query if you have the data in a table already...
SELECT CDate(Mid([datefield],5,2) & "/" & Mid([datefield],7,2) & "/" & Left([datefield],4)) AS NewDateField, WeekdayName(Weekday(NewDateField)) as WeekdayName
FROM Table1
Mike Saeger Send private email
Thursday, July 03, 2008
Since nobody has replied to this, I'll give it a shot.

Yes, this is non-trivial -- date to day of the week is a weird algorithm.

Suggestion -- find some of the "standard" VBA date formats, convert to that, then use the built-ins to find day of the week.

I know, you knew all this already.  What was your question again?
Thursday, July 03, 2008
You've already got two replies to try out.

While you are messing with dates in Access,  remember that days of the week are wrong in Excel for January and February of 1900.  I think this bug was introduced in Excel version 1 for compatibility with Lotus 1-2-3. Dates after March 1, 1900 should give the same weekday in Excel and Access.

AFAIK, the bug has never been fixed.

So be careful when using Excel to cross check your results.
Walter Mitty Send private email
Friday, July 04, 2008
And to expand on what Walter said, you might also have to keep an eye on the serial number. The reason for the kerfuffle is that Lotus and, for compatibility, Excel classify 1900 as a leap year when it wasn't. Thus an extra day in Feb (and in 1900), possibly leaving the serial numbers mismatched between Excel and Access. The serial number is the number of days since Jan 1, 1900 in both systems, so it seems logical that the serial numbers won't match., but I've never checked.
Ron Porter Send private email
Friday, July 04, 2008
Any reason why it has to be a VBA function rather than converting the date in a query, which wil generally be faster?

Here's two approaches. The first uses Access WeekDay and WeekDayName functions. The second is a little cleaner simply formatting the date serial. Both assume the date field is called MyDate.

Day1: WeekdayName(Weekday(DateSerial(Left([MyDate],4),Mid([MyDate],5,2),Mid([MyDate],7,2)))-1)

Day2: Format(DateSerial(Left([MyDate],4),Mid([MyDate],5,2),Mid([MyDate],7,2)),"dddd")

Why the shudder? Access is a great database development environment for the right purposes.

Regards -
Marcus from London
Friday, July 04, 2008
Yes, and I feel foolish now: both WeekdayName() and WeekDay() are core VB/VBA functions.  So much for air-coding.
Saturday, July 05, 2008
Hi, all.

Thanks for the help.  Here's the code that I ended up writing (for better or worse; this was my first piece of VB):

'Returns a three character indicator of the day of the week (Mon, Tue, Wed, etc)
Function GetWeekday(theTimestamp As String) As String
Dim year As String
Dim month As String
Dim day As String
Dim theDateMMDDYYYY As String

year = Strings.Left$(theTimestamp, 4)
month = Strings.Right$(Strings.Left(theTimestamp, 6), 2)
day = Strings.Right$(Strings.Left(theTimestamp, 8), 2)

theDateMMDDYYYY = month + "/" + day + "/" + year

Dim theDate As Date

theDate = Format(theDateMMDDYYYY, "MM-DD-YYYY")

GetWeekday = Format(theDate, "ddd")
End Function

As for my Access shudder, I suppose it's due to previous trauma; I enjoy using Access, but I've seen it used for some thing that it's not really designed to do.  Then, again, I suppose I could shudder like that for a lot of different products. :-)


Tuesday, July 15, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz