Query used for sorting may contain text or numeric values

[Jim Pockmire]

A field that is used for sorting data uses an optional field that may
contain either numeric or text data (one or the other but not both). The
problem is that the numeric data is converted to text such that numeric
values are sorted 1, 10, 2 etc. I have tried the "Val" function to no avail
and the report has multiple group levels such that using VB to set the
"orderby" property will be cumbersome. I there a way to to have the field
store a numeric value when appropriate?

 

[Steve Schapel]

Jim,

A field can only have one data type.

As regards the Val() function, this is what I would use, I think it
will work. But maybe I don't fully understand your requirements.
Perhaps you could post back with some more detailed examples.

- Steve Schapel, Microsoft Access MVP

 

[Jim Pockmire]

Thanks for the response. I did a work around converting the numbers to
strings and placing sufficient leading zeros in front of them to make it
sort correctly. I don't know how efficient it is, but it works.

Jim

 

[Michel Walsh]

Hi,

Alternatively:


SELECT *
FROM myTable
ORDER BY val( MyField )


would order numerically, without modifying the field itself. Indeed, we can
order by on computed expressions... but definitively, not being pre-indexed,
that may be a little bit slower.



Hoping it may help,
Vanderghast, Access MVP