By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,903 Members | 2,097 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,903 IT Pros & Developers. It's quick & easy.

Select multiple criteria within a field - pass to report filter

P: n/a
I have a field called ingredients where multiple ingredients can be
contained in one record (client request). Now when a report is run I
need to prompt a user to determine which records to display which could
be all of the below ingredients or just 1.

Order ID Ingredients
1 PEP, SAU, MSH, GP
2 SAU
3 PEP

If user only wants report to display orders for PEP and SAU, what type
of prompt/filter must I create ?

Thanks in advance ?

Aug 31 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
This is classic denormalized design. Ingredients should be listed
individually in a child table. then you can use OR to filter for the
exact set of ingredients you need.
If this were my database, I'd create a table of (OrderID, Ingredient)
and then use Split to return an array of ingredients, which you an loop
through and append to your OrderIngredients table.

Then you could populate a listbox with ingredients and pass the
selected items to the open event of a report to be used as a filter.
Create a function to return the list...

Aug 31 '06 #2

P: n/a

What do you mean by Split ?
pi********@hotmail.com wrote:
This is classic denormalized design. Ingredients should be listed
individually in a child table. then you can use OR to filter for the
exact set of ingredients you need.
If this were my database, I'd create a table of (OrderID, Ingredient)
and then use Split to return an array of ingredients, which you an loop
through and append to your OrderIngredients table.

Then you could populate a listbox with ingredients and pass the
selected items to the open event of a report to be used as a filter.
Create a function to return the list...
Aug 31 '06 #3

P: n/a

po*******@gmail.com wrote:
What do you mean by Split ?
Your design is a little off, although I guess you could use InStr() to
figure out if a value was in your list.

Split is a VB function. Open up a code module, go to help (F1) and
read all about it. It will basically take a delimited string and turn
it into an array.
e.g., varNumbers=Split("One, Two, Three",",") would return:
varNumbers(0)=1, varNumbers(1)=2, varNumbers(2)=3
so then I could just iterate through the subscripts and write
them to a table.

IF your list is comma-delimited, you should be able to do something
like

Sub NormalizeIngredientList(byval strIngredientList as string)
dim varIngredients as Variant
'--will return an array of ingredients
varIngredients=Split(strIngredientList,",")

'---loop through the ingredients that are now in the array,
varIngredients
'---lbound() returns the lowest subscript in the array, and
ubound() returns the highest.
for intCounter=lbound(varIngredients) to ubound(varIngredients)
'---write the values to a child table
strSQL="INSERT INTO RecipeChild(RecipeID, IngredientID)
VALUES (" & Me.Controls("txtRecipeID"),varIngredients(intCount er) & ")"
DBEngine(0)(0).Execute strSQL, dbFailOnError
next intCounter

End Sub

Aug 31 '06 #4

P: n/a
On 31 Aug 2006 13:59:18 -0700, po*******@gmail.com wrote:
What do you mean by Split ?

pi********@hotmail.com wrote:
>This is classic denormalized design. Ingredients should be listed
individually in a child table. then you can use OR to filter for the
exact set of ingredients you need.
If this were my database, I'd create a table of (OrderID, Ingredient)
and then use Split to return an array of ingredients, which you an loop
through and append to your OrderIngredients table.

Then you could populate a listbox with ingredients and pass the
selected items to the open event of a report to be used as a filter.
Create a function to return the list...
If each field in each record held just one item of data you wouldn't
need to be asking how to do this. :-(

See VBA help on the Split function.
It takes data in one record field, such as
book,pencil,eraser
and separates it into 3 different parts based upon the position of the
comma.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Aug 31 '06 #5

P: n/a
Even if I normalize the data I am unsure how this helps my report
dilemma. Let me use a better example:

Normalized Data for Property Table
PropertyID Rooms
LincolnTower A
LincolnTower B
LincolnTower C
LincolnTower D
ForestEstate A
ForestEstate B

Report must look like this

Property Rooms
LincolnTower A,B,C,D
ForestEstate A,B

User must have ability to filter on Rooms when viewing the report

fredg wrote:
On 31 Aug 2006 13:59:18 -0700, po*******@gmail.com wrote:
What do you mean by Split ?

pi********@hotmail.com wrote:
This is classic denormalized design. Ingredients should be listed
individually in a child table. then you can use OR to filter for the
exact set of ingredients you need.
If this were my database, I'd create a table of (OrderID, Ingredient)
and then use Split to return an array of ingredients, which you an loop
through and append to your OrderIngredients table.

Then you could populate a listbox with ingredients and pass the
selected items to the open event of a report to be used as a filter.
Create a function to return the list...

If each field in each record held just one item of data you wouldn't
need to be asking how to do this. :-(

See VBA help on the Split function.
It takes data in one record field, such as
book,pencil,eraser
and separates it into 3 different parts based upon the position of the
comma.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Sep 1 '06 #6

P: n/a
simple. Use fConcatChild to do that.
Test it out on NWind first, because that's where it was designed to
work. Then you can work out why your first few attempts may not be
working. (I don't think I ever got that one right the first time...
maybe I'm just too ADHD to pay attention that long!)

http://www.mvps.org/access/modules/mdl0004.htm

Sep 1 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.