Query with Checkboxes!! Help - Pls Need to solve this problem ASAP

[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

---------------------------------------------------

I have a command button called CmdSearch. That is to be pressed once the user makes their decision. Thanks in advance

 

[Nordyck]

I've had the same problem a while back but but i had both strings, dates and checkboxes (about 40 of them). This is how I solved it.

dim st as string
dim st1 as string
dim sql as string
dim op as string 'option for opt1, opt2

st = ""
st1 = ""
op = iif(op1," AND ", " OR &quot
st = iif(filenumber,filenumber,"&quot
st1 = iif(incidenttype,incidenttype,"&quot
st = iif(st = "",st1,iif(st1="",st,st & op & st1))
st1 = iif([Date of Occurence],[Date of Occurence],"&quot
st = iif(st = "",st1,iif(st1="",st,st & op & st1))

and so on

Once you finished the criteria then you build your sql statement.

sql = "select * from table where " & st

If you don't like using immediated if statements, you can change them to standard if ... then ... else statements.

have fun.

 

[MsPeppa]

Thanks, that's good stuff...Do you think you could possibly tell me what each statement does. Like explain what their purpose is. Thank you

 

[Nordyck]

The way that this works is you build your where clause as you go through the various criteria for the select statment.

'op' sets your option for 'and' or 'OR' to be used in the crierica statement.

'st' is the criteria statement that is being built.

'st1' is for each piece of the criteria statement.
if its for date field then it st1 = "..."

using the iif is much simpler and cleaner than using if...then...else clauses.

the format for iif is
st = iif((test condition),true statement, false statement)

The proess goes like this.
1: Int variables
2: build 1st criteria and assign it to st1
3: build add st1 to st
4: go to your next criteria and build st1
5: repeat #3 and #4 until done
nordycki@hotmail.com