Or statement returns both values if both are true

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I have a where statement which is as follows:
WHERE (((a.PRICEVALUE)=[Forms]!![Combo2] OR
(a.PRICEVALUE) = [Forms]!![Text100])) ;

I only want it to evaluate the right side of the OR when
the left side does not match (if the left side matches I
only want it to print that match). Currently if both
sides have a match the query prints both of them instead
of just the first (left side of OR) Any help would be
appreciated.

Thanks
Alex
 
I'm confused by your question. It may be that using a WHERE clause is not
the right approach for what you want to do, but we need you to post examples
of the values in the controls and the results that should occur in each
situation so that we can identify how to help.
 
No problem...
In the Combo2 box is the name of an office lets call
it "office1". In the Text100 box is a regional code lets
call it "234".

The way I want this to work is if there is no entry
for "office1" in the table then it should search by the
regional code "234" (which will be in the table), and then
print the rest of the select statement.

If "office1" is in the table then I do not want it to
print the results of "234". I thought this should be
really simple but for some reason even if "office1" is in
the table it is still printing the results of "234".

So far it is a basic select statement
select .... from ....where..=..or..=..

Thanks for replying to my question...hopefully this should
be a simple solution I just have not been able to come up
with one yet...

p.s. I am purposely not inputing the actual statement in
here because i do not want certain items to be seen. If
you really need to see just let me know and I will try to
doctor the names up a bit and paste it in.

Alex



-----Original Message-----
I'm confused by your question. It may be that using a WHERE clause is not
the right approach for what you want to do, but we need you to post examples
of the values in the controls and the results that should occur in each
situation so that we can identify how to help.

--

Ken Snell
<MS ACCESS MVP>

I have a where statement which is as follows:
WHERE (((a.PRICEVALUE)=[Forms]!![Combo2] OR
(a.PRICEVALUE) = [Forms]!![Text100])) ;

I only want it to evaluate the right side of the OR when
the left side does not match (if the left side matches I
only want it to print that match). Currently if both
sides have a match the query prints both of them instead
of just the first (left side of OR) Any help would be
appreciated.

Thanks
Alex
Click to expand...



.
Click to expand...
 
OK - something like this (untested) should get you started in the right
direction (note, it may run slow because of the DCount function):

SELECT * FROM TableName
WHERE TableName.PRICEVALUE =
"'" & IIf(DCount("*", "TableName",
"PRICEVALUE='" & Forms!FormName!Combo2 & "'")
0, Forms!FormName!Combo2,
Click to expand...
Forms!FormName!Text100) & "'";


--

Ken Snell
<MS ACCESS MVP>


No problem...
In the Combo2 box is the name of an office lets call
it "office1". In the Text100 box is a regional code lets
call it "234".

The way I want this to work is if there is no entry
for "office1" in the table then it should search by the
regional code "234" (which will be in the table), and then
print the rest of the select statement.

If "office1" is in the table then I do not want it to
print the results of "234". I thought this should be
really simple but for some reason even if "office1" is in
the table it is still printing the results of "234".

So far it is a basic select statement
select .... from ....where..=..or..=..

Thanks for replying to my question...hopefully this should
be a simple solution I just have not been able to come up
with one yet...

p.s. I am purposely not inputing the actual statement in
here because i do not want certain items to be seen. If
you really need to see just let me know and I will try to
doctor the names up a bit and paste it in.

Alex



-----Original Message-----
I'm confused by your question. It may be that using a WHERE clause is not
the right approach for what you want to do, but we need you to post examples
of the values in the controls and the results that should occur in each
situation so that we can identify how to help.

--

Ken Snell
<MS ACCESS MVP>

I have a where statement which is as follows:
WHERE (((a.PRICEVALUE)=[Forms]!![Combo2] OR
(a.PRICEVALUE) = [Forms]!![Text100])) ;

I only want it to evaluate the right side of the OR when
the left side does not match (if the left side matches I
only want it to print that match). Currently if both
sides have a match the query prints both of them instead
of just the first (left side of OR) Any help would be
appreciated.

Thanks
Alex
Click to expand...



.
Click to expand...
Click to expand...
 
Ken,

Thanks a lot for your help i really appreciate it. It did
not give me any results but like you said you got me in
the right direction now (I only tried it once I might have
a typo). I did not know you could put those types of
functions into the ACCESS/SQL statements. This opens up a
lot of possibilities for me now. It did slow it down a
bit but not a lot.

Thanks again

Alex

-----Original Message-----
OK - something like this (untested) should get you started in the right
direction (note, it may run slow because of the DCount function):

SELECT * FROM TableName
WHERE TableName.PRICEVALUE =
"'" & IIf(DCount("*", "TableName",
"PRICEVALUE='" & Forms!FormName!Combo2 & "'")
0, Forms!FormName!Combo2,
Click to expand...
Forms!FormName!Text100) & "'";


--

Ken Snell
<MS ACCESS MVP>


No problem...
In the Combo2 box is the name of an office lets call
it "office1". In the Text100 box is a regional code lets
call it "234".

The way I want this to work is if there is no entry
for "office1" in the table then it should search by the
regional code "234" (which will be in the table), and then
print the rest of the select statement.

If "office1" is in the table then I do not want it to
print the results of "234". I thought this should be
really simple but for some reason even if "office1" is in
the table it is still printing the results of "234".

So far it is a basic select statement
select .... from ....where..=..or..=..

Thanks for replying to my question...hopefully this should
be a simple solution I just have not been able to come up
with one yet...

p.s. I am purposely not inputing the actual statement in
here because i do not want certain items to be seen. If
you really need to see just let me know and I will try to
doctor the names up a bit and paste it in.

Alex



-----Original Message-----
I'm confused by your question. It may be that using a WHERE clause is not
the right approach for what you want to do, but we need you to post examples
of the values in the controls and the results that
Click to expand...
should
occur in each
situation so that we can identify how to help.

--

Ken Snell
<MS ACCESS MVP>

I have a where statement which is as follows:
WHERE (((a.PRICEVALUE)=[Forms]!![Combo2] OR
(a.PRICEVALUE) = [Forms]!![Text100])) ;

I only want it to evaluate the right side of the OR when
the left side does not match (if the left side matches I
only want it to print that match). Currently if both
sides have a match the query prints both of them instead
of just the first (left side of OR) Any help would be
appreciated.

Thanks
Alex


.
Click to expand...
Click to expand...



.
Click to expand...
 
Back
Top