Searching through a subform on a form

[HarryScofs]

I borrowed some code to try to create a dynamic filter that can search through a table named Projects. The searchable criteria is Project Name, Status and also a start and end date. I would like to be able to search through the records based on a range of start or end dates. Here's the code:

Private Sub Command24_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Project Name
If Not IsNull(Me.ProjectName) Then
'Create Predicate
strWhere = strWhere & " AND " & "[Project].[Project Name] = " & Me.ProjectName
End If

' If Status
If Not IsNull(Me.Status) Then
'Add the predicate
strWhere = strWhere & " AND " & "[Project].[Status] = " & Me.Status & ""
End If


' If Start Date From
If IsDate(Me.StartDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Projects.[Start Date] >= " & GetDateFilter(Me.StartDateFrom)
ElseIf Nz(Me.StartDateFrom) <> "" Then
strError = cInvalidDateError
End If



' If Start Date To
If IsDate(Me.StartDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Projects.[Start Date] <= " & GetDateFilter(Me.StartDateTo)
ElseIf Nz(Me.StartDateTo) <> "" Then
strError = cInvalidDateError
End If

' If End Date From
If IsDate(Me.EndDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Projects.[End Date] >= " & GetDateFilter(Me.EndDateFrom)
ElseIf Nz(Me.EndDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If End Date To
If IsDate(Me.EndDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Projects.[End Date] <= " & GetDateFilter(Me.EndDateTo)
ElseIf Nz(Me.EndDateTo) <> "" Then
strError = cInvalidDateError
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Search_Projects_Subform.Form.Filter = strWhere
Me.Search_Projects_Subform.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Right now I am getting an error on the

Me.Search_Projects_Subform.Form.Filter = strWhere

line. I have checked and the Subform name is not misspelled. Any Suggestions? I could definitely use some help!

[pbaldy]

Let's learn how to fish. The way to debug this type of thing is to add this line:

Debug.Print strWhere

right before the line that errors. Run the code, and that will print the finished SQL to the VBA Immediate window. You can examine it there and see if you spot the problem. If not, post the SQL here. I have a thought, but would rather walk through it. By the way, it always helps when you tell us what the error is.

[HarryScofs]

Ok I put the that into my code and ran it but I didn't get a debug window, just another error message saying "Syntax error (missing operator) in query expression. How do I do this again???

Sorry I'm not very good at all of this.

[pbaldy]

See here:

http://www.baldyweb.com/ImmediateWindow.htm.

[TheShabz]

Not to ruin the surprise but I'm 99% sure it's a case of string-in-string concatenation.

[HarryScofs]

I'm still not getting the Immediate VBA window to pop up with the SQl... Sorry. Any idea whay I could be doing wrong?

[pbaldy]

Can you post the db here?

[HarryScofs]

No the db is too big to upload. I changed the code a little and took away the date function. The "Me.Search_Projects_Subform.Form.Filter = strWhere" Is highlighted in yellow when the debugger runs.

I also get a "Data type mismatch in criteria expression" whenever I try to search. Or I get a "Syntax error (missing operator) in query expression. I have a similiar code in another form in my database and it works perfectly fine. I can't figure out what the difference is.

Private Sub Search_Click()
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Project Name
If Not IsNull(Me.ProjectName) Then
'Create Predicate
strWhere = strWhere & " AND " & "Projects.[Project Name] = " & Me.ProjectName & ""
End If

' If Status
If Not IsNull(Me.Status) Then
'Add the predicate
strWhere = strWhere & " AND " & "Projects.[Status] = " & Me.Status & ""
End If
If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.Search_Projects_Subform.Form.Filter = strWhere
Me.Search_Projects_Subform.Form.FilterOn = True
End If
End Sub

[pbaldy]

Is the Immediate window not visible, or it is and the SQL is not showing up? If it's not visible, View/Immediate Window.

[HarryScofs]

Ok I this is what poped up in the immediate window. Not sure if this is what yall are looking for.

1=1
1=1 AND Projects.[Project Name] = 114
1=1 AND Projects.[Project Name] = 76
1=1 AND Projects.[Project Name] = 39
1=1 AND Projects.[Project Name] = 39 AND Projects.[Status] = 4.Completed
1=1 AND Projects.[Project Name] = 39 AND Projects.[Status] = 4.Completed
1=1 AND Projects.[Project Name] = 39 AND Projects.[Status] = 4.Completed
1=1 AND Projects.[Project Name] = 39 AND Projects.[Status] = 4.Completed
1=1 AND Projects.[Project Name] = 39 AND Projects.[Status] = 4.Completed
1=1
1=1 AND Projects.[Project Name] = 16
1=1 AND Projects.[Project Name] = 102 AND Projects.[Status] = 3.In Progress
1=1 AND Projects.[Project Name] = 1 AND Projects.[Status] = 5.Closed

[pbaldy]

Values for text and date fields require delimiters around them. Generally single quotes around text values and # around dates. Here's a tutorial:

http://www.baldyweb.com/BuildSQL.htm

Not sure about the first field but the second value is text. Did this one work?

1=1 AND Projects.[Project Name] = 114

If not, I assume that is a text field too.