sorting a calculated numeric field

[Paul Ponzelli]

I'm trying to sort a field in a query that calculates the number of days
between two date fields in the same query. However, when I try to sort a
recordset on this field, it sorts as if it were a text field instead of a
numeric field. For example, 145 comes before 15 in the ascending sort
order.

Another indication this field thinks it's a text field is that the values
are left aligned in the query datasheet.

I tried converting these values to integers by enclosing the output values
in the Cint() function, but this didn't have any effect. A simplified
version of my query expression is:

Days: IIf(Not
IsNull([Suspended]),([Suspended]-[Received]),Date()-[Received])

I've tried using Cint() and Clng(), as well as setting the format property
of the query field, but I wasn't able to get anything to work.

What can I do to get this calculated query field to think it's a number so
it will sort in numeric order?

Thanks in advance,

Paul

 

[Allen Browne]

Try:
CLng(DateDiff("d", [Received], Nz([Suspended], Date()))

JET is quite poor at interpreting the data types, so typecasting is the
solution, but we also have to give it something good to typecast. The
expression above should work unless the if the Received field can be null.
It it can be, try:
CLng(Nz(DateDiff("d", [Received], Nz([Suspended], Date()),0))

More info on typecasting fields:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Paul Ponzelli]

Thanks, Allen. The DateDiff function works great.

I also discovered another solution to my immediate problem, which is to
simply enclose the entire date expression in the Val() function. The query
then seems to understand the value is numeric, and it sorts the values like
numbers.

Paul