Sorting on an expression

  • Thread starter Thread starter Mr-Re Man
  • Start date Start date
M

Mr-Re Man

In my query I hvae created a field that is not part of my table to calculate
the number of days between two dates. I have set up a sort, but it sorts the
numbers incorrectly.

I would expect 1,2,3,4,5 etc etc but get 1,11,12,2,22 etc

Is there anything I am missing to have the records display correctly?
 
The expression is returning a text string that consists of number characters.

I would suggest that you post the expression, so someone can determine why and
suggest an alternative. The other option is to try wrapping the expression in
the Val function to force a conversion of the text value to a number value.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Thank you John.

In my query as well as some other fields that all relate to the table
set-up, I created another field with the following detilas:
DaysRemaining: IIf([DateInput] Is
Null,DateDiff("d",Now(),[DateReceived]+21),"Completed")

It is this field that I am attempting to sort an order.

They are all -(minus) results displayed as the expression is working out
which invoices are past payment and by how many days.

The results I currently get are , -9, -9, -9, -85, -7 etc
As you know, I would like to see -85, -9, -9, -9, -7

I didn't know how to input a Val command in the query.

thanks again
 
Try this --
DaysRemaining: Val(IIf([DateInput] Is
Null,DateDiff("d",Now(),[DateReceived]+21),"Completed"))

--
KARL DEWEY
Build a little - Test a little


Mr-Re Man said:
Thank you John.

In my query as well as some other fields that all relate to the table
set-up, I created another field with the following detilas:
DaysRemaining: IIf([DateInput] Is
Null,DateDiff("d",Now(),[DateReceived]+21),"Completed")

It is this field that I am attempting to sort an order.

They are all -(minus) results displayed as the expression is working out
which invoices are past payment and by how many days.

The results I currently get are , -9, -9, -9, -85, -7 etc
As you know, I would like to see -85, -9, -9, -9, -7

I didn't know how to input a Val command in the query.

thanks again

John Spencer said:
The expression is returning a text string that consists of number characters.

I would suggest that you post the expression, so someone can determine why and
suggest an alternative. The other option is to try wrapping the expression in
the Val function to force a conversion of the text value to a number value.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Your expression will always return a string since you have told it to
sometimes return a string.

You might try the following expression in place of yours.
IIf([DateInput] Is Null, DateDiff("d",Now(),[DateReceived]+21), Null)

That will return a number or null. IF you want to get rid of the negative
values then flip, NOW() and DateReceived in the DateDiff function.

You can apply Val to your expression by doing this.
Val(IIf([DateInput] Is Null, DateDiff("d",Now(),[DateReceived]+21),"Completed"))

That will return 0 for "completed"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Mr-Re Man said:
Thank you John.

In my query as well as some other fields that all relate to the table
set-up, I created another field with the following detilas:
DaysRemaining: IIf([DateInput] Is
Null,DateDiff("d",Now(),[DateReceived]+21),"Completed")

It is this field that I am attempting to sort an order.

They are all -(minus) results displayed as the expression is working out
which invoices are past payment and by how many days.

The results I currently get are , -9, -9, -9, -85, -7 etc
As you know, I would like to see -85, -9, -9, -9, -7

I didn't know how to input a Val command in the query.

thanks again

John Spencer said:
The expression is returning a text string that consists of number characters.

I would suggest that you post the expression, so someone can determine why and
suggest an alternative. The other option is to try wrapping the expression in
the Val function to force a conversion of the text value to a number value.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Back
Top