UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Using Same Criteria For 2 Fields, Access 2016    
 
   
Knuckles
post Dec 27 2017, 09:16 AM
Post#1



Posts: 804
Joined: 1-February 10
From: New Jersey


Good morning UA world,

I have not been able to figure this one out. I have a query that includes two fields for agents.
fkSellersAgent
fkBuyersAgent

I am using a combo box to select a single agent (or all agents when an agent is not selected).
When the agent is selected I need to return the records where the agent is in either field. Could be both.

I have tried setting the criteria of fkSellersAgent to Like "*"& [forms]![blah, blah, blah]![cboAgent]&"*" and the criteria of fkBuyerssAgent to Like "*"& [forms]![blah, blah, blah]![cboAgent]&"*"in the line below it as an OR.
It does not filter as it should.

Any help would be greatly appreciated.
Thanks in advance.

Knuckles
Go to the top of the page
 
JonSmith
post Dec 27 2017, 09:48 AM
Post#2



Posts: 3,227
Joined: 19-October 10



Does it work if you try adding the criteria to a single field? First ensure it works separately on each field before trying to combine them.
You can also try adding your criteria as a field in the query to see what it is evaluating as.

JS
Go to the top of the page
 
Knuckles
post Dec 27 2017, 09:51 AM
Post#3



Posts: 804
Joined: 1-February 10
From: New Jersey


Hi JS,

Yes, it works fine on either field.
I thought of trying to concatenate the 2 foreign keys and evaluating whether the agents ID was contained in the string but couldn't make that work either.
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 09:51 AM
Post#4


UA Admin
Posts: 31,632
Joined: 20-June 02
From: Newcastle, WA


What are the datatypes for the two fields, fkSellersAgent and fkBuyersAgent?

Inasmuch as we typically see AutoNumbers used for Primary and Foreign Keys, we would expect these to both be Long Integers. If that is the case, then the Like operator won't work here. The combo boxes should be bound to that Long Integer Foreign Key field and you should just use:

fkSellersAgent = [forms]![blah, blah, blah]![cboAgent] OR fkBuyerssAgent = [forms]![blah, blah, blah]![cboAgent]

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 09:55 AM
Post#5


UA Admin
Posts: 31,632
Joined: 20-June 02
From: Newcastle, WA


Hm. I see that you are using alphabetic Foreign Keys for these two fields. What are they?

--------------------
Go to the top of the page
 
Knuckles
post Dec 27 2017, 10:03 AM
Post#6



Posts: 804
Joined: 1-February 10
From: New Jersey


The primary key for the agent is an autonumber.
The foreign keys are in a transaction table.

Simplified view:
qryAgents (based on tblPeople)
pkAgentID (autoNumber)
fkRoleID
fname
lname

tblTransactions
pkTransactionID (autoNumber)
fkSellersAgentID
fkBuyersAgentID
PropertyAddress
Go to the top of the page
 
projecttoday
post Dec 27 2017, 10:26 AM
Post#7


UtterAccess VIP
Posts: 8,817
Joined: 10-February 04
From: South Charleston, WV


Are you sure you're ORing rather than ANDing? What is the query's code?
'

--------------------
Robert Crouser

Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 10:30 AM
Post#8


UA Admin
Posts: 31,632
Joined: 20-June 02
From: Newcastle, WA


I fail to understand, then, how your Like operators can work individually, unless you've bound the combo boxes to the Value field, not to the Foreign Key field.


--------------------
Go to the top of the page
 
Knuckles
post Dec 27 2017, 10:32 AM
Post#9



Posts: 804
Joined: 1-February 10
From: New Jersey


I will create a simplified version of the query later today and post the SQL.
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 10:59 AM
Post#10


UA Admin
Posts: 31,632
Joined: 20-June 02
From: Newcastle, WA


It would be really helpful to see the actual SQL that fails....

--------------------
Go to the top of the page
 
JonSmith
post Dec 27 2017, 11:18 AM
Post#11



Posts: 3,227
Joined: 19-October 10



Agreed, if the criteria works on both fields if you apply them in SQL separately an OR should logically also work without any issues. Something else must be going on here.

SQL would be great. Simplified SQL should include all the WHERE clauses.

JS
Go to the top of the page
 
projecttoday
post Dec 27 2017, 11:23 AM
Post#12


UtterAccess VIP
Posts: 8,817
Joined: 10-February 04
From: South Charleston, WV


I requested the query code to determine why it works with 1 condition but not both.

--------------------
Robert Crouser

Go to the top of the page
 
John Vinson
post Dec 27 2017, 02:05 PM
Post#13


UtterAccess VIP
Posts: 4,162
Joined: 6-January 07
From: Parma, Idaho, US


What is the Bound Column of the combo box? If it is the agent's name it will not match the numeric ID.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 02:11 PM
Post#14


UA Admin
Posts: 31,632
Joined: 20-June 02
From: Newcastle, WA


Also, I hope there are none of those nasty Lookup Fields in the TABLE involved.

--------------------
Go to the top of the page
 
Knuckles
post Dec 27 2017, 02:45 PM
Post#15



Posts: 804
Joined: 1-February 10
From: New Jersey


bound column is the ID.
No lookups in the table.
Go to the top of the page
 
projecttoday
post Dec 27 2017, 02:49 PM
Post#16


UtterAccess VIP
Posts: 8,817
Joined: 10-February 04
From: South Charleston, WV


Please post the code of the query (the SQL code.)

--------------------
Robert Crouser

Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 02:53 PM
Post#17


UA Admin
Posts: 31,632
Joined: 20-June 02
From: Newcastle, WA


Without the actual SQL, it's impossible to offer useful suggestions.

Thank you.

--------------------
Go to the top of the page
 
Knuckles
post Dec 28 2017, 10:11 AM
Post#18



Posts: 804
Joined: 1-February 10
From: New Jersey


I created a small database in order to simplify the example and present the SQL.

SELECT tblTransactions.pkTransactionID, tblTransactions.fkSagent, tblTransactions.fkBagent, tblTransactions.fkProperty
FROM tblTransactions
WHERE (((tblTransactions.fkSagent) Like "*" & [forms]![frmTransactions]![cboAgent] & "*")) OR (((tblTransactions.fkBagent) Like "*" & [forms]![frmTransactions]![cboAgent] & "*"));


In doing so I found that it worked. Since I am actually applying other filters I think I need to either reorder the filters or create a query on the filtered query.
Does that make sense?

also, noticed that my "Like" expression returns 1 and 10 when I select agent 1. Is there a better way to do that?

K


Go to the top of the page
 
projecttoday
post Dec 28 2017, 10:34 AM
Post#19


UtterAccess VIP
Posts: 8,817
Joined: 10-February 04
From: South Charleston, WV


Can you put the other filters in this code?

If you're getting 1 and 10, what about just using = instead of Like?

--------------------
Robert Crouser

Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 10:38 AM
Post#20


UA Admin
Posts: 31,632
Joined: 20-June 02
From: Newcastle, WA


It MIGHT help to see the actually FAILING SQL, not a cut-down version of it.

And, yes, I absolutely agree with Robert here. If the values are Numeric, i.e. if 1 and 10 are Long Integers, then the Like operator is inappropriate. It's used ONLY for string comparisons with wildcards.

--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    21st January 2018 - 03:42 PM