Erratic results from query criteria

[Owl]

I am getting different results from running the same query with the same
selections. One moment it is all behaving as expected, the next it has gone
haywire. (I have done what appears to me to be EXACTLY what I have done in
another database, where it works perfectly every single time.)

In a query I have, amongst others, the following fields:

Category
SubCategory
Company

I want to be able to select any OR ALL of the relevant fields. I have the
following criteria:

Like "*"&[Type Category otherwise leave blank for ALL]&"*"
Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*"
Like "*"&[Type Company otherwise leave blank for ALL]&"*"

At one stage, I got it running perfectly with the Company one having Is Null
in OR.

I have them all in a row. I tried them stepped down from each other, but
that didn't work either. What I want is to be able to press OK or Enter on
all of them for all records, or to type in a selection from one of the fields
when I so wish.

What is puzzling me the most, is the erratic results.

I even tried running Microsoft Diagnostics. I can't remember if that
helped, but if it did, it didn't for long.

Thank you for any help.

 

[KARL DEWEY]

Try modifying like this --
Like "*"&[Type Category otherwise leave blank for ALL]&"*" OR Is Null
Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*" OR Is Null
Like "*"&[Type Company otherwise leave blank for ALL]&"*" OR Is Null

 

[Owl]

Firstly, I don't know whether you click Notify Me Of Replies in all your
posts or not so you may not have seen my very belated reply to a November
2009 question I posted about Yes/No/Null criteria in a checkbox field in a
query. I forgot to click Notifly Me Of Replies and must have been very
stressed and busy and forgotten to check if there were answers to my
question. Anyway, I saw it today and was mega-excited with your reply. It
worked and has made and will continue to make a huge and exciting difference
to my life (as it did to somebody else as well who posted there too!). Thank
you.

Secondly, than you for this reply. I DID try that, and as I mentioned in my
post, I had success with Is Null in the Company field.

However, I think she's got it (à la "My Fair Lady") - the rain in Spain -
and all that!
I have realised what the problem was. I had amalgamated 3 tables and hadn't
normalised the amalgamation, but I think the huge problem was that I had 3304
records in it already, and the amalgamation was messy and time-consuming.

I have since re-created the database from scratch and only added records
after I had normalised it, and checked that all the relationships are nice
and neat. Now the criteria are working, but I only have a few sample records
in it, but will keep checking as I add a few more (à your very useful motto
of "Build a little, test a little", which I have always loved every time I
have read it in your responses to me and to others.

Once I have the database skeleton complete and I am sure that it is working
perfectly, I will try to split the previous one in the same way that I have
done this one, but I think I will have to clean up some of the data first,
and then hopefully it will work.

Thank you very much for your time, and an especial thank you for the
Yes/No/Null solution.

Owl

Try modifying like this --
Like "*"&[Type Category otherwise leave blank for ALL]&"*" OR Is Null
Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*" OR Is Null
Like "*"&[Type Company otherwise leave blank for ALL]&"*" OR Is Null

--
Build a little, test a little.

I am getting different results from running the same query with the same
selections. One moment it is all behaving as expected, the next it has gone
haywire. (I have done what appears to me to be EXACTLY what I have done in
another database, where it works perfectly every single time.)

In a query I have, amongst others, the following fields:

Category
SubCategory
Company

I want to be able to select any OR ALL of the relevant fields. I have the
following criteria:

Like "*"&[Type Category otherwise leave blank for ALL]&"*"
Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*"
Like "*"&[Type Company otherwise leave blank for ALL]&"*"

At one stage, I got it running perfectly with the Company one having Is Null
in OR.

I have them all in a row. I tried them stepped down from each other, but
that didn't work either. What I want is to be able to press OK or Enter on
all of them for all records, or to type in a selection from one of the fields
when I so wish.

What is puzzling me the most, is the erratic results.

I even tried running Microsoft Diagnostics. I can't remember if that
helped, but if it did, it didn't for long.

Thank you for any help.

 

[John W. Vinson]

I don't know whether you click Notify Me Of Replies

Doesn't matter: that feature has been broken for months, and no notifications
are being sent.