Rearranging text to Date Format in Access 1

[james33]

Dear All,
I am trying to clean up some text so that I can format a field as a Date.
The Format at the moment is Text as it is imported from another programme which only supports Text Fields.
It comes like this 1999/05/20: which has a trailing space after the colon.
I wish to rearrange it to the following 21/05/1999 without the colon and the trailing space
so that (after further tidying up of any that do not conform to either) I can format the field as a Date field.
I am a beginner with VBA so I would appreciate suggestions.
Thank you in advance
Jim.

 

[smedvid]

I think u can just use the Input mask to accomplish this. 99/99/9999;0;" " is my preference. Also, the Format clause would be dd/mm/yyyy. htwh... Steve Medvid
"IT Consultant & Web Master"

 

[snoopy75]

Will the input date always be in the format you have? If so, use this:

Function ChangeDate(str As String) As String

    Dim Month As String
    Dim Day As String
    Dim Year As String
    
    Year = Left(str, 4)
    Month = Mid(str, 6, 2)
    Day = Mid(str, 9, 2)
    
    ChangeDate = Format(Month & "/" & Day & "/" & Year, "dd/mm/yyyy")
    
End Function

--Ryan

 

[james33]

Thank you both for your input, perhaps I should have also mentioned that these dates are in a field amongst Dates which are formatted as 2000/12/05: etc (When I say formatted I mean that I have inserted them with a shortcut ctrl+D in Sidekick98 which only has Text Fields and I wish to move them to Access97 via Tab-delimited.Txt and alter the Format after I have imported them.)
The situation has arisen because I only realised that I could change the format in Late 1999 by altering the Regional properties in Windows.

What I want to do is update the Field in the Table after I have Imported it and then Reformat it (perhaps by an Update qry).

I apologise for sounding a Little unclear but I'm just feeling totally ignorant (I'm after inbibing a bottle of Port as I'm still celebrating Christmas and the New Year) could you possibly explain where I would call the Procedures from?
Happy New Year (Hic!) #-)

 

[snoopy75]

I'm glad you've been enjoying the holidays! LOL

Hopefully, you're now sober enough to clarify your previous post. Here's what I understand: You have a bunch of dates, and some of them are formatted as yyyy/mm/dd: , and you need to reformat them to dd/mm/yyyy, right?

Sounds like you need to use DAO or something to step through your imported table and reformat the offending dates. So, just in case this is actually what you're doing, here's one method to accomplish this:

Sub ChangeDates()

   Dim db As DAO.Database
   Dim rst As DAO.Recordset

   Set db = CurrentDb()
   ' Replace tblData with the name of your table
   Set rst = db.OpenRecordset("tblData", OpenDbDynaset)

   With rst
      .MoveFirst
      ' Step through all records
      Do Until .EOF
         ' Check for incorrect formatting
         ' Replace [Date] with the field your dates are in
         If Mid(![Date],5,1) = "/" Then
            .Edit
               ' Reformat date w/ previously posted function
               ![Date] = ChangeDate(![Date])
            .Update
         End If
         .MoveNext
      Loop
   End With

   Set rst = Nothing
   Set db = Nothing

End Sub

Let me know if I've misunderstood what you're doing. Good luck!

--Ryan

 

[james33]

Thanks, yes I did enjoy my holidays....
And thanks for the suggestions.
The fact is that (through my Fault)I have got a field with Dates entered in several different ways.
Some are like this: 1999/05/20:
Some are like this: 21/05/1999:
Some are like this: 2/05/98:
and some are like this: 21/5/99:
They are not at the moment formatted as anything other than Text as it would be impossible for the software to work out what goes where.
What I want to do is rearrange them all to at least conform to one particular way preferably: 1999/05/20: then I know I can reformat them as Dates.
The way you suggest would be ideal most of the time but I'm afraid I need a way of testing each string to find out where the Days and the months are.
At least the Years are always 98 99 1999 2000 or 2001
so that leaves some hope.
I appreciate the effort you have made (and I'm not in the habit of finishing off a bottle of Port too often)
Regards and thanks.
Jim

 

[snoopy75]

Hmm, that might be possible, if it weren't for the fact that any code at all (or people, for that matter) will not be able to tell if 5/2/98 is May 2nd or February 5th. If you have both formats in your field, I don't know how you're going to be able to distinguish them.

If, however, you have some kind of a pattern, you can create multiple If statements to check for each case, then format the string appropriately.

--Ryan

 

[james33]

Dear Snoopy75
The month is always in the Middle (I'm in Ireland) so there is some! crazy logic in it.
Jim

 

[snoopy75]

Well, that will give you some hope, anyway.

So there are 2 formats: dd/mm/yyyy and yyyy/mm/dd, right? All you need to do, then, is have If statements checking whether the / is in the 3rd or 5th position in your string, and rearrange it accordingly.

--Ryan