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
 
Val() cannot handle nulls, so try:
ORDER BY Val(Nz([Number], "0"))
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert datatype using query 7
Data Mismatch in Excel 2010 4
query syntax error 1
Number to Number "Data Type Mismatch in Expression" in Join 1
Joining Access table to SQL table with "Float" datatype 2
Using a combo box as a criteria for a query 1
Data Type Mismatch in criteria expression 3
Make table query 1

Back
Top