Hi,

I'm trying to get around an issue I'm having with a set of data that has a [Date] in a text string format (See below):

14th January 2009 (17 characters in length)
7th March 2009 (15 characters in length)
2nd September 2009 (19 characters in length)

Ideally I want the above to appear in the following format as a date value:

14/01/2009
7/03/2009
2/09/2009

The source data is from an Excel spreadsheet that is emailed to me weekly. I don't want to perform the conversion within Excel as my aim is to just to link the spreadsheet into the database and append the updated weekly data into a table for further manipulation.

If it is not possible to perform a conversion then could someone tell me how I select just the month from the above from the string (14th January 2009). If I could do this I could create a simple lookup table:

Month Month No
January 1
February 2
March 3
etc...


Then Bring the three elements together as I already know how to separate the [DD] and [YYYY]:


Application Date:([DD]&"/"&[MM]&"/"&[YYYY])

Hope that makes sense and that there is a simple solution.

Thanks in Advance

Steve

Hi Steve,

Welcome to Utter Access!

To get the month, you could try something like:

Split([FieldName], " ")(1)

or something like:

Dim MyData() As String
Dim MyMonth As String
MyData = Split([FieldName], " ")
MyMonth = MyDate(1)

(untested)
Hope that helps...

It isnt working for me unfortunately? Thanks for the suggestion.

Put this into a standard module (do not name the module the same as the function):



then you just use it in your query like:

MyNewFieldNameHere:ConvertToDate([YourOldFieldNameHere])

I just copy to code into the module as it is no amendments? I adjusted the query part:

Month: ConvertToDate([Date])

I have done this and get an error message of type mismatch and the code stops at:

ConvertToDate = DateSerial(varSplit(2), varSplit(1), varSplit(0))


Sorry for the stupid questions but i really appreciate the support.

You may have null values.

Also, you should NOT have a field named DATE as that is an Access Reserved Word. You should change that ASAP. Also MONTH is a reserved word.

You may need to use:

NewDate: ConvertToDate(Nz([Date]))

There are no nulls and its still not working. Nightmare!!

Try this,

I took Bob's mod and modified;-

Works for dates supplied.

Cheers
Darren

Modify Bobs code to remove the ordinal text and change the DateSerial() to CDate().