question on report customization

[aquinox]

Hi,

Just wonder if anyone might be able to help me here. I have a question on Access report. I have a database built using Access 03. I have a report that has a several subreports whose control sources are based on queries.

Sometimes, there are occasions where in the sub-reports, there are a set of records showing lots of zeros (0) as follows

Activity Project 2008/9 2009/10 2010/11
cleaning MAG 0 0 0
moping MAG 0 0 0
...

or a bit of both
Activity Project 2008/9 2009/10 2010/11
WasteRubbish MAG 0 0 0
Wood collect MAG 5 4 9
Cleaning MAG 0 0 0

What i would like to happen is if it's zeros across the board, treat it as "no data" or don't show it at all or blank
so, the first example used as above would be
Activity Project 2008/9 2009/10 2010/11


The second example is
Activity Project 2008/9 2009/10 2010/11
Wood collect MAG 5 4 9

I know that this can perhaps be achieved via query. but is there any way of doing this on the report itself rather than on query (i wasn't allowed to touch the query in the sub-reports). I was attempting to hide the details using the VBA codes if all zeros are found across the years as follows but got errors

If Me.TB_Year1_Sum = 0 And Me.TB_Year2_Sum = 0 And Me.TB_Year3_Sum = 0 Then
Me.Detail.Visible = False
End If

Even if the above code i wrote worked, it will not work for the case where there is a case where there are some zeros records across all years and non-zeros records across the years.

BTW, TB_Year1_Sum, TB_Year2_Sum, and TB_Year3_Sum are located in the report footer.

Thank you in advance

 

[Remou]

You can add a Filter to the subform, for example:

=[2008/9]>0 AND [2009/10]>0 AND [2010/11]>0

You will need to enure that you set Filter On to Yes.

It is possible to use a query without changing it, for example, you can set the RecordSource to:

[tt]SELECT * FROM qryQuery WHERE [2008/9]>0 AND [2009/10]>0 AND [2010/11]>0[/tt]

 

[dhookom]

Do you actually have field names like "2008/9 2009/10 2010/11" or are these the result of a crosstab query? These look like they should be data values rather than field names.

If you can't change the query, the filter is a decent route to go. If you want to use code in the On Format event of the detail section like:

 Cancel = (Nz([2008/9],0) + Nz([2009/10],0) + Nz([2010/11],0)) =0

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aquinox]

Thank you, Remou and Dhookom for your helps.

I tried the following code but got run-time error #2465

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
InitialYear = Form1.myYear.Value
MyYearValue = Val(Mid(InitialYear, 3, 2))
startYear = Left$(InitialYear, 2)

currYear = startYear + Right("00" + Trim(Str(MyYearValue)), 2) + "/" + Right(Trim(Str(MyYearValue + 1)), 2)
myyear1 = currYear

MyYearValue = MyYearValue + 1
currYear = startYear + Right("00" + Trim(Str(MyYearValue)), 2) + "/" + Right(Trim(Str(MyYearValue + 1)), 2)
myyear2 = currYear

MyYearValue = MyYearValue + 1
currYear = startYear + Right("00" + Trim(Str(MyYearValue)), 2) + "/" + Right(Trim(Str(MyYearValue + 1)), 2)
myyear3 = currYear

Cancel = (Nz(["'" & myyear1 & "'"], 0) + Nz(["'" & myyear2 & "'"], 0) + Nz(["'" & myyear3 & "'"], 0)) = 0
End Sub

Initially tried the following code but the zeros records still showed up in the report.

Cancel = (Nz([myyear1], 0) + Nz([myyear2], 0) + Nz([myyear3], 0)) = 0

Thank you in advance

 

[dhookom]

I don't see where you have declared any of your variables. Did you use Option Explicit in your module?

If you are combining strings/text, use "&" not "+". "+" is generally reserved for numeric addition.

This line is improper syntax.

Cancel = (Nz(["'" & myyear1 & "'"], 0) + Nz(["'" & myyear2 & "'"], 0) + Nz(["'" & myyear3 & "'"], 0)) = 0

If you want to reference a control by name, use something like:

   Me(strControlName)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aquinox]

Hi Dhookom,

Previously, i didn't use Option Explicit and now i do.

supposedly, they're all of type "string" - myyear1,myyear2, etc.

I changed the following code
Before:
Cancel = (Nz(["'" & myyear1 & "'"], 0) + Nz(["'" & myyear2 & "'"], 0) + Nz(["'" & myyear3 & "'"], 0)) = 0

After:
Cancel = (Nz(["'" & myyear1 & "'"], 0) & Nz(["'" & myyear2 & "'"], 0) & Nz(["'" & myyear3 & "'"], 0)) = 0

I still get run-time error
"Microsoft office Access can't find the field '|' refered to in your expression"

don't understand that - i think i don't miss anything there.

Thank you in advance

 

[dhookom]

I suggested using & to combine strings and + to add numbers. So, you changed the expression adding numbers to the &. You are moving in the wrong direction.

Did you under my comment about using a variable as a control name? Your syntax is still way off.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aquinox]

Hi Dhookom

I sort of don't quite understand how it works with the variable as control name. Sorry for being too slow to catch what you suggested before.

However, i just realised what you meant by saying + is for numeric addition use. so now i understand how the code you gave works now.

Cancel = (Nz([2008/9],0) + Nz([2009/10],0) + Nz([2010/11],0)) =0

Sorry i must've been half asleep. The above code works for me if i hardcoded the year 2008/9, 2009/10, 2010/11. How do i make them variables rather than hard coding because they are fields in the table?

Thank you for your patience

 

[dhookom]

As I suggested earlier, use this syntax

Cancel = (Nz(Me(CtrlName1),0) + Nz(Me(CtrlName2),0) + Nz(Me(CtrlName3),0)) =0

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aquinox]

Thank you, Dhookom, for your reply.

I actually did try it yesterday but wasn't sure what i was doing according to your suggestion.

Yesterday, this was what i did but got the error
Cancel = (Nz([Me(Ctr_Year1)], 0) + Nz([Me(Ctr_Year2)], 0) + Nz([Me(Ctr_Year3]), 0)) = 0

Then, tried what you just suggested based on your reply today.
Cancel = (Nz(Me(Ctr_Year1), 0) + Nz(Me(Ctr_Year2), 0) + Nz(Me(Ctr_Year3), 0)) = 0

However, the run-time error i got is different this time. It said "The control number you specified is greater than the number of controls". I doubled-checked this many times.

In that report, under "Detail" section, there are three(3) text-boxes named - Ctr_Year1, Ctr_Year2, and Ctr_Year3. Don't understand why i got the error.

Thank you in advance

 

[aquinox]

Hi Dhookom,

I found that in the event of sub Report_Open as follows
Private Sub Report_Open(Cancel As Integer)
InitialYear = Form1.myYear.Value
MyYearValue = Val(Mid(InitialYear, 3, 2))
startYear = Left$(InitialYear, 2)

...
Me.Ctr_Year1.ControlSource = currYear

MyYearValue = MyYearValue + 1
...
Me.Ctr_Year2.ControlSource = currYear
...
MyYearValue = MyYearValue + 1
...
Me.Ctr_Year3.ControlSource = currYear

Is that because all textboxes controls are used already? or have i misunderstood your suggestion here again?

Thank you in advance

 

[dhookom]

You are really confusing me. Can you help by describing how the Record Source of the report is created? Is the record source a crosstab query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aquinox]

Hi Dhookom,

Sorry about confusing you with my explanation. Ok, i can explain that.

The report has the Record Source coming from a query (which i'm not allowed to touch). In the query, there are a number of fields in there and some of them are the years - 2008/9, 2009/10, etc.

In the design view of the report, i have textboxes in the "Detail" section. They are Ctr_Year1, Ctr_Year2, Ctr_Year3 and many more

Here is the codes
in the sub Report_Open()
...
Me.Ctr_Year1.ControlSource = currYear
...
MyYearValue = MyYearValue + 1
...
Me.Ctr_Year2.ControlSource = currYear
...
MyYearValue = MyYearValue + 1
...
Me.Ctr_Year3.ControlSource = currYear

The value of currYear depends on the value of another form - Form1.myYear.Value

So if the user selects 2006/7 as the initial year, then the report has to show the year 2006/7, 2007/8, and 2008/9. So they have to be variable. I can't hard-code them in the code as mentioned above.

So i think this is the crosstab query, Dhookom.

Thank you in advance

 

[dhookom]

To make the detail section hidden for all records where all three year controls are 0, just use code in the On Format event of the detail section:

Cancel = (Nz(me.Ctr_Year1,0) +Nz(me.Ctr_Year2,0) + Nz(me.Ctr_Year3,0) ) = 0

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aquinox]

Hi Dhookom,

Thank you very much. It works now. Sorry for taking up much of your time to get this resolved because of my poor explanation.

You're a legend

Thank you