Very Simple Query Returns Wrong Rsults

[RobertIngles]

Hi everyone;

This one is totally wierd and the first time I have ever seen it happen.

I have a single table query that I have tested with only the column (Device_Category) that is acting up. Some fields have the value "Legacy Device" but most don't. I have included only that column in the query because it seems to be returning the wrong results. If I specify the query to find all records with "Leg*", or "Legacy Device" I get the correct records returned. If I simply place Not in front of the criteriea ie. Not "Leg*" or Not "Legacy Device" the query returns no records - if I use Null as the criteria, I get the correct results. The field Data Type is text and uses the default properties.

Can anyone give me a suggestion as to why this would be happening??

 

[dhookom]

Try
Not Like "leg*"
or
<> "Legacy Device"

Nulls are unknown so the records with Null values may trip you up.

Duane
Hook'D on Access
MS Access MVP

 

[RobertIngles]

Hey Duane - good to hear from you!

Not like "leg*" returned no results - should be 20,000+ records.
<> "leg*" returned all fields with "Legacy Device"
<>"Legacy*" and <>"legacy device" returned no results

I have deleted and re-inserted the feild into the table but get the same results with the query - please tell me this isn't a symptom of a corrupt database!

 

[SkipVought]

Try

(Device_Category Not Like "leg*" or Device_Category is null)
[code]
include the parentheses.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[RobertIngles]

Thanks for the suggestion Skip however both returned no records

 

[dhookom]

Is Device_Category a lookup field?
Can you provide the full SQL you are trying?

Duane
Hook'D on Access
MS Access MVP

 

[RobertIngles]

No, Device_Category isn't a lookup field

This should produce about 20,000 records in the results - returns none:

SELECT TBL_AVEPP_Phase2_Devices.Device_Category
FROM TBL_AVEPP_Phase2_Devices
WHERE ((Not (TBL_AVEPP_Phase2_Devices.Device_Category)="Legacy Device"));

This should return 3063 devices and it does:

SELECT TBL_AVEPP_Phase2_Devices.Device_Category
FROM TBL_AVEPP_Phase2_Devices
WHERE (((TBL_AVEPP_Phase2_Devices.Device_Category)="Legacy Device"));

Table Properties

General
Field Size: 255
Format:
Input Mask:
Caption:
Default Value:
Validation Rule:
Validation Text:
Required: No
Allow Zero Length: Yes
Indexed: No
Unicode Compression: Yes
IME Mode: No Control
IME Sentence Mode: None
Smart Tags:
Text Align: General

Lookup
Display Control: Text Box

 

[dhookom]

Why not do as I suggested:

SELECT TBL_AVEPP_Phase2_Devices.Device_Category
FROM TBL_AVEPP_Phase2_Devices
WHERE Device_Category <> "Legacy Device";

If you want to return records where Device_Category might also be null, use:

SELECT TBL_AVEPP_Phase2_Devices.Device_Category
FROM TBL_AVEPP_Phase2_Devices
WHERE Device_Category & ""  <> "Legacy Device";

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Thanks for the suggestion Skip however both returned no records

BOTH? I only made ONE suggestion.

Can you explain?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JoeAtWork]

I would suggest you post the SQL statement where you used Skip's suggestion, as I think it would do what you want.

You might want to read up on how SQL evaluates Nulls to understand what is happening.

 

[Gammachaser]

You have a Null problem... which is not the same as not having a problem. You need to use nz().

SELECT TBL_AVEPP_Phase2_Devices.Device_Category
FROM TBL_AVEPP_Phase2_Devices
WHERE ((Not (TBL_AVEPP_Phase2_Devices.Device_Category)="Legacy Device"));

Should be:

SELECT TBL_AVEPP_Phase2_Devices.Device_Category
FROM TBL_AVEPP_Phase2_Devices
WHERE Not nz(TBL_AVEPP_Phase2_Devices.Device_Category) ="Legacy Device";

Access is ignoring all the fields that are Null or empty instead of counting them... thus a result of 0. That is also why it works when you use Null as the value.

 

[MajP]

although there are many ways as shown to handle this, checking
Device_Category is null
is more efficient than using a NZ in the query. 90 percent of the time if you can use native sql syntax instead of a vb function in your query, it will be more efficient. Although NZ is not a very expensive function, I would be interested in the difference running this on tens of thousands of records. It may or may not be substantial.

 

[dhookom]

Gammachaser,
I never use Nz() without providing two arguments. I recommend:

nz(TBL_AVEPP_Phase2_Devices.Device_Category,"")

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

@ MajP -- True, it is more efficient to use Is Null, and in this particular case it may work, since the OP stated that there would either be nothing or "Legacy Device" in the field. However, if there were more possible results, the Is Null would only capture the empty fields, not those with 'Something Else' in the field. I was being generic, which may not be needed in this case. One benefit of Nz, however, is that it would capture anything that was not "Legacy Device" so even if there was a stray non-visible character in the field it would still be counted as not "Legacy Device".

@ dhookom -- Why not? The default is "", so you don't need it unless you want a different result.

An old friend of mine, who was a pioneer of sorts in the computer field used to say "A keystroke saved is an error avoided." I sort of took that to heart.

 

[Gammachaser]

BTW -- Out of curiosity, I tested the NZ vs Is Null on a database I have that links a table in a fairly large Oracle DB.

For just over half a million records, the first run was nearly identical at about 4.5 seconds for each. But... I suspected that there was a caching 'penalty' for the first run so on the second run, the 'Is Null' was substatially quicker -- 0.5 seconds to 4.5 seconds. But, for over half a million records, that is still not very penalizing. Scaled down to 5,000 I got what was expected, about .05 seconds, so 20,000 should take about 0.2 seconds extra.
Tolerable if you need the ability to test for non-visible characters or entries other than null that you still want to count.

 

[MajP]

@Gammachaser.

However, if there were more possible results, the Is Null would only capture the empty fields, not those with 'Something Else' in the field.

I was unclear, but I was referring back to Skip's post of 4 Jun when stating "as shown".
(Device_Category Not Like "leg*" or Device_Category is null)
That should handle non null values not like leg* and null values.

As I stated, for this query I doubted that you would see much difference. I was more speaking in general practice. In general practice you are almost always better off using native sql than functions. If however this same query uses an NZ in a subquery then the subquery cannot be optimized nor the main query. The results could be drastic

I can write vba functions as good or better than anyone, but my SQL is marginal. I have forced myself to use native SQL even when I could easily write a UDF. You see a lot of people post on this site complaining about poor query performance and their query is littered with iif, swithch, NZ, choose and other vb functions.

You may find this interesting as it pertains to NZ

http://allenbrowne.com/QueryPerfIssue.html

 

[dhookom]

I always prefer to be explicit since according to Help Nz() can "return zero, a zero-length string (" "), or another specified value when a Variant is Null".

I don't like leaving stuff to chance for Nz(). You can if you want to save a couple key-strokes.

Duane
Hook'D on Access
MS Access MVP