Advance search by check box

[ghost]

Greeting,

I have a form for search, in this form there is a combo box contains Major
types once the user click on a command button a form open and shows all
records that match combo box value selected. The query is working fine. But
What I want to is making 3 check boxes that allow user making advance search.
The fist check box is for married employees, second one for those who are
grater than 30 years and the last one for those who are single. If the user
checked married employee's check box, the form result shows all those who are
married and so one.
Thank you in advance

 

[Ken Sheridan]

You need to test separately for the criterion being True AND the check box
being True, OR the check box being False. If a row matches the criterion,
e.g. MaritalStatus = "Married" and the check box is True then the expression
will evaluate to True; if the check box is False then the expression will
evaluate to True regardless of whether the criterion is either satisfied or
not. Each expression is evaluated independently by enclosing it in
parentheses. All three separate expressions are operands of Boolean AND
operations, so all three expressions have to evaluate to True for a row to be
returned.

Note that the check boxes must not be triple state, i.e. cannot be Null, so
make sure they each have a DefaultValue property of False. You should also
take steps to ensure that a user cannot check both the Married and Single
check boxes, as no employee can be both.

You don't say whether you want also a selection from the combo box to be
optional or not, but if you do, then it should treated in a similar way to
the check boxes, but examined for Null rather than False.

So, putting all this together, a query would go something like this:

SELECT *
FROM YourTable
WHERE
(MajorType = Forms!YourForm!cboMajorType
OR Forms!YourForm!cboMajorType IS NULL)
AND
((MaritalStatus = "Married" AND Forms!YourForm!chkMarried)
OR NOT Forms!YourForm!chkMarried)
AND
((MaritalStatus = "Single" AND Forms!YourForm!chkSingle)
OR NOT Forms!YourForm!chkSingle)
AND
((Age(DoB,DATE()) >= 30 AND Forms!YourForm!chkOverThirty)
OR NOT Forms!YourForm!chkOverThirty);

I've assumed that age here is being computed using a function such as the
following Age function into which the value of a DoB field and the current
date are passed :
Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
End Function
The function comes from the following link, along with other means of
computing age on the basis of a person's date of birth:

http://www.mvps.org/access/datetime/date0001.htm


Ken Sheridan
Stafford, England

 

[Pat Hartman]

Checkboxes allow two states - true and false and for searching purposes, you
need three states - true, false, and ignore so I would switch to an option
group with three choices. Make the third choice - "Ignore" the default. To
make the query simpler, you can add a few lines of code to the AfterUpdate
event of the OptionGroup to save the result with the value that is in your
table fields.

Select Case Me.OptionGroup1
Case 1 ' the true option
Me.hiddenOG1 = -1
Case 2 ' the false option
Me.hiddenOG1 = 0
Case Elxe
Me.hiddenOG1 = null
End Select

Create extra hidden fields and create this code for each of the option
groups. Then the query will be:

Select ...
From YourTable
Where (fld1 = Forms!yourform!fld1 or Forms!yourform!fld1 Is Null)
AND (fld2 = Forms!yourform!fld2 or Forms!yourform!fld2 Is Null)
AND (fld3 = Forms!yourform!fld3 or Forms!yourform!fld3 Is Null)
AND (fld4 = Forms!yourform!hiddenOG1 or Forms!yourform!hiddenOG1 Is Null)
AND (fld5 = Forms!yourform!hiddenOG2 or Forms!yourform!hiddenOG2 Is Null);

Create the where clause with the query in SQL view and save the query in SQL
view. If you ever open this query in QBE view and save it, Access will
totally rearrange the where clause to the point where you will not recognize
it or be able to change it correctly. For queries like this, I frequently
copy them so I have a back up should I make the mistake of saving them once
they have been opened in QBE view.