• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1037
  • Last Modified:

MS Access get date and


I have this string in a field:

Dec 7, 14 (0.5)
Dec 8 (0.75)
Dec 1 (1 hr)

I'm wondering how I can get the day of the week when I extract the first date. The first dates in this case are:
Dec 7
Dec 8
Dec 1

It's for the year 2011 but I would like to keep the year as a variable.

Thank you,
Victor Kimura
Victor Kimura
  • 5
  • 2
5 Solutions
Guru JiCommented:
To get the name of the day, you can try the format function

? format(date, "dddd")

But there are other ways to work with this, too, for instance the weekday function

? weekday(date)

 - note that default is that sunday is first day, for other, use

? weekday(date, vbMonday)

Also In Access, the Weekday function returns a number representing the day of the week (a number from 1 to 7) given a date value.

You can assign that number with strings like Monday - Sunday

See example of use of Weekday function
Weekday function is part of it.

Give this query a try for the year 2011:

SELECT WeekdayName(Weekday(YourField  ", "  & "2011")) AS DayOfTheWeek
FROM YourTable

This is assuming that you have extracted the first part of the date as you mentioned in your original post.
If you want this in VBA rather than a query:

Function GetWeekdayName (DateString as string, intYear as Integer)
         GetWeekdayName =  WeekdayName(Weekday(DateString ", "  & intYear))
End Function

Open in new window

You would call that function like this -- using a msgBox for example purposes:

Msgbox GetWeekDayName("Dec 7", 2011)

Msgbox GetWeekDayName("Dec 15", 2012)

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi mbizup,

That's great! It's a bit of a silly question but how do I run that. I saved it as a Module for my database (see the snapshot) and then when I click on Run this Macros pop-up box comes up.

Like this code runs when I click on Run but when I created the function the Macros window opens instead. How come?

Option Compare Database

Sub SelectIntoX()

    Dim dbs As Database
    Dim vendor_name As String
    Dim rsCount As Integer
    Dim N As Integer
    Dim I As Integer
    Dim ItemLineSeqNo As Integer
    Dim ItemLineItemRefListID As String
    Dim ItemLineItemRefFullName As String
    Dim ItemLineDesc As String
    Dim student_name As String
    Dim first_date As String
    Dim comma_exists As Integer
    Dim date_raw As String
    Dim date_formatted As String

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("F:\BackUp\ClientsWebsite\OMusicStudios\BackUp\BackUp_G_USB\Administration\Quickbooks\MSAccess\qb_export_12_30_11_2101.accdb")

    Set rst = dbs.OpenRecordset("SELECT BillItemLine.*" _
    & "FROM BillItemLine WHERE (((BillItemLine.[VendorRefFullName]) Like '*sunshine*') AND ((BillItemLine.[TxnID])='20A8D-1325181637'));")
    rsCount = rst.RecordCount
    'N = 3
    'For I = 0 To rsCount
    '    name = rst.Fields(0)
    'Next I
    If rst.EOF Then
       MsgBox "No records found"
       Exit Sub
    End If
    I = 0
    Do Until rst.EOF
        I = I + 1
        'MsgBox "The value of field VendorRefFullName is " & rst!VendorRefFullName
        vendor_name = rst!VendorRefFullName
        ItemLineSeqNo = rst!ItemLineSeqNo
        ItemLineItemRefListID = rst!ItemLineItemRefListID
        ItemLineItemRefFullName = rst!ItemLineItemRefFullName
        ItemLineDesc = rst!ItemLineDesc
        student_name = Right(ItemLineItemRefFullName, Len(ItemLineItemRefFullName) - InStr(1, ItemLineItemRefFullName, ":"))


End Sub
Go into the VBA Editor and place this in the immediate window (It is a smaller window in the editor usually located below the code window):

Msgbox GetWeekDayName("Dec 7", 2011)

Hit "enter" after typing or placing that in the immediate window...
Also, this line

---->>> Msgbox GetWeekDayName("Dec 7", 2011)

Should not go in with the rest of the code.

Just type it into the immediate window and hit enter.
Here's a correction to the code.  It was missing an ampersand:

Function GetWeekdayName(DateString As String, intYear As Integer)
         GetWeekdayName = WeekdayName(Weekday(DateString & ", " & intYear))
End Function

Open in new window

And this shows how you would test it from the immediate window:Weekday function
Victor KimuraSEO, Web DeveloperAuthor Commented:
thanks! that's helpful!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now