Missing fields (1 Viewer)

[Mal Ba]

You are being so good to me I thought I try your patience with another problem. Basically this whole thing is trying to convert something which works sucessfully in Excel into Access because you can't adequately lock down the tables in Excel to prevent people being silly. I'm sort of most of the way there, but there are a couple more issues.

The one here which has stumped me is as follows. I have input data which needs to be pivoted in order get the right table for output. The pivot works fine but depending on what filters are applied, there will be a different number of columns to display in the final report. So the standard report might have six columns, but only 1,2,4,5,6 might have date in them after the pivot has been applied. Having six columns bound to the six potential fields as required in the report, means we get the error "The Microsoft Access database engine does not recognise <column3> as a valid field name.

Now in the Excel version we get around that using the capacity in M Formula "Get an Transform" setting MissingColumn=Ignore : the missing column gets filled with nulls which we replaced with zeros and Bob's your uncle.

I can't see how one gets an Access form to do the same and simply include 0s where the field bound to the control doesn't exist. Using a formula with IsError() doesn't trap it nor does disabling the control seem to prevent it.

The only solutions I have come up with so far are:

1) to create a query with examples of all possible entries in it before we pivot the table, add those entries to the table, then pivot the table so we get these entries as column names, then filter out all those dummy entries so we get to the right data to present. This is horrible and longwinded. In fact even that would be easier in M formula than in SQL.

2) adding and removing the binding to the controls on the fly by VBA whenever the report is opened using the Report's On Error event or On_Open event, though even that seems a bit messy as it will involve iterating through the controls testing each one in turn and removing the binding, then on closing the Report, re-iterating through them again and restoring them in the filters/data is different next time.

But there must be a better/simpler way of dealing with missing fields in a report. Somebody please tell me what it is?

Thanks

 

[theDBguy]

Hi. How exactly are you creating the pivot table in Access? Or, maybe you're not using Access to create the pivot table? If so, are you creating it in Excel and then exporting/importing the result in Access? We are talking about a Report in Access, correct?

 

[Mal Ba]

We are talking about an Access Report.
I'm not using Excel at all. I'm trying to re-create what is currently done in Excel in Access.

The pivot table is created using Access and SQL. Simplifying the names, it looks like this

TRANSFORM Sum(1) AS [Value]
SELECT T.Name
FROM LYS INNER JOIN (E INNER JOIN T ON E.ID = T.ID) ON LYS.LY = E.LY
WHERE (LYS.O>0) AND (E.Omit=False) AND (LY.Include=True)
GROUP BY T.Name, E.Omit
PIVOT "Code" & [Order] & [ET];

It gives a table which will a variable number of columns with names starting with code thus:
Name CodeAA CodeAB CodeBB CodeCA
and then a record for each name containing data. The results of this query are then joined with 3 more field (joined on the [Name] field) before being presented in the final report which should have columns headed like this:
Name AddnInfo1 AddInfo2 AddInfo3 CodeAA CodeAB CodeAC CodeBA CodeBB CodeBC CodeCA CodeCB Code CC

In the specific example I have given columns for CodeAC, CodeBA, CodeBC, Code CB and Code CC would all be zeroes, but in other cases it would be other columns or none at all, depending on the filter applied to table T.

[Order] can only be A,B or C and [ET] can only be A,B,C so the total number of possible field is known and can be set up on the Report but whic actual fields will appear in the query is another matter.

M

 

[arnelgp]

then use Ubound textbox's (as many as you think) to your report.
on the Load event of the report, open a Recordset of your Rowsource.
and on each textbox (assumed to be numbered like tbox1, tbox2, etc.)
assign the control source to each tbox1.
if the fields of the recordset is "less" than the number of textboxes, you
set the remaining textbox Visible property to No.

 

[arnelgp]

sample report

 

[Mal Ba]

Thanks for that, sounds obvious, but I hit a problem.

If I try to assign a simple binding in the Report_ Load event like:
Me.Controls(i).ControlSource = "[Year1]" where [Year1] is the field in the recordset, it works fine.

If I put a formula into the ControlSource directly as a property on the Report, thus:
ControlSource: =iif(IsNull([Year1]),0,[Year1]) it also works fine.

BUT if in the VBA I do exactly the same assignment:
Me.Controls(i).ControlSource = "=iif(IsNull([Year1]),0,[Year1])"
it shows up as a #Error in the control when I run the report.

Why would this be?

 

[arnelgp]

you got error, that is when you Assign a value
using VBA.
the reason is that it Already has a Value (the expression
you put for it's ControlSource , "=iif(IsNull([Year1]),0,[Year1])"

you can Either use Expression or VBA assignment (the controlsource should be blank).

 

[Mal Ba]

I'm sorry, it is so difficult to be clear. I tried to do so by saying IF not AND. If I put the expression in as assigned directly to the property, it works. It does not work if I assign it via VBA. I am never trying to do both (why would I?).

If I assign it by VBA I doing so with the controlsource blank and it does not work. To be clear:

if in the VBA I do exactly the same assignment and (with the ControlSource unassigned (blank) in the properties pane of the Report):
Me.Controls(i).ControlSource = "=iif(IsNull([Year1]),0,[Year1])"
it shows up as a #Error in the control when I run the report.

Sorry for any confusion.

 

[Pat Hartman]

I think there might be a logic flaw with the example arnel posted. The example creates a one line report. If there were multiple lines, for example if there were one per day for a month, the code might not work correctly if the first row returned did not have data in all the columns. Just in case, I've attached an example I built. It is more complex so it might be harder to implement but I'll try to explain how it works.

There is a table (TEMP_AssignSequence) which is filled with the unique values that would end up as columns in the crosstab and assigns each a sequence number so in the picture below seq 1 = Electric and seq 2 = Heat. Once that table is populated the base query for the crosstab joins to that table and adds the SeqNum for each of the unique values. Then the crosstab, instead of pivoting on the variable value, pivots on the seqNum so the set of values is ALWAYS known. It will be 1,2,3,etc. I set a limit of 10 because 10 is all a typical Access report in landscape view has room for. You can adjust this if your columns are smaller or larger.

This is the crosstab query used as the RecordSource for the report. Notice that the crosstab pivots on the Seqnum and joins to the TEMP table on ExpenseType to CaptionText

Here is a picture of the report in design view. Notice the names of the bound fields and the caption values.

Here is the code in the report's load event that populates the Captions. It does a dLookup() to the TEMP table to retrieve the text for the captions.

Private Sub Report_Load()
    Dim ctl As Control
    
    On Error GoTo ErrProc
    For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acLabel
            If IsNumeric(ctl.Caption) Then
                ctl.Caption = Nz(DLookup("CaptionText", "TEMP_AssignSequence", "SeqNum = " & CLng(ctl.Caption)), "")
            End If
        End Select
    Next ctl

ExitProc:

    Set ctl = Nothing
    Exit Sub

ErrProc:
    Select Case Err.Number
        Case 13     'ignore
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
    End Select
    Resume ExitProc
    
End Sub

Here is the final report:

 

[plog]

Excel is probably the easiest path for the report.

Been in similar situation and what I did is generated a query in Access for the data I needed (unpivoted), pasted it into a 'data' tab in Excel and made a 'pivot' tab where I pivoted the data, formatted it and made the report exactly as needed. Then when I had to rerun the report every week I'd run the query, paste the data into the 'data' tab and refresh the pivot tab. 1 hour development time for the system, 2 minutes to rerun it every week.