DateDiff returns CLng but cannot order the output?

[stumped]

Hi All, I'm working with a datset that contans dates all of which have been input as strings. Because some values are either not present, zero lengtrh strings and so on I have written a couple of simple functions to help me to analyse the dataset.



The output of one function is the date difference (this is calculated within the function using datediff). My understanding is that the output of datediff is a CLng which I thought was a number (which is what I need it to be for my analysis). The query itself filters the output of the function field for values >=28 which seems to be working ok, but I then want to order the output and it is behaving as though it is a string with 99 at the top and values such as 238 well doen the list with 2's.

Can anyone help with what is going on and what I need to do please?

Thanks.

[RuralGuy]

How are you trying to "order" the output? Maybe if you posted the SQL for your query.

[stumped]

As requested.
SELECT dbo_CallLog.CallID, dbo_CallLog.CallType, dbo_CallLog.CallStatus, dbo_CallLog.Priority, dbo_CallLog.ClosedDate, dbo_CallLog.RecvdDate, DDateDiff([RecvdDate],[ClosedDate]) AS Expr1
FROM dbo_CallLog
WHERE (((dbo_CallLog.Priority)="1" Or (dbo_CallLog.Priority)="2" Or (dbo_CallLog.Priority)="3") AND ((DDateDiff([RecvdDate],[ClosedDate]))>=28))
ORDER BY DDateDiff([RecvdDate],[ClosedDate]) DESC;

[stumped]

And the function .....
Public Function DDateDiff(rd, cd)
Dim RecDate, ClosDate As Date
If Len(cd) = 0 Then cd = "2011-10-07"
If IsEmpty(cd) Then cd = "2011-10-07"
If IsNull(cd) Then cd = "2011-10-07"
RecDate = CDate(rd)
ClosDate = CDate(cd)
DDateDiff = CLng(DateDiff("d", RecDate, ClosDate))
End Function

[RuralGuy]

I'll start here, though I don't think it will make a difference:
This line in VBA: Dim RecDate, ClosDate As Date
...is the same as:
Dim RecDate As Variant, ClosDate As Date
It would need to be:
Dim RecDate As Date, ClosDate As Date
...to create two Date variables.

[RuralGuy]

You can also change this:
If Len(cd) = 0 Then cd = "2011-10-07"
If IsEmpty(cd) Then cd = "2011-10-07"
If IsNull(cd) Then cd = "2011-10-07"
...to...
If Len(cd & "") = 0 Then cd = "2011-10-07"
...which should catch all three conditions. NOTE: I believe only a Variant datatype can use IsEmpty()

[RuralGuy]

You might also make this change:
Public Function DDateDiff(rd, cd) As Long

[stumped]

Ok, thank you for the education, I'll let you know.

[stumped]

Hi, I tried what you suggested in stages just to see, I do appreciate that some of it was good practice rather than the fix and thanks for that. Anyway what resolved it was defining the function as Long ie Public Function DDateDiff(rd, cd) As Long

I though the return would be Long by default!

Thanks again.

[RuralGuy]

Excellent and you're welcome. I marked this thread as Solved for you.