Why am I getting a Data Type mismatch in this query?

[Linty Fresh]

Hi all

I'm running a query on street names, and I want to sort first by the name of the street and then by the number, which is in a string format. Because of the string format, I'm trying to use the val() function to sort it. My query looks like this:

SELECT Cases_interim.Case_Number, Cases_interim.Street_Number, Cases_interim.Street_Name, Cases_interim.Applicant, Cases_interim.Relief, Cases_interim.Result, Cases_interim.Comments
FROM Cases_interim
ORDER BY Cases_interim.Street_Name, Val([Street_Number]);

When I try to run this, I get a "Data type mismatch in criteria expression" error message, and it persists no matter what I try to do. Any ideas?

Many thanks

 

[redneckgeek]

Make sure that Street Number field contains only numeric data.
I'm sure Val([Street_Number]) is what's causing the error.

 

[Linty Fresh]

Make sure that Street Number field contains only numeric data.
I'm sure Val([Street_Number]) is what's causing the error.

I'm sure it's the Val([Street_Number]) that's causing the error too. But shouldn't the output of any Val() function be numeric? Is there a way to get around it?

Thanks.

 

[redneckgeek]

Unless street_number contains a Null

 

[FoFa]

If it is NULL causing problem, try this
Val(NZ([Street_Number],"0"))

 

[redneckgeek]

If it is NULL causing problem, try this
Val(NZ([Street_Number],"0"))

Depending on the country you live in, 0 might be a valid address.
I'd use something like
NZ(STREET_NUMBER,"-999999")
just to make it obvious that somebody forgot to put in a street number.

(Actually, I'd make somebody fix all the nulls, and then change the table so that it doesn't accept nulls in that field any more. But that's just me...)

 

[FoFa]

They are just using it for sorting. So a null would be first (giving ascending) and so would zero, it would not show in their query.

 

[Linty Fresh]

I just wanted to come back and say that it worked. The Null values were screwing me up. Thanks again, FoFa and redneckgeek.