How do I convert a string for eg 011206 to 01/12/06 in Access?

[Guest]

 

[Tom Lake]

If you don't actually need it as a date field, you can do this:

Left([MyString], 2) & "/" & Mid([MyString], 3, 2) & "/" & Right([MyString],
2)

where MyString is the variable that contains the string to be converted.

If you want to actually convert it to a date rather than just a string that
looks like a date, you can do this:

DateSerial(Right([MyString], 2), Left([MyString], 2), Mid([MyString], 3, 2))

Tom Lake

 

[Douglas J. Steele]

It's even easier than that.

If you don't need it as a date field, you can use Format(MyString,
"00/00/00")

If you want to convert it to a date, you can use CDate(Format(MyString,
"00/00/00"))

WARNING: The second will fail if the user's Short Date format has been set
to other than mm/dd/yyyy through Regional Settings, so Tom's suggestion is
better, as it will work in all cases.

 

[Guest]

Doug:

I think yours is more likely to be the safer method. It depends on whether
the string is in mmddyy or ddmmyy format, i.e. whether its 12 January or 1
December. If it’s the latter Tom's method would fail here for instance as it
assumes US short date format. As, unlike date literals, the CDate function
respects the regional setting yours would work where the format of the string
matches the regional setting, whatever that is. This is more likely to be
the case than assuming a US short date format is being used.

I guess you could cover both with something like this:

Public Function GetDate(MyString As String) As Date

Dim intYear As Integer, intMonth As Integer, intDay As Integer
Dim dtmDate As Date

dtmDate = CDate(Format(MyString, "00/00/00"))
intMonth = Left(MyString, 2)

If Month(dtmDate) = intMonth Then
intYear = Right(MyString, 2)
intMonth = Left(MyString, 2)
intDay = Mid(MyString, 3, 2)
dtmDate = DateSerial(intYear, intMonth, intDay)
End If

GetDate = dtmDate

End Function

Ken Sheridan
Stafford, England

It's even easier than that.

If you don't need it as a date field, you can use Format(MyString,
"00/00/00")

If you want to convert it to a date, you can use CDate(Format(MyString,
"00/00/00"))

WARNING: The second will fail if the user's Short Date format has been set
to other than mm/dd/yyyy through Regional Settings, so Tom's suggestion is
better, as it will work in all cases.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

If you don't actually need it as a date field, you can do this:

Left([MyString], 2) & "/" & Mid([MyString], 3, 2) & "/" &
Right([MyString], 2)

where MyString is the variable that contains the string to be converted.

If you want to actually convert it to a date rather than just a string
that
looks like a date, you can do this:

DateSerial(Right([MyString], 2), Left([MyString], 2), Mid([MyString], 3,
2))

Tom Lake

 

[Douglas J. Steele]

In my opinion, it's never a good assumption to rely on the user's Regional
Settings being a specific way.

If the text field has the date in a given format, it's always safer to parse
it into its component parts, and use DateSerial to stitch it together,
rather than hoping that your user's Short Date format is consistent with the
format of the text field.

I guess the big question is "is the incoming date format fixed, or might it
change?"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug:

I think yours is more likely to be the safer method. It depends on
whether
the string is in mmddyy or ddmmyy format, i.e. whether its 12 January or 1
December. If it's the latter Tom's method would fail here for instance as
it
assumes US short date format. As, unlike date literals, the CDate
function
respects the regional setting yours would work where the format of the
string
matches the regional setting, whatever that is. This is more likely to be
the case than assuming a US short date format is being used.

I guess you could cover both with something like this:

Public Function GetDate(MyString As String) As Date

Dim intYear As Integer, intMonth As Integer, intDay As Integer
Dim dtmDate As Date

dtmDate = CDate(Format(MyString, "00/00/00"))
intMonth = Left(MyString, 2)

If Month(dtmDate) = intMonth Then
intYear = Right(MyString, 2)
intMonth = Left(MyString, 2)
intDay = Mid(MyString, 3, 2)
dtmDate = DateSerial(intYear, intMonth, intDay)
End If

GetDate = dtmDate

End Function

Ken Sheridan
Stafford, England

It's even easier than that.

If you don't need it as a date field, you can use Format(MyString,
"00/00/00")

If you want to convert it to a date, you can use CDate(Format(MyString,
"00/00/00"))

WARNING: The second will fail if the user's Short Date format has been
set
to other than mm/dd/yyyy through Regional Settings, so Tom's suggestion
is
better, as it will work in all cases.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

If you don't actually need it as a date field, you can do this:

Left([MyString], 2) & "/" & Mid([MyString], 3, 2) & "/" &
Right([MyString], 2)

where MyString is the variable that contains the string to be
converted.

If you want to actually convert it to a date rather than just a string
that
looks like a date, you can do this:

DateSerial(Right([MyString], 2), Left([MyString], 2), Mid([MyString],
3,
2))

Tom Lake

 

[Tom Lake]

Doug:

I think yours is more likely to be the safer method. It depends on
whether
the string is in mmddyy or ddmmyy format, i.e. whether its 12 January or 1
December. If it's the latter Tom's method would fail here for instance as
it
assumes US short date format. As, unlike date literals, the CDate
function
respects the regional setting yours would work where the format of the
string
matches the regional setting, whatever that is. This is more likely to be
the case than assuming a US short date format is being used.

There's some country other than the US?

Tom Lake