asked on

Convert date to numeric

Hi Experts

I have following function which is use to convert dates to numeric value such as 11-16-2010 to 20101611. However I am getting errors possibly because of nulls in the field. I need to have conversion keeping null values intact.

Function ConvertDateToNumeric(pDate As Date) As Long

Dim LYear As Integer
Dim LMth As Integer
Dim LDay As Integer

'Extract the year, month, and day values from the date parameter called pDate
LYear = (DatePart("yyyy", pDate)
LMth = (DatePart("m", pDate)
LDay = (DatePart("d", pDate)

'Format new number as a yyyymmdd value

ConvertDateToNumeric = CLng(CStr(LYear) & Right("00" & CStr(LMth), 2) & Right("00" & CStr(LDay), 2))

End Function

Thanks

cyberkiwi

You need to change pDate to Variant to accept Null

if IsNull(pDate) then
ConvertDateToNumeric = pDate
else
ConvertDateToNumeric = Year(pDate)*10000+Month(pDate)+100+Day(pDate)
end if

drivers

ASKER

Hi

I am getting "invalid use of null" error

trying this

Function ConvertDateToNumeric(pDate As Variant) As Long

If IsNull(pDate) Then
ConvertDateToNumeric = pDate
Else
ConvertDateToNumeric = Year(pDate) * 10000 + Month(pDate) + 100 + Day(pDate)
End If

End Function

thanks

cyberkiwi

If you want nulls to be returnable, then the function must return Variant, not Long

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

This is all you need to do this conversion:

Public Function ConvertDateToNumeric(pDate As Variant) As Variant
If Not IsDate(pDate) Then Exit Function
ConvertDateToNumeric = Split(pDate, "-")(2) & Split(pDate, "-")(1) & Split(pDate, "-")(0)
End Function

mx

drivers

ASKER

Thanks mx

getting "subscript out of range" error

drivers

ASKER

cyberkiwi

changed to variant and receiving "type mistmatch" error

Function ConvertDateToNumeric(pDate As Variant) As Variant

If IsNull(pDate) Then
ConvertDateToNumeric = pDate
Else
ConvertDateToNumeric = Year(pDate) * 10000 + Month(pDate) + 100 + Day(pDate)
End If

any suggestions?

End Function

thanks

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

drivers

ASKER

cyberkiwi

No actually 2 tables.

Tbl1.Datem = date/timeformat (source) value: 11/16/2010 or null
Tbl2.Datem = Long integer (destination) value: 20101116 or null

I am calling this function in an append query.

thanks

cyberkiwi

I still get no error

I created table Test2, id autonumber, intval Long
I used this update query

Insert into Test2(intval)
SELECT ConvertDateToNumeric(dt) from Test1

drivers

ASKER

hmm.. then it could be related to any other field being used in the query. I'll double check and get back to you... thanks for quick responses.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

drivers:
What format are your dates being passed in. In your Q you show 11-16-2010 dashes
Above you show 11/16/2010 - slashes

"getting "subscript out of range" error"
What was actually passed in pDate ?

mx

cyberkiwi

pDate is a column from a table, so it is indeterminate what format it will be when implicitly cast to string in Split()
On my machine, inspecting a date value gives a string equivalent of d/m/yyyy. But it could be anything, probably dependent on regional settings.
Split is a bad idea imho

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

ok ... one more time:

Public Function ConvertDateToNumeric(pDate As Variant) As Variant
If Not IsDate(pDate) Then Exit Function
pDate = Format(pDate, "yyyy-mm-dd")
ConvertDateToNumeric = Split(pDate, "-")(0) & Split(pDate, "-")(1) & Split(pDate, "-")(2)
End Function

mx

Gustav Brock

Here is how to achieve this.

/gustav

Public Function ConvertDateToNumeric(ByVal varDate As Variant) As Variant

  Dim varNum As Variant

  If IsDate(varDate) Then
    varNum = Int(Format(varDate, "yyyymmdd"))
  Else
    varNum = Null
  End If

  ConvertDateToNumeric = varNum

End Function

Open in new window

drivers

ASKER

Thanks everyone but cyberkiwi's solution worked for me.