Access Date Formatting / Extraction

[applebyd]

Good morning all.

I'm a bit stuck as I cannot use a LEN command with the problem I have. I have had a look around the site and cannot see a simple (or even complex) fix.

I have an extract from a BI system that in four columns produces a text field with a
date in the format: January 1, 1900 12:00:00 AM

What I am looking to do is extract DAY, MONTH and YEAR from the source data. LEN doesn't work as the actual number of characters in the month changes.

Can anyone suggest how I can attack this? Online help hasn't been too productive as all
the solutions I have found assume month to be in an MM format and not alphabetical.

Any help greatly appreciated.

Regards

DaveA

 

[applebyd]

Just checked.

The format when I look at the download file in Excel is actually 'General', not, Text.

Sorry

Thanks again..

DaveA

 

[frank_AL]

You can use the VBA Split Function. I put your entry in cell A3 and then ran this code module.
The Split Function puts results in an Array (datearray) and then you can pull out the piece parts you want to use.

The code returned:
Day = 1,
Month = January
Year = 1900

We could in code clear the "," from the Day result and could convert the Month from the spelled out Month (January) to a numeric.

Here's the code:

Option Explicit

Sub BreakDates()

Dim datearray() As String
Dim day As String
Dim month As String
Dim year As String

datearray() = Split(Range("A3"))
day = datearray(1)
month = datearray(0)
year = datearray(2)
MsgBox day & " " & month & " " & year
End Sub

 

[applebyd]

Thanks.

Any way this can be done in Access rather than Excel?

I could write a convertor for the file to 'tidy' the data before import.
That would add an extra stage that I don't mind, however, would prefer a one stop approach.

Anyway, that works a treat if I have to go for PlanB

Take care
Regards
DaveA

 

[Joe4]

You have a few choices.

1. To write some VBA code to import the data by looping file the text file recordset and do the conversion on the field as you are importing it (most complex method)

2. Import the file to "temporary" table first, importing this field as a Text field.
Then, convert the field to a Date value and use an Append Query to write the records to your "permanent" table.
If your field is called "Field1", here is the calculation that will drop the time component and coerce it to a valid date entry:

CDate(Left([Field1],InStrRev(Left([Field1],InStrRev([Field1]," ")-1)," ")-1))

 

[applebyd]

You have a few choices.

1. To write some VBA code to import the data by looping file the text file recordset and do the conversion on the field as you are importing it (most complex method)

2. Import the file to "temporary" table first, importing this field as a Text field.
Then, convert the field to a Date value and use an Append Query to write the records to your "permanent" table.
If your field is called "Field1", here is the calculation that will drop the time component and coerce it to a valid date entry:

CDate(Left([Field1],InStrRev(Left([Field1],InStrRev([Field1]," ")-1)," ")-1))

Superb, exactly what I needed!
Many thanks.

As usual I'm amazed by the support and knowledge here!

Regards

DaveA

 

[Joe4]

You are welcome!
Glad we were able to help.