custom search form

[datacontrol]

Hello all...

I am in the midst of setting up a custom search form. On my form, there are checkboxes next to almost every field. I f the user selects these fields or not is what the search is based on.


So, I was trying to create a statement that says if Me!whatever = -1 AND Me!whatever = 0 Then.......

But I get errors when using and. Is there a better way? I need a lot of help on this little sucker, Please help! I placed * around my problem code.

This form is tied to a report!

Option Compare Database
Option Explicit



Private Sub Form_Load()


End Sub

Private Sub option_AfterUpdate()








End Sub
Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Dim where2 As Variant
Dim where3 As Variant
Dim where4 As Variant
Dim where5 As Variant
Dim where6 As Variant
Dim where7 As Variant
Dim where8 As Variant
Dim where9 As Variant
Dim where10 As Variant
Dim where11 As Variant
Dim where12 As Variant

Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

' Note the single quotation marks surrounding the text fields [Ship
' Country] and [CustomerID].
' Note that there are no type-casting characters surrounding the
' numeric field [EmployeeID].


where = Null
where = where & " AND [dst_user]= '" + Me![UserName] + "'" ' Search by username
where2 = Null
where2 = where2 & " AND [dst_task] = " & Me![task] & "" ' Search by task only
where3 = Null
where3 = where3 & " AND [dst_date] between #" + Me![UserStartDate] + "# AND #" & Me![UserEndDate] & "#" ' Search between dates only
where4 = Null
where4 = where4 & " AND [dst_date] = #" & Me![OneDate] & "#" ' search by one date only
where5 = Null
where5 = where5 & "AND [dst_user]= '" + Me![UserName] + "' AND [dst_date] between #" + Me![UserStartDate] + "# AND #" & Me![UserEndDate] & "#" 'Search by user and between date
where6 = Null
where6 = where & where2 'search by task and user only
where7 = Null
where7 = where & where3 ' search by username and between dates
where8 = Null
where8 = where7 & where2 'Search by user, task and between dates
where9 = Null
where9 = where2 & where3 'Search by task and between date
where10 = Null
where10 = where & where2 & where4 'Search by user, task and one date
where11 = Null
where11 = where2 & where4 'search by task and one date
where12 = Null
where12 = where & where4 'search by user and one date
*******************************************
' Begin where username given searches
If me!user_opt = 1 and me!one_opt = 0 Then




Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tbldst " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query", acViewPreview

Else
MsgBox "Error".................

End If
*********************************************




Exit Sub


' Remove the following MsgBox line if you do not want to display the
' SQL statement.
' NOTE: The Mid function is used in the following MsgBox function to
' remove the word AND that follows the first Where clause. If you do
' not use the Mid function, the SQL statement contains the word AND
' at the beginning of the WHERE clause, for example:
'
' Select * from Orders where AND [CustomerID] = 'CACTU'
End Sub









Private Sub Exit_Click()
DoCmd.Close
End Sub

Private Sub user_option_AfterUpdate()

If user_option.Value = True Then
UserName.Enabled = False
Me.UserName = ""
End If

If user_option.Value = False Then
UserName.Enabled = True


End If

End Sub

Private Sub OneDate_AfterUpdate()
If one_opt.Value = True Then
OneDate.Enabled = True
ElseIf one_opt.Value = False Then
OneDate.Enabled = False



End If
End Sub

Private Sub start_opt_AfterUpdate()

End Sub

Private Sub task_opt_AfterUpdate()
If task_opt.Value = True Then
task.Enabled = True

End If

If task_opt.Value = False Then
task.Enabled = False

End If
End Sub

Private Sub user_opt_AfterUpdate()


End Sub

Private Sub UserStartDate_AfterUpdate()
If start_opt.Value = True Then
UserStartDate.Enabled = True
UserEndDate.Enabled = True




ElseIf start_opt.Value = False Then
UserStartDate.Enabled = False
UserEndDate.Enabled = False

End If

End Sub

 

[dcx693]

Holy moly. No offense, but what convoluted code. In quickly looking at the code, I wonder why you defined the where strings as Variants. I'd suggest looking at the code in Break mode and seeing what the value of that final "where" string is.

There are lots of examples on the forum of dynamically created SQL queries that use more streamlined code.

By the way, the value of a checkbox is typically -1 for true and 0 for false. You can just say:
If Me.Whatever
to test if the checkbox value is True.

 

[Peter D]

Instead of a search form, have you considered using Access' filter by form feature?

Hope this helps,

 

[datacontrol]

Attached is a screen shot of my form. I know my code is a mess, that is why I am here.

Does anyone have a REAL suggestion on how to accomplish what I need?


Thanks

 

[WayneRyan]

data,

Those were REAL suggestions.

Base your main form on a query. For criteria, use the controls
on your search form Forms![YourSearch]![cboUserName],
Forms![YourSearch]![cboTask], and Between
Forms![YourSearch]![BeginDate] And Forms![YourSearch]![EndDate]

On the Run Query button, do a Forms![MainForm].ReQuery

Wayne