MS Access extract date

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

I'm having a little bit of difficulty trying to extract a date out of some fields because the format changes slightly on every record.
The fields are like:
Dec 5 (0.5hr)
Dec 5, 12 (0.5hr)
Sept 8
March 10, (.75hr)
January 7, 15 (1hr)

I have this so far:
        comma_exists = InStr(1, ItemLineDesc, ",")

        If (comma_exists > 0) Then
            first_date = Left(ItemLineDesc, InStr(1, ItemLineDesc, ","))
            first_date = Left(ItemLineDesc, InStr(4, ItemLineDesc, " "))
        End If

But with the comma I still have the comma at the end for the variable first_date when there is a comma.

I basically just need the date. Any help is appreciated.

Question by:Victor Kimura
    LVL 48

    Assisted Solution

    by:Gustav Brock
    You could use:

    ItemLineDesc = Replace(ItemLineDesc, ",", "")

    to first filter out the comma.

    LVL 61

    Accepted Solution

    You could also use this one-liner for the whole thing:

    replace(split(ItemLineDesc," ")(0) & " " &  split(ItemLineDesc," ")(1),",","")

    Author Closing Comment

    by:Victor Kimura
    mbizup, that's awesome! In one line!

    Thanks, cactus_data
    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now