Sort Not Working: Thinks Numbers are String Values

  • Thread starter Thread starter Larry R Harrison Jr
  • Start date Start date
L

Larry R Harrison Jr

I have Access 2000. I have a continuous form which is
based on a sort-of-special query. This query doesn't
screen out any data--it adds a calculated field which
performs a "days left" calculation by subtracting Date()
from the field [EndDate].

The reason I perform this calculation in the query rather
than on the continuous form is so that the user can sort
by this value on the continuous form. And it does work
fine to that end except for one thing: some records don't
have values in [EndDate] rendering this calculated field
irrelevant. To cover this, I have the following syntax as
the calculation for this "Days 'Til" field:

DaysLeft: IIf(IsNull([EndDate]),99999,[EndDate]-Date())

That is, if it's an "irrelevant" field then it assigns the
value 99999 to it.

Notice there are no quotes.

I did at first have it assigned the value "---" to it. The
problem with this--and this is the principle reason behind
this post--is doing this resulted in this field being
treated as a "string" field rather than a "numeric" field.
As a result, when sorting by this field on the continuous
form 40 will show up before, say, 395. It thinks it's the
STRING "40" rather than the numeric value of 40.

So, having it assign 99999 (without the quotes)
for "irrelevant" (or non-applicable) records prevented
this.

Thing is, though, having 99999 in there sort of looks
ridiculous and unprofessional. I really would rather
having something like "----" or N/A for the ones which
won't have a value. But again, doing this causes it to do
the wacko sort as I mentioned.

I did try doing something like Val([EndDate-Date()) as
well but it still didn't work. I also tried an IIf
calculation on the text box which basically converted
99999s to "----" but left others as is. It ***still***
sorts, say, 395 before 40.

Tips?

LRH
 
Hi,
One way would be not to display your present calaculated field, just use it
for
sorting purposes. Have another calculated field that uses IIf to display N/A
on your form.
 
Thanks very much. That's so freaking obvious, how could I
miss something so easy?

LRH

-----Original Message-----
Hi,
One way would be not to display your present calaculated field, just use it
for
sorting purposes. Have another calculated field that uses IIf to display N/A
on your form.

--
HTH
Dan Artuso, MVP

"A problem well stated is a problem half solved"


I have Access 2000. I have a continuous form which is
based on a sort-of-special query. This query doesn't
screen out any data--it adds a calculated field which
performs a "days left" calculation by subtracting Date()
from the field [EndDate].

The reason I perform this calculation in the query rather
than on the continuous form is so that the user can sort
by this value on the continuous form. And it does work
fine to that end except for one thing: some records don't
have values in [EndDate] rendering this calculated field
irrelevant. To cover this, I have the following syntax as
the calculation for this "Days 'Til" field:

DaysLeft: IIf(IsNull([EndDate]),99999,[EndDate]-Date())

That is, if it's an "irrelevant" field then it assigns the
value 99999 to it.

Notice there are no quotes.

I did at first have it assigned the value "---" to it. The
problem with this--and this is the principle reason behind
this post--is doing this resulted in this field being
treated as a "string" field rather than a "numeric" field.
As a result, when sorting by this field on the continuous
form 40 will show up before, say, 395. It thinks it's the
STRING "40" rather than the numeric value of 40.

So, having it assign 99999 (without the quotes)
for "irrelevant" (or non-applicable) records prevented
this.

Thing is, though, having 99999 in there sort of looks
ridiculous and unprofessional. I really would rather
having something like "----" or N/A for the ones which
won't have a value. But again, doing this causes it to do
the wacko sort as I mentioned.

I did try doing something like Val([EndDate-Date()) as
well but it still didn't work. I also tried an IIf
calculation on the text box which basically converted
99999s to "----" but left others as is. It ***still***
sorts, say, 395 before 40.

Tips?

LRH
Click to expand...


.
Click to expand...
 
Back
Top