Data type mismatch using RND()

Question

I need some assistance with an SQL query. The query is meant to return 75 random records from a table. Here is the query:

SELECT TOP 75 a.Number, a.Location, a.Manufacturer
FROM a
WHERE (((a.Location) = 'Columbus'))
ORDER BY Rnd(Int(Now()*Number)-Now()*Number);

This query works fine if I use a different city name in the WHERE clause. For example if I change the WHERE clause to WHERE (((a.Location) = 'Toledo')) the query works. However, if the city name is 'Columbus', I get data type mismatch error.

Answer 1

You need to verify the values in the Number column where a.Location = 'Columbus'... I suspect you have a non-numeric value in one of the Number columns, which is causing the type mismatch error.

You can use this to narrow it down:

SELECT *
FROM a
WHERE NOT ISNUMERIC(a.Number) and a.Location = 'Columbus'

Or better yet, exclude the location to find any possible bad values:

SELECT *
FROM a
WHERE NOT ISNUMERIC(a.Number)