Rearrange date string

[Soegstad]

Hi guys!
I have a string of 8 numbers that represent a date. This is extracted through ODBC so the format is bad. October 22nd 2004 is shown as 20041022.
How can i re-format this string to look like: 22.10.2004
I am mainly using the date in a form.
Thanks for your help
Mads

 

[pono1]

If the year is always 4 characters, the month two, and the day two, then something like this should get you close.

Dim sOldDate as string
Dim sNewDate as string

sOldDate = "20041022"
sNewDate = Right$(sOldDate,2)& "." & left(Right(sOldDate,4),2) & "." & left(sOldDate,4)
debug.print sNewDate

Regards,
Tim

 

[bjackson]

Try a custom function if the dates you are trying to re format are always in the same format when you receive them.If You want to use a full stop as a delimeter then this function would generate an error, you would need to change the functions return data type to a string

Function FormatMyDate(Mydate As String) As Date
'get rid of the full stop at the end
Mydate = Left(Mydate, Len(Mydate) - 1)

Dim StrHolder(4) As String

StrHolder(1) = Right(Mydate, 2)
StrHolder(2) = Left(Mydate, Len(Mydate) - 2)
StrHolder(3) = Right(StrHolder(2), 2)
StrHolder(4) = StrHolder(1) & "/" & StrHolder(3) & "/" & Left(Mydate, 4)

FormatMyDate = CDate(StrHolder(4))

' or if its a string
FormatMyDate = StrHolder(4)
End Function

 

[pono1]

Or -- it just occurred to me -- you can try a function created by Bruce.

Regards,
Tim

 

[ecniv]

Alternative

strNewDate=right$(strDate,2) & "." & Mid$(strDate,4,2) & "." & left$(strDate,4)

Vince

 

[RoyVidar]

Hei Mads!

Challenges occur if you need it as a date, and not a string.

With Norwegian settings, the date functions returns dates in the format dd.mm.yyyy, and sometimes, especially if the month and day is below 13, there's easily anomalities.

strDate="20041022"
dtMyDate=Dateserial(val(mid$(strDate,1,4)),val(mid$(strDate,5,2)),val(mid$(strDate,7,2)))

 

[Soegstad]

Thanks for all the feedback guys, It works like a charm now.
However I have stumbled onto another problem. I'd like to show several records at one time, and I therefore need a continous form.
The easiest way, as I see it, to overcome the date problem now is to do it through a query....which brings me to my question.
What's the function to rearrange the dates in a query.
Thanks again.
Mads

 

[RoyVidar]

Would be interesting to know what worked like a charm - getting the date as text or date?

Anyway, here's a date version - use something like this in a standard module (not a forms/reports module)

public function GetMyDate(byval strDate as string) as date
GetMyDate=Dateserial(val(mid$(strDate,1,4)),val(mid$(strDate,5,2)),val(mid$(strDate,7,2)))
end function

Then call it in the query like this

MyDate: GetMyDate([YourTextDateField])

For string versions, use one of the other suggestions, and alter the datatype of the function to string.