In general, use Double for fractional numbers and Long for whole numbers.
Your function accepts an argument of type Date. That's fine as long as you
are certain you will never pass in a Null when calling this function.
Examples where a Null might get passed in are:
- If the field is not Required in your table;
- If the function might be called from the new record row in a form;
- If the query contains outer joins.
If any of those could occur, you need to use:
Function LOSActives(emp As Variant) As Double
If IsDate(emp) Then
LOSActives = Round((DateDiff ("d", emp, Date)) / 365.25, 1)
End If
End Function
If you want the function to return Null when the date passed in is null,
declare it As Variant instead of As Double.
Function LOSActives(emp As Variant) As Variant
If IsDate(emp) Then
LOSActives = Round((DateDiff ("d", emp, Date)) / 365.25, 1)
Else
LOSActives = Null
End If
End Function
But this brings you full circle to the problem where they query treats it as
Text instead of numeric. You then have to trick JET into recognising it as a
number in the query, like this:
LengthService: IIf(False, 0, losactives([activeemp]![empdate]))
False will never be True, so the 0 will never be used, but just having the
alternative there is enough to give JET the intended data type.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Coll" <co*********@ho tmail.comwrote in message
news:11******** *************@y 42g2000hsy.goog legroups.com...
>I have a function that calculates a period of time e.g. 2.3 years, .5
years etc. I was using the function in a query, but since I had not
declared a data type for the function, it wasn't sorting numerically
in my query. Can you help me figure out which data type is appropriate
for my function. I tried type Long, but that doesn't seem to include
decimals. I tried Single, but I can't seem to round it to just one
decimal (I tried in the actual function, and I also tried in the
query).
Here is my function:
Function LOSActives(emp As Date) As Single
LOSActives = Round((DateDiff ("d", emp, Date)) / 365.25, 1)
End Function
And here is how I'm using it in my query...
LengthService: losactives([activeemp]![empdate])
And I am using Access 2000. Thanks.
