Using a check box to determine query criteria

[pyroskliq]

I have created a form containing a subreport that lists records with the fields vendor number, beginning date, and ending date. Some of the records do not have an ending date. My current query returns all records by vendor number. What I'm attempting to do is place a check box on the form that will allow the user to limit the query to only those records that do not have an ending date.

My current direction has been to create a subroutine that checks whether or not the box is checked on the form (If [checkbox] = true then...else...), then use that subroutine to change the criteria on my "ending date" field in the query. I keep getting a "query too complex" error when I attempt to do this.

Am I going about this the wrong way?

Thanks in advance.

 

[Nordyck]

I've had the same problem a while back dealing with 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 AND or OR

st = ""
st1 = ""
op = iif(Option," AND ", " OR &quot
st = iif(filenumber,filenumber,"&quot 'Checkbox
st1 = iif(incidenttype = "", "",incidenttype) 'String
st = iif(st = "",st1,iif(st1="",st,st & op & st1))
st1 = iif(isnull([Date of Occurence]),"","Date=#" &[Date of Occurence] & "#&quot 'Date
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 Its messy but it can be done.

have fun.