Advanced search form

[ScrmingWhisprs]

I have a form that my users can create lists of volunteers based on criteria from their worker record.

The subform is based on one query that has for each searchable field, this criteria (as an example):

[WorkerType1]=[Forms]![frmCreateLists]![WorkerType1] Or [Forms]![frmCreateLists]![WorkerType1] Is Null

With the criteria being True.


My problem comes with Dates. The screenshot I attached shows Date Added and Date Modified as searchable criteria. The combobox before the Date search boxes contains Operators ie, =, >, <=, between. I'm not really sure what to put into the query to allow it to look at the date boxes appropriately.

Please help! Thanks
ScrmingWhisprs

 

[qdogfball]

Could you use a Select Case ?

You could have the Cases setup where the criteria changes based on the <, >=, =< or between, etc.

EX)

Function Bonus(performance, salary)
    Select Case performance
        Case 1
            Bonus = salary * 0.1
        Case 2, 3
            Bonus = salary * 0.09
        Case 4 To 6
            Bonus = salary * 0.07
        Case Is > 8
            Bonus = 100
        Case Else
            Bonus = 0
    End Select
End Function

 

[ScrmingWhisprs]

I suppose I could do something like that, but the rest of my search criteria is in a query. Unless I change the query to a SQL statement and put that into the code. But would I have to make a separate SQL statement for each operator?

 

[qdogfball]

You wouldn't necessarily have to put in a separate code for each SQL.

But the Case would help make the SQL code.

From one of the sample Databases (from Kenhiggs I think) on this site.

From a On Click event on a button

 strSQL = strSQL & " or " & "(tblMyData.my_color)='" & Me!lstColors.ItemData(intX) & "' " '

strSQL = "SELECT tblMyData.recno, tblMyData.my_text, tblMyData.my_color FROM tblMyData WHERE ((" & strSQL & "));"

The Me!lstColors is a listbox that gets put into the SQL statement

The thing with SQL statements is that you need to make sure you have all the " ' " around your information, or it won't work right. Dates I think you need to have # around them.