Report generation with filter in form

[colintis]

Hi guys, I'm having problem with passing the where clause SQL into the report for preview. The form I created allows users to make 3 steps of selection before the report is generated: The date range, a group by selection, and the variable filter for the group by field. By now I put the date range selection aside and try to get the report working with just the group by selection and the variable filter. Below is the code I had written so far.

    Dim dbs As Database     'Points to current database
    Dim rpt As String       'Points to the report will open
    Dim qry As QueryDef     'Points to the query that runs the report
    Dim qryName As String   'Name of the query
    Dim strSQL As String    'SQL code for the query
    Dim sWhere As String    'The where clause for the query
    
    Dim oItem As Variant
    Dim sTemp As String
    Dim sBy As String
    Dim fstEle As Boolean
    
    fstEle = True
    
    qryName = "TrainingHr_Area"
    Set dbs = CurrentDb
    
    'Set Default where clause
    sWhere = "(A.Attendance='T' OR A.Surname Like 'Training*')"
    
    
    ' Delete the existed query
    dbs.QueryDefs.Delete qryName
        
    If FraRpt.Value = 1 Then            'Select the report to open as by Department
        rpt = "TrainingHr_Dept"
        sBy = " By Department"
        
    ElseIf FraRpt.Value = 2 Then        'Select the report to open as by Area
        rpt = "TrainingHr_Area"
        sBy = " By Area"
        
    Else
        MsgBox "Pleae select the report to be view by Area or Department."
        Exit Sub
    End If
    
    'Check the list box if any particular item(s) selected
    If Me!lstAreaDept.ItemsSelected.count() = 0 Then
        MsgBox "Nothing was selected from the list", vbInformation
'        Exit Sub  'Nothing was selected
    Else
        'Check which is the selected option
        If FraRpt.Value = 1 Then
            sWhere = sWhere & " AND E.Department"
        ElseIf FraRpt.Value = 2 Then
            sWhere = sWhere & " AND A.Area"
        End If
        
        'Add selected item(s) into the IN()
        For Each oItem In Me!lstAreaDept.ItemsSelected
            If fstEle Then
                sTemp = sTemp & Me!lstAreaDept.ItemData(oItem)
                sWhere = sWhere & " IN (" & Chr(34) & Me!lstAreaDept.ItemData(oItem) & Chr(34)
                fstEle = False
            Else
                sTemp = sTemp & ", " & Me!lstAreaDept.ItemData(oItem)
                sWhere = sWhere & "," & Chr(34) & Me!lstAreaDept.ItemData(oItem) & Chr(34)
            End If
        Next oItem
    End If
    
    MsgBox "You have selected: " & sTemp & sBy
    strSQL = "SELECT A.Area, IIf(SUM(ManfHours)=0,COUNT(A.Surname)*8.5,SUM(ManfHours)) AS TotalTraining" & _
             " FROM Attendance AS A LEFT JOIN Employees AS E ON A.EmployeeNo=E.EmployeeNo WHERE " & sWhere & _
             " GROUP BY A.Area;"
    Set qry = dbs.CreateQueryDef(qryName, strSQL)
    
    DoCmd.OpenReport rpt, acViewPreview

When I tried to run the form, it pops out error saying the section width is greater than the page width, and after i clicked OK it asks me to enter the parameter again to generate the report. Hence, the report generate is only a small column of data that only takes half the page.

EDIT (feedback): Continued from the reason of editing. Both query and report are opened successfully by just clicking on them, but still pops the same error when i try to open in the form's VBA....

Please tell me what am I missing with it. Thanks

[colintis]

I had sort out "the section width is greater than the page width" problem from the Microsoft support page and now the code runs with what I want.

Generally all we need to do is to set the Margin under Page Layout from Narrow to Normal.

[NeoPa]

Reason: I had re-write the code so it re-create the query that is the record source of the report

As a general rule, it is better when working with main Access objects (reports & forms) to keep the recordsource static and apply a filter to the object at runtime. This really does simplify things, especially for coding. You work out what the filter string is in your code, then apply this same string to the object when you preview it as well as when you print it. Does this make sense.

[colintis]

As a general rule, it is better when working with main Access objects (reports & forms) to keep the recordsource static and apply a filter to the object at runtime. This really does simplify things, especially for coding. You work out what the filter string is in your code, then apply this same string to the object when you preview it as well as when you print it. Does this make sense.

Yes I understand your mean, I will try restructure the code in a more static way as I can get the my desired result in this method.

[NeoPa]

Cool. I doubt you'll regret that :)

[colintis]

Cool. I doubt you'll regret that :)

After I make it static to the query, by just creating the where clause. It then pop-up asks me to enter the filter parameters which was already written in the clause.

    Dim rpt As String       'Points to the report will open
    Dim strSQL As String    'SQL code for the query
    
    If FraRpt.Value = 1 Then            'Select the report to open as by Department
        rpt = "TrainingHr_Dept"
        
    ElseIf FraRpt.Value = 2 Then        'Select the report to open as by Area
        rpt = "TrainingHr_Area"
        
    Else
        MsgBox "Pleae select the report to be view by Area or Department."
        Exit Sub
    End If
    
    Dim oItem As Variant
    Dim sWhere As String    'The where clause for the query
    Dim fstEle As Boolean

    fstEle = True
    
    'Set Default where clause
    sWhere = "(A.Attendance='T' OR A.Surname Like 'Training*')"
    
    'Check the list box if any particular item(s) selected
    If Me!lstAreaDept.ItemsSelected.count() = 0 Then
        'Below codes are for testing purposes
        '------------------------------------
        'MsgBox "Nothing was selected from the list", vbInformation
        'Exit Sub  'Nothing was selected
    Else
        'Check which is the selected option
        If FraRpt.Value = 1 Then
            sWhere = sWhere & " AND E.Department"
        ElseIf FraRpt.Value = 2 Then
            sWhere = sWhere & " AND A.Area"
        End If
        
        'Add selected item(s) into the IN()
        For Each oItem In Me!lstAreaDept.ItemsSelected
            If fstEle Then
                sWhere = sWhere & " IN (" & Chr(34) & Me!lstAreaDept.ItemData(oItem) & Chr(34)
                fstEle = False
            Else
                sWhere = sWhere & "," & Chr(34) & Me!lstAreaDept.ItemData(oItem) & Chr(34)
            End If
        Next oItem
        
        'End of adding item(s) to the clause
        sWhere = sWhere & ")"
    End If
    
    'Implement date values
    sWhere = sWhere & " AND A.Date BETWEEN " & Format(FromDate, strcDate) & " AND " & Format(ToDate, strcDate)
  
    DoCmd.OpenReport rpt, acViewPreview, , sWhere

I make the dims in the middle because if it works that part of code I'll put it into a function. The parameter it asks are those that are not in the select list, which I don't want them to show up in the query result. Thanks.

[NeoPa]

I've split an addendum question into its own thread at Set ControlSource of Report TextBox to String.

By the way, I assume that the line of code you're experiencing this on is line #55. It's generally a good idea to say this when postiing code. If unknown you can say that instead, but at least then we know where to start looking.

[NeoPa]

For this part of the question I suggest you follow the instructions below and, if you still need help, post the resultant SQL string you're using in here for us to check over for you. It may not be necessary by then of course, as this will make the whole thing a lot easier to understand.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[colintis]

For this part of the question I suggest you follow the instructions below and, if you still need help, post the resultant SQL string you're using in here for us to check over for you. It may not be necessary by then of course, as this will make the whole thing a lot easier to understand.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

Yes, the error highlight is pointing to code line #55 for experiencing pop-ups to enter the parameter values. The whole SQL query is based on the strSQL at the start. I paste the resultant SQL here in the following condition starting from default:

sWhere = "(A.Attendance='T' OR A.Surname Like 'Training*')"

Then in condition I select to display the area in "Health", "Manufactur e" and "Sys Dev." Fields. Results with range 2010/05/01 to 2010/05/28, which becomes:

SELECT A.Area, IIf(SUM(ManfHours)=0,COUNT(A.Surname)*8.5,SUM(ManfHours)) AS TotalTraining
FROM Attendance AS A LEFT JOIN Employees AS E ON A.EmployeeNo=E.EmployeeNo
WHERE (A.Attendance='T' OR A.Surname Like 'Training*')
AND Areas IN ("Health","Manufacture","Sys Dev.")
AND A.Date BETWEEN #05/01/2010# AND #05/28/2010#
GROUP BY A.Area;"

This query works fine in directly open from report or by the re-create query method. But not with trying to execute it as a separate where clause in post #6. I've do some googleling with this problem, in result it is found the pop-up parameters are required to be included in SELECT and GROUP BY. But in result for doing that, the report will not be grouping the names of areas in one row, which is not the result I need in the report.

[NeoPa]

Well, trying to understand what you're actually saying is a little difficult for me. I'm sure you're doing your best, but the English doesn't make too much sense I'm afraid. Of course, I don't know where you're from but I expect your English is much better than my attampts at your language would be :D

Anyway, from what I can see, I would guess that your problem is mainly about using the correct names for your field references. In one place you refer to A.Area, whereas in another you refer simply to [Areas]. It's possible you have both of course, but I suspect not.

If you don't find this resolves your problem then possibly post details of all the fields in your tables ([Attendance] and [Employees]). With this I'm sure we could check over the SQL and see if anything is wrong there.

[colintis]

Well, trying to understand what you're actually saying is a little difficult for me. I'm sure you're doing your best, but the English doesn't make too much sense I'm afraid. Of course, I don't know where you're from but I expect your English is much better than my attampts at your language would be :D

Anyway, from what I can see, I would guess that your problem is mainly about using the correct names for your field references. In one place you refer to A.Area, whereas in another you refer simply to [Areas]. It's possible you have both of course, but I suspect not.

If you don't find this resolves your problem then possibly post details of all the fields in your tables ([Attendance] and [Employees]). With this I'm sure we could check over the SQL and see if anything is wrong there.

I'm sorry that if my grammars and sentence structures are confusing...eve n myself....for speaking 2 foreign languages everyday and thinking things in my own mother tongue languages...

I just missed out some details about the cancel error the database is given. Lets use back the SQL code from my last post (#9), that is used to open report with the WhereCondition. The [Area] part I had placed the [A.] in front, as it supposed to be part of the code that I missed on typing the code...

SELECT A.Area, IIf(SUM(ManfHours)=0,COUNT(A.Surname)*8.5,SUM(ManfHours)) AS TotalTraining 
FROM Attendance AS A LEFT JOIN Employees AS E ON A.EmployeeNo=E.EmployeeNo 
WHERE (A.Attendance='T' OR A.Surname Like 'Training*') 
AND A.Areas IN ("Health","Manufacture","Sys Dev.") 
AND A.Date BETWEEN #05/01/2010# AND #05/28/2010# 
GROUP BY A.Area;

This SQL query has no error running in the query re-create solution which I had at first. But in the new method passing the where condition in the open report command, it will pop up parameter windows of the following fields:

• A.Attendance
  A.Surname
  A.Date

These fields cannot be included in SELECT because they will affect the output of the query.

Output A: (Area, TotalTrainingHr s)

Health 85hrs
Manufacture 120hrs
Sys Dev. 40hrs

Output B: (Area, TotalTrainingHr s)

Health 2hrs
Health 7hrs
Health 5hrs
...
Manufacture 10hrs
Manufacture 7.5hrs
Manufacture 4hrs
...
Sys Dev. 8hrs
Sys Dev. 2hrs
Sys Dev. 8.5hrs
...

Output A is the original result I'm looking for, it is able to generate in the report through query re-creation. Output B includes the 3 fields above in SELECT to resolve the cancel error that appears.

Therefore it has nothing wrong with the fields in the tables.

[NeoPa]

This is still very confusing.

Are you copy/pasting the SQL code? Or are you typing it again for the post? If you're not copying it now, then please start again and do it by copying and pasting. The likelihood of errors when just typing it is is far too high to have people waste their time looking at it.

Never, ever, type in code and ask that people help you with it. People generally get cross when they realise.

If it is copied / pasted in, then I'm still having difficulty seeing what you're saying. Either you have two fields in the [Attendance] table called [Area and [Areas] (which would be strange in itself), or you have more errors in the SQL that you haven't even mentioned. At this stage I cannot tell which, but I cannot help with this as it stands now. There is just too much that doesn't add up.

[NeoPa]

colintis: This SQL query has no error running in the query re-create solution which I had at first. But in the new method passing the where condition in the open report command, it will pop up parameter windows of the following fields:

By the way, you also mention a where condition that you are trying, but I see nowhere that you've told us what is in it. This is a good idea in principle, but if we're to try to determine why it is behaving as you explain, then we will surely need to see what it is you are using.