Search records based on a form with Checkboxes...Need help PLS!!!

[MsPeppa]

I have a form that is called "Custom Search" What custom search has on it are two option buttons "AND" & "OR". Then it contains Checkboxes with "Filenumber", "Incident Type", "Date of Occurence" and a few others, but those three should be enough to get my point across. What happens is the user is to select if they want the search to match all of them, or they want the search to just match at least one. After the user selects a checkbox a textbox appears and that is where they are supposed to enter the data they wish to search for in the corresponding field (so if they check filenumber a textbox appears for the desired filenumber, and the same for incident type and date of occurrence) What I want this program to do is to somehow create a query that says, depending on if the user selects AND or OR than it will search the records in that sense. So if the user selects "AND" and they check for "Filenumber" and "Incident Type" than it will search for those fields. Exact code would be great, but suggestions may also work in this situation. Just to give some starters I will give you the names below of all of my check, option and textboxes.

-----------------------------------------------------
FileNumber (Corresponding checkbox = FileNumCheck
Textbox = FileNumText)
IncidentType (checkbox = IncidentTypeChk
Textbox = IncidentTypeText)
Date of Occurrence (checkbox = DateChk
Textbox = DateText)

AND option button = Opt1
OR option button = Opt2

 

[Taff07]

MsPeppa,

Easiest way is to create your query directly using SQL.

Bets way is to create 2 queries manually from scratch, one using the AND option and another Using the OR option. Compare these to see what the difference between them is. I think the only difference will be in the WHERE or HAVING sections. The start of the SQL should be the same and you can use this as the basic SQL String. Within the coding afterwards you will need to add and expand the base SQL with your criteria.

this can be added piecemeal for each of your check boxes. just use IF statements.

Let me know if this is confusing, and I`ll try and give you some example when I get the chance.

Ian

 

[MsPeppa]

Extremely confusing, do you think you could give me a one line example of each. Like, what to write to add statements to the AND and the OR. Thanks. I'm not to Knowledgable in sql, but when I see an example then I can expand on it.

 

[Taff07]

MsPeppa,

Below are examples of the SQL you need to generate from your form. The Basic which normally would return all of your data remains consistent throughout.

The only change afterwards, determined by your choice of AND/OR is the Command linking your criteria together.

just start with your variable string being the basic and build it up with each check box from there.

Any further queries just let me know and I`ll see what I can do.

Ian

Basic (Assumes you want all fields to be returned)
-----

SELECT RepairsOther.*
FROM RepairsOther;


AND used
--------

SELECT RepairsOther.*
FROM RepairsOther
WHERE (((RepairsOther.[FileNumber])=999999) AND ((RepairsOther.[IncidentType])="One off&quot AND
((RepairsOther.[Date of Occurrence])=#17/12/01#));


OR used
-------

SELECT RepairsOther.*
FROM RepairsOther
WHERE (((RepairsOther.[FileNumber])=999999)) OR (((RepairsOther.[IncidentType])="One off&quot) OR
(((RepairsOther.[Date of Occurrence])= #17/12/01#));