Help needed - converting a string to a date.

  • Thread starter Thread starter FatMan
  • Start date Start date
F

FatMan

Hi all:
I have a string that is 20090224 and I need to convert it to a date. The
string is always in the format as YYYYMMDD. How can I convert this string
to a date? I have tried Datevalue([FieldName]) but all I get is #Error
displayed in my query.

All help is greatly appreciated.

Thanks,
FatMan
 
You need to add the "/"s for this to work but:

Dim str As String
Dim dt as Date
str = "2009/02/24"
dt = CDate(str)

I just tested... doesn't work without "/"

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
Jack:
Thanks for the quick reply. How do I insert the "/" via code?

Thanks,
FatMan

dymondjack said:
You need to add the "/"s for this to work but:

Dim str As String
Dim dt as Date
str = "2009/02/24"
dt = CDate(str)

I just tested... doesn't work without "/"

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


FatMan said:
Hi all:
I have a string that is 20090224 and I need to convert it to a date. The
string is always in the format as YYYYMMDD. How can I convert this string
to a date? I have tried Datevalue([FieldName]) but all I get is #Error
displayed in my query.

All help is greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
 
That said...


Function GetDateFromString(str As String) As Date
GetDateFromString = CDate( _
mid(str, 1, 4) & "/" & _
mid(str, 5, 2) & "/" & _
mid(str, 7, 2) _
)
End Function



--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


dymondjack said:
You need to add the "/"s for this to work but:

Dim str As String
Dim dt as Date
str = "2009/02/24"
dt = CDate(str)

I just tested... doesn't work without "/"

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


FatMan said:
Hi all:
I have a string that is 20090224 and I need to convert it to a date. The
string is always in the format as YYYYMMDD. How can I convert this string
to a date? I have tried Datevalue([FieldName]) but all I get is #Error
displayed in my query.

All help is greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
 
Use the following expression

CDate(Format([StringDate],"@@@@-@@-@@"))

If it is possible that the value is null or not able to be converted you
can expand that to test to see if the value can be recognized as a date
and if so return the date, else return Null.

IIF(IsDate(Format([StringDate],"@@@@-@@-@@")),CDate(Format([StringDate],"@@@@-@@-@@")),Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top