convert number to date

[jkncrew]

I am importing data from a text file that contains dates in this format: 08012007

I am trying to convert the number to a date 08/1/2007 so that I can run date related queries.

Your help is appreciated - Thank you.

 

[RuralGuy]

Maybe this function will get you started.

Public Function DateVal(InValue As String) As Date
'-- Convert the string in the format mmddyyyy to a date value

   DateVal = DateSerial(CInt(Right(InValue, 4)), _
                        CInt(Left(InValue, 2)), _
                        CInt(Mid(InValue, 3, 2)))

End Function

 

[GrexP]

Also....

Dim dDateType as Date

sDateStrIn = "08012007"

dDateType = DateSerial(Format(sDateStrIn , "&&-&&-&&&&"))

 

[jkncrew]

Thank you kindly for your response, but I'm not that familiar with modules. I copied & pasted the function language as was given, but it's not working out. I'm guessing that I'm missing a line that references the name of the field that needs to be converted. You may have omitted that piece believing that this is something I should already know to do (forgive my ignorance). Can I bother you to make the language more complete? ... Or am I way off the mark.

Table name: [extext]
Field name: [date]

 

[RuralGuy]

Do you have a DateTime field n your table already? BTW, Date is a reserved word and should not be used as the name of a field. You can either 1) use an update query to convert the StringDate field into a dateTime field or 2) do the conversion each time you run a query against the table. If you wish to do 1) then you need to add a DateTime field to your table.