USe check boxes in form to show fields in query

[ian87]

Hi all,

I need to be able to show certain query fields based on whcih tick boxes i select in a form. For example my form contains a drop down box for companyname and then i want to view the revenues of only some countries so i have made them into tickboxes for the user to select.

E.g

Companyname combobox (this works fine, just reference it in criteria on query)

Tickboxes(name of tickbox)=

U.S.(chkUS)
France(chkFrance)
Germany(chkGermany)

Query Fields=
Companyname(linked to combobox choice)
USEst10
FranceEst10
GermanyEst10

So for example, if I want the query to show company name, USEst10 and France Est10 in datasheet view, how do i link the corresponding textboxes in the criteria field of the query??

Please any help would be much appreciated,

Ian

 

[John Big Booty]

As criteria for your Company name, use;

IIf([Forms]![YourFormName]![YourCheckName1]=-1,"USEst10",0) or IIf([Forms]![YourFormName]![YourCheckName2]=-1,"FranceEst10",0) or IIf([Forms]![YourFormName]![YourCheckName3]=-1,"GermanyEst10",0)

 

[ian87]

Thanks for the reply John, but I don't think you understood what I was asking

I have attached two pictures below to show you how I have organised things.

I want the tick boxes in the forms in picture 1 to show/hide the fields that they correspond to qryResultsReporting. What do i type in each criteria box to relate each field to the respective tick boxes?

 

[John Big Booty]

Given the number of countries you are dealing with I suspect that you will need to dynamically construct your SQL statement dependant on the check boxes that are checked. I don't think you can easily achieve your goal with queries short of writing a query for every possible combination of check box possibilities

 

[John Big Booty]

You will need to build up your Select Clause dynamically, something like;

Dim stSelect As String
stSelect = "tblServiceLin_geo.HomeEst10," [COLOR="SeaGreen"]'or whatever your default should be[/COLOR]

If Me.BrazilCheck = True Then
     stSelect = stSelect & " tblServiceLin_geo.BrazilEst10,"
End If

If Me.MexicoCheck = True Then
     stSelect = stSelect & " tblServiceLin_geo.MexicoEst10,"
End If

stSelect = Left(stSelect, Len(stSelect)-1)  [COLOR="SeaGreen"]'This line required to remove trailing comma[/COLOR]

You can then insert that into the rest of your SQL which should be fairly static.

 

[slvoight]

Hi - I'm new to both access and SQL... I'm having some trouble getting these select statements to work. Am I missing something before or after? Where exactly should this select code go?