multiple "OR" criteria Problem (1 Viewer)

smercer

Registered User.
Local time
Tomorrow, 02:01
Joined
Jun 14, 2004
Messages
442
Hi all

I have a query that needs to have multiple OR criteria (See attachment to see what I mean), but I am having problems with it.

It will work if I only had one criteria in, but when I have nearly all fields having a OR criteria then it won’t work

None of them have any criteria in the criteria row, just the OR row

What have I done wrong?
 

Attachments

  • OR_Query problem.gif
    OR_Query problem.gif
    33.6 KB · Views: 219

WayneRyan

AWF VIP
Local time
Today, 15:01
Joined
Nov 19, 2002
Messages
7,122
smercer,

The "OR" clause is meant to be applied vertically.
You want the first column to be in the criteria slot.
The second column will drop down a line ... and so on.

It will look like a staircase.

You currently have "No Criteria" OR (A And B And C And D ...) which
does not make sense.

Below is a query with the make, size and color of cars.

The query will display all; large, red Fords OR all small, blue chevies,
OR all large, green Buicks.

Code:
Column:   Make   Size   Color
          =====  =====  =====
Criteria  Ford   Large  Red      <-- Ford, Large, Red are "ANDs"
Or        Chevy  Small  Blue
          Buick  Large  Green    <-- the rows are "ORs"


Wayne
 

smercer

Registered User.
Local time
Tomorrow, 02:01
Joined
Jun 14, 2004
Messages
442
Hi Wayne

You took me down memory lane. I forgot about using OR like that, but for some reason it is not working still. (See attachment), In the attachment shows the query in design view, and a form with the fields that the query is referencing, one of which is filled in (title) and a subform that has the query as the source.

This is very weird. Thanks for helping Wayne :)
 

Attachments

  • OR_Query problem with results.gif
    OR_Query problem with results.gif
    74.1 KB · Views: 218
Last edited:

smercer

Registered User.
Local time
Tomorrow, 02:01
Joined
Jun 14, 2004
Messages
442
WayneRyan said:
s,

No attachment!

Wayne

Sorry, you were too quick for me. I had forgoten to add it at the time but now I have edited the post.

Thanks for helping
 

WayneRyan

AWF VIP
Local time
Today, 15:01
Joined
Nov 19, 2002
Messages
7,122
s,

Quantity shows #Error, that means that it sees it in the query, but it has
an invalid value.

You don't show quantity in the query.

You show the OR'd criteria, but can't see quantity.

How is it derived? Does it look OK if you just run the query (with form open)?
Does the form alter it?

Wayne
 

smercer

Registered User.
Local time
Tomorrow, 02:01
Joined
Jun 14, 2004
Messages
442
The #Error is refering to a calculation in the subdatasheet. when you click on the plus sign the #Error for that record goes away and is replaced by a number. trouble is when I select all records and click on the plus sign all records have the same quantity

I am considering storing the calculation if I can't get that to work

**Edited**
Forgot to mention: The query itself shows exactly the same thing as the form, so the record source is right
**End edit**
 
Last edited:

WayneRyan

AWF VIP
Local time
Today, 15:01
Joined
Nov 19, 2002
Messages
7,122
s,

Calculate the quantity in the query. It's easier for continuous forms.

Wayne
 

smercer

Registered User.
Local time
Tomorrow, 02:01
Joined
Jun 14, 2004
Messages
442
Thanks Wayne (genus)

I must have done that the wrong way by counting the ISBN_Number in the tbl_Each_Book table in query once before and decided it would not work.

Any way I have got another screenshot of what it looks like now. It shows all records instead of just the one (Same as before)

Scott
 

Attachments

  • OR_Query problem with results Qty fixed.gif
    OR_Query problem with results Qty fixed.gif
    67.9 KB · Views: 207
Last edited:

smercer

Registered User.
Local time
Tomorrow, 02:01
Joined
Jun 14, 2004
Messages
442
Hi Wayne

Could this be corrupted? How would I know? How would I fix it?

Scott
 

smercer

Registered User.
Local time
Tomorrow, 02:01
Joined
Jun 14, 2004
Messages
442
Hi Wayne

I got it fixed. The query was including blank criteria that I had in other fields. I fixed it by having VBA insert

"--Please enter a value--"

into all criteria fields
 

Users who are viewing this thread

Top Bottom