report filter connector.

[pikoy]

need help on a connector.

I have a form that filters the report prior to preview. With the current code, it replaces what has been valued first when both filters are valued.

Can someone point out the direction of how this can be done or what I am doing wrong?

current code:

stDoc = cboReport
   If Me.Category <> "" Then var = "Category = '" & Me.Category & "'"
   If Me.Build<> "" Then var = "Build = '" & Me.Entity & "'"
   If Me.DueDateFrom <> "" Then var = "Projects.[DueDate] = #" & Me.DueDateFrom & "#"
   DoCmd.OpenReport stDoc, acViewPreview, , var

revising the code I tried Nz and If NOT isNull (see below)

    ' If Status
    If Nz(Me.Status) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Projects.Status = '" & Me.Status & "'"
    End If
    
        ' If Project
    If Nz(Me.Project) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Projects.Project = '" & Me.Project & "'"
End If
 
    ' If Due Date From
    If IsDate(Me.DueDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Projects.[Due Date] >= " & GetDateFilter(Me.DueDateFrom)
    ElseIf Nz(Me.DueDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
 
    If strError <> "" Then
        MsgBox "Please verify your Search Criteria"
    Else
        'DoCmd.OpenReport stDoc, acViewPreview, , var
    End If

Thank you

 

[boblarson]

The first one would be:

    Dim stWhere As String
    Dim stDoc As String
 
    stDoc = cboReport
 
    If Len(Me.Category & "") <> 0 Then
        stWhere = "Category = '" & Me.Category & "' AND "
    End If
 
    If Len(Me.Build & "") <> 0 Then
        stWhere = stWhere & "Build = '" & Me.Entity & "' AND "
    End If
 
    If Len(Me.DueDateFrom & "") <> 0 Then
        stWhere = stWhere & "[Projects].[DueDate] = #" & Me.DueDateFrom & "#"
    End If
 
    If Right(stWhere, 4) = "AND " Then
        stWhere = Left(stWhere, Len(stWhere) - 5)
    End If
 
    DoCmd.OpenReport stDoc, acViewPreview, , stWhere

 

[pikoy]

Thanks again. Super Bob!!!

quick question... why string vs variant datatype?

 

[boblarson]

I tend to avoid variants just because they reserve the maximum memory space just in case something needed to fit there. If you have specific things you are working with, then I find it best to fit the items to what is being used. Since your filter is going to need to be a string, then I go for string. Since your doc name needs a string, I use a string. I personally like strong-typed variables to keep things cleaner (in my view) and so you know what you are working with (hence also my prefixes).

So, there is nothing technically wrong with using variants. I just don't like using them unless I need to, for the reasons stated above.

 

[pikoy]

I tend to avoid variants just because they reserve the maximum memory space just in case something needed to fit there. If you have specific things you are working with, then I find it best to fit the items to what is being used. Since your filter is going to need to be a string, then I go for string. Since your doc name needs a string, I use a string. I personally like strong-typed variables to keep things cleaner (in my view) and so you know what you are working with (hence also my prefixes).

So, there is nothing technically wrong with using variants. I just don't like using them unless I need to, for the reasons stated above.

Things to learn for me. Thanks again.