If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sorting a calculated numeric field
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 |
#2
|
|||
|
|||
sorting a calculated numeric field
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 -- 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. "Paul Ponzelli" wrote in message ... 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 |
#3
|
|||
|
|||
sorting a calculated numeric field
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with sorting a numeric field in an append query | Pam | Running & Setting Up Queries | 1 | October 11th, 2005 11:47 PM |
Automatically select value from a calculated field | jimmy0305 | General Discussion | 1 | June 21st, 2005 08:10 PM |
Another updating a table from a calculated field query : ) | Harry Adney via AccessMonster.com | General Discussion | 0 | April 12th, 2005 09:48 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Sorting records by a calculated field. | Setting Up & Running Reports | 6 | October 1st, 2004 10:04 PM |