Splitting number from text (Date)

[SSHA_SP]

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

 

[Ron_dK]

If the date is a field in your table, why not change the format of that field to : short date.

Hth

 

[SSHA_SP]

The date is seen as text so it wont work in its current format.

Thank you for the suggestion though

 

[Ron_dK]

Can't you change your textfield to a date field then?

 

[SSHA_SP]

I appended the data into a table and tried to change the value but it just resulted in errors.

What is the easiest way to split number and text Eg: "13th" = "13" "th" separate fields?

 

[Ron_dK]

You might do a search for left function Instr funtion or mid function. That will show you how to separate data in a string.

Hth

 

[SSHA_SP]

many thanks

 

[gemma-the-husky]

you really need to sort this at an early stage

you need to get this brought into your access table as DATE, not as a string.

if you dont you will always have problems sorting data, and handling dates in general

so how is the data getting into the system, as 13th May 2009
and what if there is only one day = does it show 7th May, or 07th May.

 

[SSHA_SP]

The source data is from an excel ss. I didnt want to convert it within excel as I was hoping to just link the ss to the database.

Its Shows 7th May but would have a lenght of 8 as there is a blank space at the front.

 

[Kiwiman]

Howzit

Should you not get it sorted at source you can try something like the below

This could get you on the track. There are probably better ways of doing it,,,

It assumes the date in your string is “13th May 2009” - inclusive of the speech marks and the day part is two digits

You call the module in a select query \ update query etc

RealDate:MthInt(yourfield)

In a module

Option Compare Database
Option Explicit

Public Function MthInt(Mth As String) As String

    Dim intMth As Integer, intYear As Integer, intday As Integer, intlen As Integer
    Dim dteDate As Date

    ' Works where the string is in the following format (inclusive of speech marks "01 Septemeber 2009"
    intMth = MthPlaceInt(Mth)   ' What is the month in the string
    Debug.Print intMth

    If intMth = 13 Then         ' if loop was ended as no match found then exit the function
        MsgBox "No month found", vbCritical, "Error"
        Exit Function
    Else

        intlen = Len(Mth)       ' Find the length of the string - used in the intyear calc
        Debug.Print intlen


        intYear = Val(Mid(Mth, intlen - 4, 4))  'Assumes year in 4 digit format, and a " is the last character of string
        Debug.Print intYear

        intday = Val(Mid(Mth, 2, 2))            ' Assumes the day is a 2 digit day and the first character of string is a "
        Debug.Print intday


        MthInt = DateSerial(intYear, intMth, intday)    ' Build the date using dateserial
        Debug.Print MthInt
    End If


End Function


Private Function MthPlaceInt(Mth As String)

' To find the month in the string.  The loop will end if the string is found
    
    Dim i As Integer    ' Holds the value to be used for the Select Stmt
    Dim x As Integer    ' Determines if the loop should continue or not
    Dim z As Integer    ' The value of the starting character of text being searched
    
    
    x = 0               ' Set x = 0
    i = 1               ' Set the value to activate case 1

    Do

        Select Case i ' Only want to have 13 case stmts as there are only 12 months and one for no match
            Case 1
                z = InStr(1, Mth, "Jan")
                
            Case 2
                z = InStr(1, Mth, "Feb")
            
            Case 3
                z = InStr(1, Mth, "Mar")
            
            Case 4
                z = InStr(1, Mth, "Apr")
            
            Case 5
                z = InStr(1, Mth, "May")
            
            Case 6
                z = InStr(1, Mth, "Jun")
            
            Case 7
                z = InStr(1, Mth, "Jul")
            
            Case 8
                z = InStr(1, Mth, "Aug")
            
            Case 9
                z = InStr(1, Mth, "Sep")
            
            Case 10
                z = InStr(1, Mth, "Oct")
            
            Case 11
                z = InStr(1, Mth, "Nov")
            Case 12
                z = InStr(1, Mth, "Dec")
            Case 13
                z = 1 ' Set to 1 as there has been no match therefore want to end the loop here at unlucky 13
        End Select

        If z > 0 Then  ' If z > 0 then set x = 1 so the loop ends
            x = 1
        Else
            i = i + 1   ' Otherwise increase i by 1 to continue loop at next case
        End If

    Loop Until x = 1    ' If x <>1 then loop otherwise end

    MthPlaceInt = i     ' Make the function value the same as the Case that ended the loop - this will be the month used in building the date

End Function

 

[SSHA_SP]

Thank you I will try that tomorrow I never imagined it would be so long winded.

I have typed up my original question, hopefully it makes more sense.

Thanks

 

[SSHA_SP]

Kiwiman you have nailed it!! Thank you ever so much and to all those who offerred advice.