RE: Generate a field value, then extract from it...

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi, Renee.

You can do this with a series of calculated fields in a query, using the
InStr function:

Pepperoni: IIf(InStr([Multi_Combo],"1"),"Pepperoni ","")
Sausage: IIf(InStr([Multi_Combo],"2"),"Sausage ", "")
etc.

Then on your report, you can concatenate the strings to print them:
[Pepperoni] & [Sausage] & ...etc.

Hope that helps.
Sprinks

"Renee" wrote:

> Using pizza for analogy..
>
> There are 10 check boxes to select pizza toppings. There are common choices
> of pizza on a drop down, one being custom.
>
> On the close event of the form, I have the following:
> Dim intX As Integer
> Dim strMulti As String
>
> intX = Me.Check_1 + Me.Check_2 + Me.Check_3 + Me.Check_4 + _
> Me.Check_5 + Me.Check_6 + Me.Check_7 + Me.Check_8 + _
> Me.Check_9 + Me.Check_10
>
> If Me.Type.Value = 13 And intX <= -2 Then ‘Type = Custom w/ 2+ toppings
> If Me.Check_1 = True Then strMulti = "1 "
> If Me.Check_2 = True Then strMulti = strMulti & "2 "
> If Me.Check_3 = True Then strMulti = strMulti & "3 "
> If Me.Check_4 = True Then strMulti = strMulti & "4 "
> If Me.Check_5 = True Then strMulti = strMulti & "5 "
> If Me.Check_6 = True Then strMulti = strMulti & "6 "
> If Me.Check_7 = True Then strMulti = strMulti & "7 "
> If Me.Check_8 = True Then strMulti = strMulti & "8 "
> If Me.Check_9 = True Then strMulti = strMulti & "9 "
> If Me.Check_10 = True Then strMulti = strMulti & "10 "
> strMulti = RTrim([strMulti]) ‘remove any trailing spaces
> Me.Multi_Combo = strMulti
> DoCmd.close
> Else
> DoCmd.close
> End If
>
> My reasoning is so I can group on the field Multi_Combo & use the Count
> function to show how many of each selected combo there were. Then in a pie
> report show the most popular combinations.
>
> How can I extract from the Multi_Combo field to make the titles/legend?
> Example:
> If 1 is in the Multi_Combo field, look at the tbl_Toppings for ingredient
> ID# 1, and select the “Topping”…
> Doing this once for each of the 10 toppings, so I might end up showing:
> “Pepperoni, Ham, and Pineapple” as one combination, “Jalapeno, Sausage, and
> Olive” as another, etc.
>
> Thanks in advance for your time!
> Renee
>
.

Relevant Pages