Richard
asked on
access vba code for separating lists
I have a text field which causes a lookup from a list;
eg the list is apples, oranges, pears, grapes.
What I want to do is make a report out of that list so that:
if the chosen list item is oranges, the report is able to make a list of the other items in the list.
How this would work:
The Report would say "Thank you for buying Oranges"
"you are now entitled to a discount against:
apples, pears, and grapes
So its a question of how the list can be split to display the exceptions.
eg the list is apples, oranges, pears, grapes.
What I want to do is make a report out of that list so that:
if the chosen list item is oranges, the report is able to make a list of the other items in the list.
How this would work:
The Report would say "Thank you for buying Oranges"
"you are now entitled to a discount against:
apples, pears, and grapes
So its a question of how the list can be split to display the exceptions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks LukeChung-
My cup is overflowing with help.
Will look at this too.
My cup is overflowing with help.
Will look at this too.
ASKER
LukeChung
I have looked at your article but I cant see how that would work for me if I have one list item in a field (as opposed to a table) selected and I want to group together in a Report the ones that have not been selected.
Mbizup, is there anything in what LukeChung is saying? Your additional comment would be welcome.
I have looked at your article but I cant see how that would work for me if I have one list item in a field (as opposed to a table) selected and I want to group together in a Report the ones that have not been selected.
Mbizup, is there anything in what LukeChung is saying? Your additional comment would be welcome.
If it's one item, you can simply have a query that retrieves all records except that item.
No need for an outer join in that case. I thought you had more than one selection that you needed to exclude.
No need for an outer join in that case. I thought you had more than one selection that you needed to exclude.
<<
Mbizup, is there anything in what LukeChung is saying?
>>
Absolutely.
From the way you posted your question, I assumed you wanted/needed to work with a comma delimited list. The code I posted does do that (tested).
However, using a table and a query gives you more flexibility.
Mbizup, is there anything in what LukeChung is saying?
>>
Absolutely.
From the way you posted your question, I assumed you wanted/needed to work with a comma delimited list. The code I posted does do that (tested).
However, using a table and a query gives you more flexibility.
ASKER
Thanks guys thts helpful but I dont know how to separate using a query ad the current stored value of the textfield
any more help please?
any more help please?
ASKER
OK progress.
I have a query which shows the record and this shows the right field for apples
call the field slected fruit type if you like and another filed in the query which shows fruit type 2
which is a list of all the fruit.
now I gather hopefully correctly that I have to creat an outer join and that will give me a list of those items not in the slected fruit list ie the remainder.
HOWEVER when I join them with an outer join it says that the query is ambiguous and I should create a first query but I Have no idea wat to do to run or make that.
help please
I have a query which shows the record and this shows the right field for apples
call the field slected fruit type if you like and another filed in the query which shows fruit type 2
which is a list of all the fruit.
now I gather hopefully correctly that I have to creat an outer join and that will give me a list of those items not in the slected fruit list ie the remainder.
HOWEVER when I join them with an outer join it says that the query is ambiguous and I should create a first query but I Have no idea wat to do to run or make that.
help please
ASKER
I have now worked out how to do a Not in Outer join. thanks
Wow! Different time zones! It looks like you got a lot done while the US East Coast was sleeping.
Glad you worked it out...
Glad you worked it out...
Open in new window
Call it like this:
Open in new window
or
Open in new window