Help With Query Please

M

mtonkovich

Dear NG,

My query is letting 6 records slip through the cracks that should be
filtered. What I'm trying to accomplish with the query below is the
following:

I want the records if the following conditions are met:

InvalidKillCheck is 1 and any of the following are also met:

1) [OLDTEST]![county] Is Null
2) [OLDTEST]![CheckCounty] Is Null
3) [County]![CountyID]) and [OLDTEST]![CheckCounty] are equal

The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Can anyone see why this may be happening? Your help is geniunely
appreciated.

Mike

UPDATE County INNER JOIN OLDTEST ON County.COUNTY = OLDTEST.county SET
OLDTEST.InvalidKillCheck = "0"
WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null)) OR (((OLDTEST.InvalidKillCheck)="1") AND
(([OLDTEST]![CheckCounty]) Is Null)) OR
((([OLDTEST]![InvalidKillCheck])="1") AND
(([County]![CountyID])=[OLDTEST]![CheckCounty]));
 
J

Jeff Boyce

Not sure what you mean by "missing values" ... is that the same as Null, or
could it be a zero-length string ("")?

I'm also wondering if there might be missing parentheses in your WHERE
clause? That is, you may be getting different AND and OR restrictions than
you had intended by not grouping them within parentheses.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

mtonkovich

Hi Jeff. Thanks so much for getting back to me so quickly. First, how
do I test for zero-length strings? That may very well be the issue.
Second, I'm with you on the PARENs. That is critical. I didn't write
the query if that puts your mind to ease. I used the point and shoot
method!

Jeff said:
Not sure what you mean by "missing values" ... is that the same as Null, or
could it be a zero-length string ("")?

I'm also wondering if there might be missing parentheses in your WHERE
clause? That is, you may be getting different AND and OR restrictions than
you had intended by not grouping them within parentheses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear NG,

My query is letting 6 records slip through the cracks that should be
filtered. What I'm trying to accomplish with the query below is the
following:

I want the records if the following conditions are met:

InvalidKillCheck is 1 and any of the following are also met:

1) [OLDTEST]![county] Is Null
2) [OLDTEST]![CheckCounty] Is Null
3) [County]![CountyID]) and [OLDTEST]![CheckCounty] are equal

The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Can anyone see why this may be happening? Your help is geniunely
appreciated.

Mike

UPDATE County INNER JOIN OLDTEST ON County.COUNTY = OLDTEST.county SET
OLDTEST.InvalidKillCheck = "0"
WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null)) OR (((OLDTEST.InvalidKillCheck)="1") AND
(([OLDTEST]![CheckCounty]) Is Null)) OR
((([OLDTEST]![InvalidKillCheck])="1") AND
(([County]![CountyID])=[OLDTEST]![CheckCounty]));
Click to expand...
Click to expand...
 
M

mtonkovich

Jeff - Thanks so much for the speedy reply. Two things. First, I
modified the query to address zero-length strings (I think by adding
the following):

WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null Or ([OLDTEST]![county])="")

That did nothing for me.

Second, I'm with you on the importance of PARENs. However, I don't
think this is the problem, because I didn't write the SQL, I used the
point and click method. In case it matters, county is a character
variable and CheckCounty is numeric.

One final note, there was an error in my initial email.

The following:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Should have read:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![county] and InvalidKillCheck = 1.



Jeff said:
Not sure what you mean by "missing values" ... is that the same as Null, or
could it be a zero-length string ("")?

I'm also wondering if there might be missing parentheses in your WHERE
clause? That is, you may be getting different AND and OR restrictions than
you had intended by not grouping them within parentheses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear NG,

My query is letting 6 records slip through the cracks that should be
filtered. What I'm trying to accomplish with the query below is the
following:

I want the records if the following conditions are met:

InvalidKillCheck is 1 and any of the following are also met:

1) [OLDTEST]![county] Is Null
2) [OLDTEST]![CheckCounty] Is Null
3) [County]![CountyID]) and [OLDTEST]![CheckCounty] are equal

The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Can anyone see why this may be happening? Your help is geniunely
appreciated.

Mike

UPDATE County INNER JOIN OLDTEST ON County.COUNTY = OLDTEST.county SET
OLDTEST.InvalidKillCheck = "0"
WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null)) OR (((OLDTEST.InvalidKillCheck)="1") AND
(([OLDTEST]![CheckCounty]) Is Null)) OR
((([OLDTEST]![InvalidKillCheck])="1") AND
(([County]![CountyID])=[OLDTEST]![CheckCounty]));
Click to expand...
Click to expand...
 
J

Jeff Boyce

I'm still not clear what you mean by "missing value". You can test for
zero-length string AND Null all together using something like:

Nz([YourField],"")=""

Is there a query you could write that would return ONLY the ones you are
having a problem with? If so, you could see how those are selected, so you
don't do it with your original query (or use a "not-in" query construction
if you absolutely cannot restrict your query).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff - Thanks so much for the speedy reply. Two things. First, I
modified the query to address zero-length strings (I think by adding
the following):

WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null Or ([OLDTEST]![county])="")

That did nothing for me.

Second, I'm with you on the importance of PARENs. However, I don't
think this is the problem, because I didn't write the SQL, I used the
point and click method. In case it matters, county is a character
variable and CheckCounty is numeric.

One final note, there was an error in my initial email.

The following:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Should have read:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![county] and InvalidKillCheck = 1.



Jeff said:
Not sure what you mean by "missing values" ... is that the same as Null,
or
could it be a zero-length string ("")?

I'm also wondering if there might be missing parentheses in your WHERE
clause? That is, you may be getting different AND and OR restrictions
than
you had intended by not grouping them within parentheses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear NG,

My query is letting 6 records slip through the cracks that should be
filtered. What I'm trying to accomplish with the query below is the
following:

I want the records if the following conditions are met:

InvalidKillCheck is 1 and any of the following are also met:

1) [OLDTEST]![county] Is Null
2) [OLDTEST]![CheckCounty] Is Null
3) [County]![CountyID]) and [OLDTEST]![CheckCounty] are equal

The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Can anyone see why this may be happening? Your help is geniunely
appreciated.

Mike

UPDATE County INNER JOIN OLDTEST ON County.COUNTY = OLDTEST.county SET
OLDTEST.InvalidKillCheck = "0"
WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null)) OR (((OLDTEST.InvalidKillCheck)="1") AND
(([OLDTEST]![CheckCounty]) Is Null)) OR
((([OLDTEST]![InvalidKillCheck])="1") AND
(([County]![CountyID])=[OLDTEST]![CheckCounty]));
Click to expand...
Click to expand...
Click to expand...
 
M

mtonkovich

Jeff - I'm sorry for the confusion. The table in question was
"imported." The data came from scanned forms. Therefore, some of the
records have no values (are missing) for some of the variables. That
is what I mean by missing. As for a query that will select the 6
records that I'm having trouble with - the following simple query does
that.

SELECT...FROM..
WHERE (((OLDTEST.county) Is Null) AND
((OLDTEST.InvalidKillCheck)="1"));

If you'll take a look at my original query below, you'll see that these
same conditions are spelled out in this query. So, if these 6 records
meet the criteria, why are they missed when the first query is run?

Mike

Jeff said:
I'm still not clear what you mean by "missing value". You can test for
zero-length string AND Null all together using something like:

Nz([YourField],"")=""

Is there a query you could write that would return ONLY the ones you are
having a problem with? If so, you could see how those are selected, so you
don't do it with your original query (or use a "not-in" query construction
if you absolutely cannot restrict your query).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff - Thanks so much for the speedy reply. Two things. First, I
modified the query to address zero-length strings (I think by adding
the following):

WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null Or ([OLDTEST]![county])="")

That did nothing for me.

Second, I'm with you on the importance of PARENs. However, I don't
think this is the problem, because I didn't write the SQL, I used the
point and click method. In case it matters, county is a character
variable and CheckCounty is numeric.

One final note, there was an error in my initial email.

The following:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Should have read:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![county] and InvalidKillCheck = 1.



Jeff said:
Not sure what you mean by "missing values" ... is that the same as Null,
or
could it be a zero-length string ("")?

I'm also wondering if there might be missing parentheses in your WHERE
clause? That is, you may be getting different AND and OR restrictions
than
you had intended by not grouping them within parentheses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear NG,

My query is letting 6 records slip through the cracks that should be
filtered. What I'm trying to accomplish with the query below is the
following:

I want the records if the following conditions are met:

InvalidKillCheck is 1 and any of the following are also met:

1) [OLDTEST]![county] Is Null
2) [OLDTEST]![CheckCounty] Is Null
3) [County]![CountyID]) and [OLDTEST]![CheckCounty] are equal

The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Can anyone see why this may be happening? Your help is geniunely
appreciated.

Mike

UPDATE County INNER JOIN OLDTEST ON County.COUNTY = OLDTEST.county SET
OLDTEST.InvalidKillCheck = "0"
WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null)) OR (((OLDTEST.InvalidKillCheck)="1") AND
(([OLDTEST]![CheckCounty]) Is Null)) OR
((([OLDTEST]![InvalidKillCheck])="1") AND
(([County]![CountyID])=[OLDTEST]![CheckCounty]));
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

I'll guess that it has to do with where the parentheses are, since there are
multiple selection criteria.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - I'm sorry for the confusion. The table in question was
"imported." The data came from scanned forms. Therefore, some of the
records have no values (are missing) for some of the variables. That
is what I mean by missing. As for a query that will select the 6
records that I'm having trouble with - the following simple query does
that.

SELECT...FROM..
WHERE (((OLDTEST.county) Is Null) AND
((OLDTEST.InvalidKillCheck)="1"));

If you'll take a look at my original query below, you'll see that these
same conditions are spelled out in this query. So, if these 6 records
meet the criteria, why are they missed when the first query is run?

Mike

Jeff said:
I'm still not clear what you mean by "missing value". You can test for
zero-length string AND Null all together using something like:

Nz([YourField],"")=""

Is there a query you could write that would return ONLY the ones you are
having a problem with? If so, you could see how those are selected, so
you
don't do it with your original query (or use a "not-in" query
construction
if you absolutely cannot restrict your query).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff - Thanks so much for the speedy reply. Two things. First, I
modified the query to address zero-length strings (I think by adding
the following):

WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null Or ([OLDTEST]![county])="")

That did nothing for me.

Second, I'm with you on the importance of PARENs. However, I don't
think this is the problem, because I didn't write the SQL, I used the
point and click method. In case it matters, county is a character
variable and CheckCounty is numeric.

One final note, there was an error in my initial email.

The following:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Should have read:
The 6 records that the Query it is missing have missing values for
[OLDTEST]![county] and InvalidKillCheck = 1.



Jeff Boyce wrote:
Not sure what you mean by "missing values" ... is that the same as
Null,
or
could it be a zero-length string ("")?

I'm also wondering if there might be missing parentheses in your WHERE
clause? That is, you may be getting different AND and OR restrictions
than
you had intended by not grouping them within parentheses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear NG,

My query is letting 6 records slip through the cracks that should be
filtered. What I'm trying to accomplish with the query below is the
following:

I want the records if the following conditions are met:

InvalidKillCheck is 1 and any of the following are also met:

1) [OLDTEST]![county] Is Null
2) [OLDTEST]![CheckCounty] Is Null
3) [County]![CountyID]) and [OLDTEST]![CheckCounty] are equal

The 6 records that the Query it is missing have missing values for
[OLDTEST]![CheckCounty] and InvalidKillCheck = 1.

Can anyone see why this may be happening? Your help is geniunely
appreciated.

Mike

UPDATE County INNER JOIN OLDTEST ON County.COUNTY = OLDTEST.county
SET
OLDTEST.InvalidKillCheck = "0"
WHERE (((OLDTEST.InvalidKillCheck)="1") AND (([OLDTEST]![county]) Is
Null)) OR (((OLDTEST.InvalidKillCheck)="1") AND
(([OLDTEST]![CheckCounty]) Is Null)) OR
((([OLDTEST]![InvalidKillCheck])="1") AND
(([County]![CountyID])=[OLDTEST]![CheckCounty]));
Click to expand...
Click to expand...
Click to expand...
 

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

Join Yields Unexplainable Results 1
update, lookups, and data type 2
Query Help Please 3
Using a field name as variable in a query 1
Return "yes" or "no" if value in one field matches any value in ot 7
using field name as value in query 2
discrepancy between SQL and Design view 5
Appending null data and moving! 4

Top