Access behaving weirdly, please help.

[Guest]

Good day,

Just curious if anyone could explain why all of a sudden one of my queries
the criterias aren't working anymore?

A brief background:

I have a query based on my tblEquipmentMaster. I was trying to weed out any
unit that doesn't have a license nbr. I put Is not null on the criteria line
but when I ran the query but it included everything. So then I tried is null
and that worked okay. So to investigate further I tried to search on
Equipment that started with HB and HA.
In the past I have always put on the criteria line: Like "*HB*" and like
"*HA*" and queries worked fine. Now when I do the exact same thing the query
returns nothing. I tested this on a bunch of difrerent fields with the same
results. So in pure frustration I tried it on some totally different queries
(based on different tables) and they worked fine. It seems to be happening
with this one table only. In desperation I tried creating a brand new query
and I also tried a Compact And Repair but neither of those helped.

If anyone could help shed some light on this it would be greatly appreciated.

Thanks Kindly

 

[Rick Brandt]

Good day,

Just curious if anyone could explain why all of a sudden one of my queries
the criterias aren't working anymore?

A brief background:

I have a query based on my tblEquipmentMaster. I was trying to weed out any
unit that doesn't have a license nbr. I put Is not null on the criteria line
but when I ran the query but it included everything. So then I tried is null
and that worked okay. So to investigate further I tried to search on
Equipment that started with HB and HA.
In the past I have always put on the criteria line: Like "*HB*" and like
"*HA*" and queries worked fine. Now when I do the exact same thing the query
returns nothing. I tested this on a bunch of difrerent fields with the same
results. So in pure frustration I tried it on some totally different queries
(based on different tables) and they worked fine. It seems to be happening
with this one table only. In desperation I tried creating a brand new query
and I also tried a Compact And Repair but neither of those helped.

If anyone could help shed some light on this it would be greatly appreciated.

Thanks Kindly

Did you remove the Is Null criteria? Perhaps none of the equipment that starts
with "HB" or "HA" has a null in the license number.

 

[Guest]

Hi Rick,

First off Thanks so much for taking the time to reply. Yes I did remove the
Is Null criteria. I tried one thing at a time. First I tried Like "*HA*"
only, worked fine, gave me all records with HA as part of the unit number, so
then I tried adding Is Null to the criteria line of License Nbr and it
returned the exact same thing. Yet there are some units that don't have a
license nbr.

I just went back in and created a brand new query based on the same table.
I searched on "HT" units only. Query returned 77 records. I counted the
null license nbrs and they = 13. When I ran the query with Is Null as the
criteria it came up with only 10. So I went into the fields and hit the
delete button and pressed backspace a couple of times just to make sure there
wasn't something in them, tried running the queires again with the exact same
results.

Any other suggestions?

Thanks again.

 

[Rick Brandt]

Hi Rick,

First off Thanks so much for taking the time to reply. Yes I did remove the
Is Null criteria. I tried one thing at a time. First I tried Like "*HA*"
only, worked fine, gave me all records with HA as part of the unit number, so
then I tried adding Is Null to the criteria line of License Nbr and it
returned the exact same thing. Yet there are some units that don't have a
license nbr.

I just went back in and created a brand new query based on the same table.
I searched on "HT" units only. Query returned 77 records. I counted the
null license nbrs and they = 13. When I ran the query with Is Null as the
criteria it came up with only 10. So I went into the fields and hit the
delete button and pressed backspace a couple of times just to make sure there
wasn't something in them, tried running the queires again with the exact same
results.

Any other suggestions?

You might have some license numbers that contain zero length strings which is
not the same a Null. Try a criteria for that field of...

"" OR Is Null

 

[Guest]

You are wonderful !! That did the trick. Don't know why I couldn't figure
that out myself. Been too long of a week I guess. TGIF.

Thanks again Rick.

 

[Rick Brandt]

You are wonderful !! That did the trick. Don't know why I couldn't figure
that out myself. Been too long of a week I guess. TGIF.

Thanks again Rick.

If you'd like to clean up the table you could use an update query to change all
of the zero-length-strings into Nulls and then go into design view of the table
and turn off the option to allow zero-length-strings in that field. I generally
don't allow them because of exactly the confusion you just went through.

 

[Guest]

I'll do just that (On Monday though....I confused myself to much today to try
it now Hee Hee).
Again Thank You.
Have a great weekend.