Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Convert date to numeric

Posted on 2010-11-16
Medium Priority
592 Views
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

0
Question by:drivers
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5
• 3
• +1

LVL 58

Expert Comment

ID: 34150745
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

LVL 1

Author Comment

ID: 34150947
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

LVL 58

Expert Comment

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

LVL 75

Expert Comment

ID: 34151621
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

LVL 1

Author Comment

ID: 34151742
Thanks mx

getting "subscript out of range" error

0

LVL 1

Author Comment

ID: 34151812
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

LVL 58

Accepted Solution

cyberkiwi earned 1000 total points
ID: 34151948
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
``````
I create a query against the table:
``````SELECT *, ConvertDateToNumeric(dt) from Test1
``````
And it all runs as expected.

What problem are you getting?
0

LVL 1

Author Comment

ID: 34151980
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

LVL 58

Expert Comment

ID: 34151995
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

LVL 1

Author Comment

ID: 34152012
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

LVL 75

Expert Comment

ID: 34152225
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

LVL 58

Expert Comment

ID: 34152250
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

LVL 75

Expert Comment

ID: 34152411
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

LVL 51

Expert Comment

ID: 34153441
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
``````
0

LVL 1

Author Comment

ID: 34155194
Thanks everyone but cyberkiwi's solution worked for me.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last weekâ€™s ManageEngine webinar, where attendees received a comprehensive look at the maâ€¦
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
###### Suggested Courses
Course of the Month11 days, 15 hours left to enroll