I think what I would do in your case then would be to:
First, on your Salesfrm, either add a hidden textbox or add another column to your CompanyCombo combo box. It might be easier to
use a hidden textbox because I'm not sure of the syntax to use that will access the second column of a combo box. If you do the
text box, you will need to set it up so it's value depends on/changes with the combo box. When CompanyCombo = (ALL), hidden text
box will = * (I think a value of * will work--if not, some other values to try will be "", NULL, C, Co, Com, Comp, Compa, etc...).
When CompanyCombo = Company1, hidden textbox will = Company1. And the hidden text box will have the same value as the combo box for
the other 2 companies as well.
Then I think that I would do a UNION query to get all records from all companies limited to your criteria into one recordset.
Something like:
SELECT [ECCN], [AccountNbr] (<--- I'm guessing at the name of your Account Number field)
FROM YourTableName
WHERE ([ECCN] = "Company1" or [ECCN] = "Company3") and ([AccountNbr] Like "*40000*" Or [AccountNbr] Like "*40001*" Or [AccountNbr]
Like "*40002*" Or [AccountNbr] Like "*40003*" Or [AccountNbr] Like "*40060*")
UNION
SELECT [ECCN], [AccountNbr]
FROM YourTableName
WHERE ([ECCN] = "Company2") and ([AccountNbr] Like "*40030*" Or [AccountNbr] Like "*40031*");
Give this UNION query a meaningful name (qryLimitedResults_Union for example).
Then I would just do a SELECT query based on this new UNION query, instead of the table, and using the hidden text box or 2nd column
of the combo box as a parameter for the criteria. Something like:
SELECT *
FROM qryLimitedResults_Union
WHERE [ECCN] Like [forms]![Salesfrm]![hidden text box or 2nd column of CompanyCombo] & "*";
I *THINK* (emphasis on THINK) that will accomplish what you are trying to do.
I don't know how much you know about UNION queries, but when you construct your UNION query, MAKE SURE each SELECT statement of your
UNION query has the same number of field and that corresponding fields in each part have the exact same name. Also, UNION queries
take a little longer to run than normal select queries, so be patient.
I hope this helps,
Conan Kelly
Angel_G said:
Sorry for not being so clear
ECCN is the correct name for the table field
They are Company1, Company2, Company3.
The choices in Salesfrm's CompanyCombo are identical with the exemption that I used a Union to add an additional option "(ALL)".
So the Dropdown shows as follows:
(ALL)
Company1
Company2
Company3
Conan Kelly said:
That didn't clear things up much.
Lets use an example. Let's say the company names are Burger King, McDonalds, and Toco Bell.
Now in your table in the [ECCN] field ([ECCN] is the field name, correct?), are the values Buger King, McDonalds, and Taco Bell
or are they Company 1, Company 2, and Company 3?
Also, are the choices in Salesfrm's CompanyCombo "Company1", "Company2", and "All Companies" or are the choices "Burger King",
"McDonalds", and "All Companies"?
You can email me directly at "ckelly at mpsaz dot com" if you want to.
Conan
Angel_G said:
There is a field that has the company name, we used [ECCN]. The field actually spells out the Comapny Name (not Numeric)
There are three companies, Company 3 uses the same accounts as Company 1. As you can see Company 2 is the exemption. But we
want to use "ALL" as an additional option. There are other accounts we need to exclude even if we select "ALL".
Many Thanks!
Angel,
I think I know of a way, but it is somewhat complicated and time consuming. I would be glad to help, if no one else can offer
advice.
I need to know a couple of things first:
Is there a field in the table that signifies which Company a record belongs to or is it purely determined by the account
number?
How many different Companies do we have here? Just the 2 mentioned her, or is ther more that you will need to query on as
well?
Hope I can be of some help,
Conan Kelly
Hi! I am trying to write a query that based on a Form's (Salesfrm) Selection Combo Box(CompanyCombo) the query will provide
data.
I am trying to use the IIF statement with out luck (not sure if there is a better way to do this) .
I need the query to lookup records IF the Selection value = "Company1" then Like "*40000*" Or Like "*40001*" Or Like "*40002*"
Or Like "*40003*" Or Like "*40060*"
But if selection value = "Company2" then Like "*40030*" Or Like "*40031*"
But if selection value = "All Companies" then Like "*40000*" Or Like "*40001*" Or Like "*40002*" Or Like "*40003*" Or Like
"*40060*" or Like "*40030*" Or Like "*40031*" (note: I can not use a wild card here because I do not want to inclue all
accounts)
Any ideas are more than welcomed.
Thank you!
