Linking Number Value from Check Boxes to Text Value in Table 1

[ccepaulb]

I have a form that has four check boxes, Tier 1, Tier 2, Tier 3 and All Outlets. Each of those checks has a value of 1,2,3 and 4 respectively.
I was hoping to assign a text value to each of these checks, but it only allows a number.
I have a table with 60,000+ records that contains a field called “Tier” and each record is assigned a Tier Value based on levels of sales.
Now what I want to do is filter out the records in a query based upon which Tier level the end-user wants to see. I was hoping to assign a text value (e.g. check box #1 “Tier 1” would have a value when checked of “Tier 1” instead of the number one, but it only let’s me assign a number to the value of each check box.
To make things even more difficult, I want to have the query show all records if the “All Outlets” check box is selected.
Can anyone think of an easy way to accomplish this?

p.s. I did it fairly easily by using a drop down box (the end-users selection would match up with the text value in the table) but I like the look of check boxes for this much better

Thanks, Paul

 

[Tadynn]

Are your check boxes grouped together? i.e you can only check one box at a time? If so, then you could write a case statement to the frame that surrounds all the controls.

Add to this a textbox that the query looks at to get it's parameters for the field 'Tier' i.e(SELECT * from [YourTable] WHERE [YourTable].Tier = Forms![YourForm]![YourTextField] Or IsNull(Forms![YourForm]![YourTextField]);. Then insert an afterupdate procedure on the frame surrounding the forms, for example:


'Where [framename] = the name of your frame
[TextBox] = the name of the textbox on your form (note that you could set this textbox to visible = False

Private Sub [FrameName]_AfterUpdate()
Select Case Me.[Framename].Value

Case "1"
[TextBox].Value = "tier1"
Case "2"
[TextBox].Value = "tier2"
Case "3"
[TextBox].Value = "tier3"
Case "4"
[TextBox].Value = "tier4"
Case "5"
[TextBox].Value = ""
End Select

End Sub

Finally assign a button to the form that opens the results of the query, if you don't have something like it already

Rgrds, Tadynn

 

[ccepaulb]

OK, I think I'm following you..Thanks

Would the statement "Or IsNull(Forms![YourForm]![YourTextField]);. " Take care of the "All Outlet" selection? I was worried that that part would be tricky, if this option is selected, somehow I'd have to have the query remove the criteria for the Tier field and show ALL outlets (Tier 1 + Tier2 + Tier 3)

Thanks, Paul

 

[ccepaulb]

Tadynn

Thanks, that worked!
I only had one problem though, the tier 1,2 & 3 all worked, but the "all Outlet" Case 4 did not work.
I put the Text Box value to equal "", but it apparently did not link up with the query and give me all Tiers.
Is there a way to use an OR statement for case 4 that will include all three ("Tier 1" "Tier 2" and "Tier 3") as criteria within the query that will show all records?

Thanks, Paul

 

[ccepaulb]

Basically all I need to say is for case four to equal no criteria in the query.
But I do not know how to write that.

Thanks, Paul

 

[ccepaulb]

I fixed the problem. Thanks for your help!

-Paul