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 28 2017, 11:08 AM
Post#21



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


I can post the failing SQL later.
Like works except if there is no agent selected. I need it to display all agents.
I meant to attach the simplified database so here it is. Works with Like but I need something to show all.
Attached File(s)
Attached File  Sample.zip ( 48.77K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 11:27 AM
Post#22


UA Admin
Posts: 33,017
Joined: 20-June 02
From: Newcastle, WA


I see no point in pursuing this with the Like operator IF, as I suspect, you have a LONG INTEGER in the bound column of the combo boxes. Like is for strings, not numbers, and that's just how it works In Access. The fact that you can get away with using the Like operator in some cases is really beside the point, I think.


You've also revealed another key detail in this last post. "Like works except if there is no agent selected. I need it to display all agents."

So, yes, there is a significant difference in the logic required to filter to ONE agent, versus "filtering" that allows all agents to be returned. And now that we know WHY there is a problem, we can begin to address it.

I have used this syntax in some cases. I believe are other ways to return one or all records. I like this one.

WHERE Iif( [forms]![frmTransactions]![cboAgent] Is Null, 0, tblTransactions.fkSagent) = Iif( [forms]![frmTransactions]![cboAgent] Is Null, 0, [forms]![frmTransactions]![cboAgent])

--------------------
Go to the top of the page
 
projecttoday
post Dec 28 2017, 12:18 PM
Post#23


UtterAccess VIP
Posts: 9,819
Joined: 10-February 04
From: South Charleston, WV


You said you were getting 1 and 10. Now you say LIKE works. ?

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

My company's website
Go to the top of the page
 
Knuckles
post Dec 28 2017, 12:56 PM
Post#24



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


GPG,

Nice, that worked but I have a question.

InDesign view of the query it creates a new field
IIf([forms]![frmTransactions]![cboAgent] Is Null,0,[tblTransactions].[fkSagent])
with the criteria
IIf([forms]![frmTransactions]![cboAgent] Is Null,0,[forms]![frmTransactions]![cboAgent]) Or IIf([forms]![frmTransactions]![cboAgent] Is Null,0,[forms]![frmTransactions]![cboAgent])

Why is [fkSagent] referenced in the field but not [fkBagent] as well?

The criteria no longer goes in the columns for the agents. Just trying to understand why this works.
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 01:01 PM
Post#25


UA Admin
Posts: 33,017
Joined: 20-June 02
From: Newcastle, WA


It works because if the control on the form is Null, i.e. nothing has yet been selected in it, the IiF() functions BOTH return 0 and 0 always equals 0. That means all records are returned. Note that I use 0 assuming that there is no Primary Key value 0 to confuse the issue.

However, if there IS a value in the control on the form, the IiF() functions return the field from the table and compares that to the value of the control. e.g. WHERE fkSagent = 12 or whatever is selected in the combo box

--------------------
Go to the top of the page
 
Knuckles
post Dec 28 2017, 01:06 PM
Post#26



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


So it doesn't matter if the field contains fkSagent or fkBagent?
Either one works and both are not required is what I am seeing.

Very cool. Thanks for the help, I have struggled with that for a while.
Since I only use it for lookup tables I would start my pk at 1001 so I have been able to use the like &"*" etc
Cheap work around but it works if you set the default value of the combo box to 0.

Cheers!

uarulez2.gif
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 01:18 PM
Post#27


UA Admin
Posts: 33,017
Joined: 20-June 02
From: Newcastle, WA


They'll both work the same way. If you want to filter on BOTH, use AND between them. If you want to filter on either one or the other, use OR between them.

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



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


Got it. Thanks again.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    17th July 2018 - 02:08 PM