MS Access get date and

Posted on 2012-09-07
Last Modified: 2012-09-07

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,
Question by:Victor Kimura
    LVL 11

    Expert Comment

    by:Guru Ji
    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
    LVL 61

    Assisted Solution

    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.
    LVL 61

    Assisted Solution

    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)


    Author Comment

    by:Victor Kimura
    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
    LVL 61

    Assisted Solution

    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...
    LVL 61

    Assisted Solution

    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.
    LVL 61

    Accepted Solution

    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

    Author Closing Comment

    by:Victor Kimura
    thanks! that's helpful!

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now