Specific reports.

**lithium** · 12-18-2013, 06:10 PM

Okay, I have a table that I need to allow my users to run reports off of. I'm seriously drawing a blank on creativity on how to design the form to allow this in the easiest form. The person generally needs to be allowed to search by: Date Range, Person Requesting, Closed By, or Title. I want this to be a somewhat specific way of doing it though which might be more of a problem then I anticipated. I'd like them to be able to search using all the criteria's or just one. (Basically they can just search by title, otherwise they can search for the title and Date Range and who requested it.

**June7** · 12-18-2013, 06:17 PM

Options:

1. parameterized query, review http://datapigtechnologies.com/flash...mtoreport.html

2. VBA procedure to construct filter string, review http://allenbrowne.com/ser-62code.html
apply the filter to report like:
DoCmd.OpenReport "report name", acViewPreview, , strWhere

**lithium** · 12-18-2013, 06:24 PM

Code:

    Const cInvalidDateError As String = "You have entered an invalid date."
    Dim strWhere As String
    Dim strError As String
    
    strWhere = "1=1"
     
    ' If Assigned To
    If Not IsNull(Me.AssignedTo) Then
        'Create Predicate
        strWhere = strWhere & " AND " & "Table1.[Assigned To] = " & Me.AssignedTo & ""
    End If
    
    ' If Opened By
    If Not IsNull(Me.OpenedBy) Then
        'Add the predicate
        strWhere = strWhere & " AND " & "Table1.[Opened By] = " & Me.OpenedBy & ""
    End If
    
    ' If Status
    If Nz(Me.Status) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Table1.Status = '" & Me.Status & "'"
    End If
    
    
     ' If Priority
    If Nz(Me.Priority) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Table1.Priority = '" & Me.Priority & "'"
    End If
      
    ' If Opened Date From
    If IsDate(Me.OpenedDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Submitted] >= " & GetDateFilter(Me.OpenedDateFrom)
    ElseIf Nz(Me.OpenedDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Opened Date To
    If IsDate(Me.OpenedDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Submitted] <= " & GetDateFilter(Me.OpenedDateTo)
    ElseIf Nz(Me.OpenedDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Due Date From
    If IsDate(Me.ResolvedDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Resolved] >= " & GetDateFilter(Me.ResolvedDateFrom)
    ElseIf Nz(Me.ResolvedDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
     ' If Due Date To
    If IsDate(Me.ResolvedDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Resolved] <= " & GetDateFilter(Me.ResolvedDateTo)
    ElseIf Nz(Me.ResolvedDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Title
    If Nz(Me.Title) <> "" Then
        ' Add it to the predicate - match on leading characters
        strWhere = strWhere & " AND " & "Table1.Title Like '*" & Me.Title & "*'"
    End If
    

    If strError <> "" Then
        MsgBox strError
    Else
        DoCmd.OpenReport "Searched", , , strWhere, acFormEdit, acWindowNormal

    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

But it keeps just jumping directly to printing the report instead of just generating it on screen to view?

**June7** · 12-18-2013, 06:26 PM

See example in my first post.

**lithium** · 12-18-2013, 07:08 PM

I know, I've reviewed it but I'm still confused and running into weird issues =(

Now everytime I put a string in the Title box, i get a pop up box saying to "Enter Perimeter Value" but I typed something in there?

Code:

Option Compare Database

Private Sub Search_Click()
    Const cInvalidDateError As String = "You have entered an invalid date."
    Dim strWhere As String
    Dim strError As String
    
    strWhere = "1=1"
    ' If Title
    If Not IsNull(Me.Title) Then
        strWhere = strWhere & " AND " & "Table1.[Title] = " & Me.Title & ""
    End If
    ' If Assigned To
    If Not IsNull(Me.AssignedTo) Then
        'Create Predicate
        strWhere = strWhere & " AND " & "Table1.[Assigned To] = " & Me.AssignedTo & ""
    End If
    
    ' If Opened By
    If Not IsNull(Me.OpenedBy) Then
        'Add the predicate
        strWhere = strWhere & " AND " & "Table1.[Opened By] = " & Me.OpenedBy & ""
    End If
    
    ' If Status
    If Nz(Me.Status) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Table1.Status = '" & Me.Status & "'"
    End If
    
    
     ' If Priority
    If Nz(Me.Priority) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Table1.Priority = '" & Me.Priority & "'"
    End If
      
    ' If Opened Date From
    If IsDate(Me.OpenedDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Submitted] >= " & GetDateFilter(Me.OpenedDateFrom)
    ElseIf Nz(Me.OpenedDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Opened Date To
    If IsDate(Me.OpenedDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Submitted] <= " & GetDateFilter(Me.OpenedDateTo)
    ElseIf Nz(Me.OpenedDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Due Date From
    If IsDate(Me.ResolvedDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Resolved] >= " & GetDateFilter(Me.ResolvedDateFrom)
    ElseIf Nz(Me.ResolvedDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
     ' If Due Date To
    If IsDate(Me.ResolvedDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Table1.[Resolved] <= " & GetDateFilter(Me.ResolvedDateTo)
    ElseIf Nz(Me.ResolvedDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Title
    If Nz(Me.Title) <> "" Then
        ' Add it to the predicate - match on leading characters
        strWhere = strWhere & " AND " & "Table1.Title Like '*" & Me.Title & "*'"
    End If
    

    If strError <> "" Then
        MsgBox strError
    Else
        DoCmd.OpenReport "Searched", acViewPreview, , strWhere

    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

**June7** · 12-18-2013, 07:19 PM

The popup is probably due to a parameter in the query and Access can't find whatever is referenced. Post the query sql statement.

Table is named Table1? Not very informative. Probably don't even need the Table1. qualifier.

**lithium** · 12-18-2013, 07:29 PM

Maybe that could be a problem, I only have the source for the Report set to the table (Table1, I know it's not informative but I forgot to change it and need to, essentially it houses Issues of equipment including the reporting persons name, time, comments)

I don't know where the SQL statement would be though? Ive never dealt with this type of thing so I'm a bit lost

**June7** · 12-18-2013, 07:48 PM

Then RecordSource not the issue.
If you want to provide db for anlaysis, follow instructions at bottom of my post. Identify objects involved.

**lithium** · 12-19-2013, 10:30 AM

I would but there is a lot of info on it and im on my phone at the moment till I get back home and work on it. I have it to pull the data but it only pulls one record and not all of them (like I made three titles "test" and it only pulled one oldone

**lithium** · 12-20-2013, 05:10 AM

[ATTACH]Repair Database[/ATTACH]

**June7** · 12-20-2013, 04:46 PM

Criteria for text fields need apostrophe delimiters, like:

= '" & Me.Title & "'"

Need to fix the code for Title, Opened By, Assigned To, Resolved By, Status, Priority because they are all text fields in Table1.

The code has two conditional structures for the Title criteria.

Sorry, I should have seen those errors from the posted code.

**lithium** · 12-20-2013, 05:16 PM

Fixed them, but the thing still only withdraws one record on the report (instead of grabbing all the titles that I search for). Essentially, If I place like "Alarm 1", "Alarm 2" etc, in the tables Title fields for several records, the report that is pulled is only ONE of them if I search for "Alarm".

**lithium** · 12-20-2013, 05:19 PM

Nevermind, I think I figured it out. Apparently I have to press the "right" arrow to flip to a new page on the report. I have to change this setting...

How do I change it to be continuously going downward on new pages (where they can just keep scrolling down to view them all?)

**June7** · 12-20-2013, 05:27 PM

Reduce the size of the Detail section so there isn't so much white space.

PrintPreview doesn't have scrolling, ReportView does.

**lithium** · 12-21-2013, 06:40 AM

Whenever I use acReportView it automatically prints tho

Specific reports.

Thread Tools

Specific reports.

Similar Threads

Specific record counting in Reports

newbie - how to query for specific word contained in specific columns

Convert Client Reports to Web Reports

Reports w/ sub-reports very slow to open

Access Reports drop sub-reports

Posting Permissions