Why am I getting a Data Type mismatch in this query? (1 Viewer)

L

Linty Fresh

Registered User.

Local time

Today, 05:01

Joined

Mar 13, 2003

Messages

20

• Jul 10, 2008

• #1

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

 

R

redneckgeek

New member

Local time

Today, 05:01

Joined

Dec 28, 2007

Messages

464

• Jul 10, 2008

• #2

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

 

L

Linty Fresh

Registered User.

Local time

Today, 05:01

Joined

Mar 13, 2003

Messages

20

• Jul 10, 2008

• #3

redneckgeek said:

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

Click to expand...

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.

 

R

redneckgeek

New member

Local time

Today, 05:01

Joined

Dec 28, 2007

Messages

464

• Jul 10, 2008

• #4

Unless street_number contains a Null

 

FoFa

Registered User.

Local time

Today, 04:01

Joined

Jan 29, 2003

Messages

3,672

• Jul 10, 2008

• #5

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

 

R

redneckgeek

New member

Local time

Today, 05:01

Joined

Dec 28, 2007

Messages

464

• Jul 10, 2008

• #6

FoFa said:

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

Click to expand...

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

Registered User.

Local time

Today, 04:01

Joined

Jan 29, 2003

Messages

3,672

• Jul 10, 2008

• #7

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.

 

L

Linty Fresh

Registered User.

Local time

Today, 05:01

Joined

Mar 13, 2003

Messages

20

• Jul 29, 2008

• #8

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