Query produces incorrect results

[Guest]

Hi,

I need help with a query that produces results out side of the USA states.
The following query produces incorrect result:

SELECT Location.Country, Location.Description
FROM Location, USA
WHERE Location.State<>[USA].[Abbr];

I also tried:
SELECT Location.Country, Location.Description
FROM Location, USA,
WHERE Left(Location.State,2)<>[USA].[Abbr];

Thanks in advance,

 

[Douglas J Steele]

Odds are that Location.State contains a Null value, not a space, so you
cannot compare it to anything else using equality (or inequality) operators.

Try:

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Location.State = [USA].[Abbr]
WHERE Location.State IS NULL

or, if necessary,

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Left(Location.State,2) = [USA].[Abbr]
WHERE Location.State IS NULL

 

[Guest]

Hi,

Still not working:
I get no matches

I also tried:
WHERE Location.State Is Not Null
I get everything, even the ZZ, wich is not a part of a State

I got sample of 4 rows for Location table, State column 255 char
AL
AL
AL
ZZ

USA table, Abbr column 2 char
AL
AK
etc..
has all 50 states

Any ideas,

Thanks in advance,

Odds are that Location.State contains a Null value, not a space, so you
cannot compare it to anything else using equality (or inequality) operators.

Try:

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Location.State = [USA].[Abbr]
WHERE Location.State IS NULL

or, if necessary,

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Left(Location.State,2) = [USA].[Abbr]
WHERE Location.State IS NULL


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I need help with a query that produces results out side of the USA states.
The following query produces incorrect result:

SELECT Location.Country, Location.Description
FROM Location, USA
WHERE Location.State<>[USA].[Abbr];

I also tried:
SELECT Location.Country, Location.Description
FROM Location, USA,
WHERE Left(Location.State,2)<>[USA].[Abbr];

Thanks in advance,

 

[Douglas J Steele]

Are you sure there are rows in the table that don't have a correct value for
State?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

Still not working:
I get no matches

I also tried:
WHERE Location.State Is Not Null
I get everything, even the ZZ, wich is not a part of a State

I got sample of 4 rows for Location table, State column 255 char
AL
AL
AL
ZZ

USA table, Abbr column 2 char
AL
AK
etc..
has all 50 states

Any ideas,

Thanks in advance,

Odds are that Location.State contains a Null value, not a space, so you
cannot compare it to anything else using equality (or inequality) operators.

Try:

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Location.State = [USA].[Abbr]
WHERE Location.State IS NULL

or, if necessary,

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Left(Location.State,2) = [USA].[Abbr]
WHERE Location.State IS NULL


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I need help with a query that produces results out side of the USA states.
The following query produces incorrect result:

SELECT Location.Country, Location.Description
FROM Location, USA
WHERE Location.State<>[USA].[Abbr];

I also tried:
SELECT Location.Country, Location.Description
FROM Location, USA,
WHERE Left(Location.State,2)<>[USA].[Abbr];

Thanks in advance,

 

[Randy Harris]

Doug, pardon my jumping in here, but if he's looking for Locations outside
the US (not matched) wouldn't he want:

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Location.State = [USA].[Abbr]
WHERE USA.Abbr IS NULL

Odds are that Location.State contains a Null value, not a space, so you
cannot compare it to anything else using equality (or inequality) operators.

Try:

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Location.State = [USA].[Abbr]
WHERE Location.State IS NULL

or, if necessary,

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Left(Location.State,2) = [USA].[Abbr]
WHERE Location.State IS NULL


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I need help with a query that produces results out side of the USA states.
The following query produces incorrect result:

SELECT Location.Country, Location.Description
FROM Location, USA
WHERE Location.State<>[USA].[Abbr];

I also tried:
SELECT Location.Country, Location.Description
FROM Location, USA,
WHERE Left(Location.State,2)<>[USA].[Abbr];

Thanks in advance,

 

[Douglas J Steele]

Yes, you're absolutely right. Thanks for catching out my typo.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, pardon my jumping in here, but if he's looking for Locations outside
the US (not matched) wouldn't he want:

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Location.State = [USA].[Abbr]
WHERE USA.Abbr IS NULL

Odds are that Location.State contains a Null value, not a space, so you
cannot compare it to anything else using equality (or inequality) operators.

Try:

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Location.State = [USA].[Abbr]
WHERE Location.State IS NULL

or, if necessary,

SELECT Location.Country, Location.Description
FROM Location LEFT JOIN USA
ON Left(Location.State,2) = [USA].[Abbr]
WHERE Location.State IS NULL


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I need help with a query that produces results out side of the USA states.
The following query produces incorrect result:

SELECT Location.Country, Location.Description
FROM Location, USA
WHERE Location.State<>[USA].[Abbr];

I also tried:
SELECT Location.Country, Location.Description
FROM Location, USA,
WHERE Left(Location.State,2)<>[USA].[Abbr];

Thanks in advance,