Check-boxes, search criteria and SQL

[Andren]

Hi!

I'm sorry to bother you again - abou a topic that is dealt with many times in this forum as well as in others. I have been reading many posts about multiple search by the help of check boxes - and I have tried to apply some solutions - but with little progress, because I only understand a tiny part of SQL and all you Pro's out there kind of asume that we newbies can follow you easily and therefore you often give a "part solution" but not the whole story.

Anyhow I have a search form (called "SerachForm"). In this I have three check boxes (symtom1, symtom2 and symtom3).
I have a query (called "query") containing id, symtom1, symtom2, symtom3, sex,age, etc etc retrieved from table "Main" (containing many more variables). Now I want to make a single or multiple selection of symtoms in the searchform having a report preview depending on that choise.

Is there ANYONE that would care to explain how tgis is should be done in a pedagogical way for us that not are pro's (yeat).

I Know ! Mile-O-Pile or the Mailman will not be to happy because you already tried to explain this some on times but anyhow....

Thanks in advanse

/ Anders

 

[EMP]

... I want to make a single or multiple selection of symtoms in the searchform having a report preview depending on that choise.

It would be easier to illustrate with a sample. The attached database (which was saved from Access 2000), contains this query:-

SELECT [ID], [symtom1], [symtom2], [symtom3], [sex], [age]
FROM Main
WHERE
([Symtom1]=[forms]![SearchForm]![symtom1] Or [forms]![SearchForm]![symtom1] Is Null) And
([Symtom2]=[forms]![SearchForm]![Symtom2] Or [forms]![SearchForm]![symtom2] Is Null) And
([Symtom3]=[forms]![SearchForm]![Symtom3] Or [forms]![SearchForm]![symtom3] Is Null);

The Triple State property of each of the three check boxes on the search form has been set to Yes, so that the check boxes can be "checked", "unchecked" or "blurred" (signifying Null).

When a check box is Null, or [forms]![SearchForm]![symtomX] Is Null becomes True, which has the effect of returning every record as if nothing was selected for that symtomX. Hence the user can make a single or multiple selection on the search form.

Note If you subsequently edit the query in query Design View, Access will re-arrange the criteria (i.e. the Where Clause) of the SQL statement and add a lot of brackets and AND and OR.

Hope it helps.

 

[Andren]

Thank You

Dear EMP

Thank you.
I'll try to evaluate your suggestion tomorrow and let you know the result.

Best wishes: Anders

 

[Andren]

Dear EMP !!

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

You have tought me in the most pedagogical way ever how to do this. You have enlightend my day ! I am happy. I am young again.
Birds are singing. You are a genious. Such a simple and straightfoeward solution. AND yeat i could never have figured out by myself - I was totaly in the wrong direction.
And the best is that I understand the SQL-code. Never did that before.

HOWEVER your (genious) solution picks a combination of , say:
symtom 1 and symtom 2 (in combination) correctly - BUT it also picks all cases where symtom 1 and 2 are correct but symtom 3 is not checked - why ? I want a list that can exclude symtom 3 (if it is not checked).

I made my independent chek-boxes "trippled-set" but they still
don´t have the little greish dot in them - like yours. Why?

Anyhow - I am most grateful. I'll be in London next weak - and if you live there I surely want to invite you for a beer or two.....

Anyhow - thank you very much.

Yours: Anders

 

[EMP]

The kudos goes to some other members of these forums. The techniques that I used are actually what I have learned from them.


>>
I want a list that can exclude symtom 3 (if it is not checked).
<<

To do this, you will have to use VBA.

In a query, the fields to be displayed is listed in the Select Clause of the SQL Statement. Once a field is listed in the clause, you cannot hide it. Though you can dynamically edit the Select Clause on the fly with VBA, but the three Symtom fields are needed in the report, you cannot remove any of them from the query.

However you can, in the report, apply some VBA code in the On Open event to set the Visible properties of a label and field to False, and adjust the "Left" properties of the remaining labels and fields to move them to the left.

In the attached example, the code in the On Open event to hide only the Symtom3 field would be:

Private Sub Report_Open(Cancel As Integer)

  If IsNull(Forms!searchform!symtom3) Then
    Dim WidthOfSymtom3 As Single
    WidthOfSymtom3 = Me.symtom3_Label.Width
    
    Me.symtom3_Label.Visible = False
    Me.symtom3.Visible = False
    
    Me.sex_Label.Left = (Me.sex_Label.Left - WidthOfSymtom3)
    Me.age_Label.Left = (Me.age_Label.Left - WidthOfSymtom3)
    
    Me.sex.Left = (Me.sex.Left - WidthOfSymtom3)
    Me.age.Left = (Me.age.Left - WidthOfSymtom3)
  End If

End Sub

If you want to hide another Symtom field too, the code will be a little more complicated.


>>
I made my independent chek-boxes "trippled-set" but they still
don´t have the little greish dot in them - like yours. Why?
<<

As soon as I place a check box on a form, set its Triple State property to Yes in the property sheet and run the form, I have the three choices of Check, Uncheck and Greyish when clicking on the check box. I can do this in both Access 97 and 2000. Perhaps you can try it again on a new form.

 

[Andren]

Hi again!

Your advice was much helpful and I am ashamed to keep asking, but I really want to be able to in report form pick out the combination of symtoms that arechecked in the search form.

you said:
-----------------------------------------------------------------------------------
If you want to hide another Symtom field too, the code will be a little more complicated.
-----------------------------------------------------------------------------------

I guess you are right!!!

Because I tried to do that by my own, but with meagre result. Would it be to impolite to, once again, ask for a template of how to do this in VBA ?

I searched and tried for my own a bit but I am stuck...

Thanks:

Anders