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

LVL 1
driversAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
drivers,

How are you using it?
I create a table with 2 fields, one of them datetime.
Table Test1, dt date/time, id autonumber

Insert 2 records, one that is null, and one that is a valid date

I put this function in a module
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
End Function

Open in new window

I create a query against the table:
SELECT *, ConvertDateToNumeric(dt) from Test1

Open in new window

And it all runs as expected.

What problem are you getting?
0
 
cyberkiwiCommented:
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
0
 
driversAuthor Commented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cyberkiwiCommented:
If you want nulls to be returnable, then the function must return Variant, not Long
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
driversAuthor Commented:
Thanks mx

getting "subscript out of range" error

0
 
driversAuthor Commented:
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
0
 
driversAuthor Commented:
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

0
 
cyberkiwiCommented:
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
0
 
driversAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
cyberkiwiCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
Gustav BrockCIOCommented:
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

0
 
driversAuthor Commented:
Thanks everyone but cyberkiwi's solution worked for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.