Function in ORDER BY

  • Thread starter Thread starter S Derouen
  • Start date Start date
S

S Derouen

I have a Query in one database that works: SELECT * from tblUGMaps ORDER BY
val([NUMBER]);
NUMBER is DataType text.

If I copy the same Query into another database(to use on another table), I
get an error message - Data Type mismatch in criteria expression. The field
in this table is also DataType text.

From what I've noticed, the problem is that there can be "blank" data in the
second table. Does anyone have a suggestion on how I can fix my Query so
that the "blank" data wouldn't cause the error?

Thanks,
S. Derouen
 
Thanks. It works great. Your quick answer is much appreciated.




Allen Browne said:
Val() cannot handle nulls, so try:
ORDER BY Val(Nz([Number], "0"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

S Derouen said:
I have a Query in one database that works: SELECT * from tblUGMaps ORDER BY
val([NUMBER]);
NUMBER is DataType text.

If I copy the same Query into another database(to use on another table), I
get an error message - Data Type mismatch in criteria expression. The field
in this table is also DataType text.

From what I've noticed, the problem is that there can be "blank" data in the
second table. Does anyone have a suggestion on how I can fix my Query so
that the "blank" data wouldn't cause the error?

Thanks,
S. Derouen
Click to expand...
Click to expand...
 
Back
Top