Return Value when two values equal

[wally2321]

I apologize if this is a simple question, but I am having a brain cramp. I am trying to create a table using the design feature of the Make Query. In my query I only want to return values if two fields are greater than zero.

How can I do that....?

Field 1: ROP
Field 2: MIN

I only want to return the records (which includes other fields) when field 1 and field 2 are greater than zero.

If both fields are equal to zero, the values should not be returned.

Any suggestions on how I can do this using the design feature of a make query?

 

[tlbroadbent]

Where Rop>0 And Min>0 Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[MichaelRed]

Where Rop>0 And Rop = Min
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Where Rop>0 And Rop = [Min]

Sorry. "Min" is a reserved (SQL) word, so it needs to be denoted as a FIELD Name. Would be better to RENAME the field in the app, as it almost certainly will be an issue (forever).


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[wally2321]

Where should the expression go? Should it be in the criteria or the field.

And how should the expression look. I tried the different combinations, but I am having difficulties.... If the field names are exactly

REORDER POINT

REC MIN ORDER QTY

What would it look like? Do you need brackets arount the fields?

Thanks for the relpies!!!

 

[tlbroadbent]

Select the fields in the query. In the criteria below ROP and MIN type >0. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[MichaelRed]

in the criteria "row" of the [ReOrder Point] "Column"

<> 0 and = [REC MIN ORDER QTY]

Terry,

The original post refers to both fields being <> 0 AND that they are equal to each other - unless (OF COURSE) i did not read it correctly.

If they are equal, then either one being non-zero satisfies the two conditions.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[wally2321]

This will not work in this situation because then it will return any values that are greater in field 1 and any values greater than field 2.... When reality, I want to eliminate the values that have zero in both fields. If one field has a value of zero, and the other field has a value of 1, then that record would be included. If both fields had zero, then that record would not be included.

Any suggests are welcomed... Thank you for the responses!!!

 

[wally2321]

MichaelRed:

<>&quot;0&quot; And =[REC MIN ORDER QTY]

I think you understand what I am getting at... but it is possible that the Reorder point column might be greater than zero, but the second column is equal to zero. How do I do it so that I eliminate records that have zero in both columns.

Wally

 

[dneufarth]

where [REORDER POINT] > 0 or [REC MIN ORDER QTY] > 0



Dave

 

[wally2321]

I put this in the criteria field of the first column, but it did not return the correct values. I think it should be an &quot;AND&quot; instead of an &quot;Or&quot; when I try it I get an error...

I had ..

where [REORDER POINT] > 0 And [REC MIN ORDER QTY] > 0

What am I missing?

 

[jflachman]

In the [REORDER POINT] column, put
>0 in the criteria field

In the [REC MIN ORDER QTY] column, put
>0 in the criteria field Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[MichaelRed]

Logic 101.

If A <> 0 And B = A Then B <> 0

So testing EITHER value for 0 AND texting that they are &quot;=&quot; assures that NEITHER is 0.

E.g. A = 4 and B = 4

If (A <> 0) OBVIOUSLY [blue]TRUE[/blue]!! Then
IF B = A OBVIOUSLY [red]TRUE[/red]!!

B CANNOT be = 0 (it = a, which is 4!!!!

So, now. It is someone elses turn to take me to the &quot;blackboard&quot;


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jflachman]

MichaelRed

Thanks..but Logic 102:
I only want to return the records (which includes other fields) when field 1 and field 2 are greater than zero.
A>0 AND B>0

If both fields are equal to zero, the values should not be returned.
A<>0 AND B<>0 which is a subset of the above.

I don't see any A=B description in the text.

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[jflachman]

Of course...his last statement might be interpreted as:
NOT ((A=0) AND (B=0))

which is, of course, the same as:
A>0 OR B>0
which is different from his first statement.
In which case the query solution would be different. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[dneufarth]

Wally,

JFlachman has it for the AND.

If you want an OR put one of the >0 criteria on the next row of the query design grid.


MichaelRed,

I'm confused on your A=B quest as well. Wally said he wanted the record if both fields were greater than zero and subsequently stated he wanted the record even if only one of the fields is greater than zero. Hence, an OR relationship. What am I missing on the logic lesson? :-(

Dave

 

[MichaelRed]

I think it must be me. Doing &quot;leaps of mis-reading&quot;, Blame it on brain flatulence?

Wnt back and read it (original post) several time to get it straight.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[wally2321]

Wow! I did not expect expect such a response, unfortunately I am not sure what the answer was suppose to be. I believe one of the responses stated the following:

&quot;If both fields are equal to zero, the values should not be returned.
A<>0 AND B<>0 which is a subset of the above.&quot;

I believe this is the right idea, but where do I put this code. I tried putting it in the first column criteria, but I keep getting an error. Should the A be equal to the first field and B be equal to the second field.

I am sorry, but I am relatively new to Access, and there have been so many different comments thrown out, I am not sure what the results were?

 

[tlbroadbent]

I'm sorry there has been so much discussion for such an easy question. The criteria can be placed in the criteria box under the column name - one criteria per column.
[tt]
Field: |[REORDER POINT]|[REC MIN ORDER QTY]
Table: | |
Sort: | |
Show: | |
Criteria: |<> 0 |<> 0[/tt]

Of course, you would also select the other fields you want in the result set. Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[jflachman]

Sorry for the confusion caused by our off-subject conversation.

If you want to make sure that your return only fields where either [REORDER POINT]<>0 AND [REC MIN ORDER QTY]<>0 then:

In the [REORDER POINT] column, put
<>0 in the criteria field

In the [REC MIN ORDER QTY] column, put
<>0 in the criteria field


If you want to make sure that your return only fields where either [REORDER POINT]<>0 OR [REC MIN ORDER QTY]<>0 then:

In the [REORDER POINT] column, put
<>0 in the criteria field

In the [REC MIN ORDER QTY] column, put
<>0 in the criteria field but one row below the <>0 in the [REORDER POINT] column.

If that doesn't work, post your e-mail address and we can talk more real time. I'll help you get this worked out.




Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt