Using variables in a report 1

[bhoran]

I am building a report that has monthly columns along the top.

and analysis lines underneath currently I am using this formula to provide one of the analysis lines
=((Sum(Abs([LD]="IBM F04&quot*[5]))/(Sum(Abs([LD]="AOP F04&quot*[5])))-1

but I need to make the F04 part a variable as this relates to the year something like F& variable"

However I have never used variables in a report before and need HELP.

I have a control that contains the the year in this format yyyy (2004)

I also need to use the variable plus 1 ie 2005

I am thinking I need something like Right([Year],2). But how do I define this as a variable?

Cheers

 

[KenReay]

Hi

You can reference a control within the report with the syntax Me("Controlname&quot, or to be more exact Me.Controls("ControlName&quot

So You could get at the part of the Year with something like

Right(Me("YearControl&quot,2)

Note if the control in question is a label you may need to explicitly give the .caption property so:

Right(Me("YearControl&quot.Caption,2)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bhoran]

I have tried a few different variations but can't seem to get it right perhaps my syntax is screwy. NB. I am using this as a formula in the control source property of an unbound text control box.

=((Sum(Abs([LD]="IBM F&Right(Me(txtFYYear),2)&quot*[1]))/(Sum(Abs([LD]="AOP F&Right(Me(txtFYYear)&quot*[1])))-1

When I use Me.Controls("txtFYYear&quot I get an error Undefined Function Me.Controls.

The source of txtFYYear is from a form where criteria is selected for the report to run.

What I am trying to achieve is to divide the row of detail where [LD] = IBM F04 by row of detail where LD = AOP F04

However 04 is determined by the fiscal year so next year it will be F05. The report "knows" which year to report on based on the selection criteria within a form, which is also shown in txtFYYear (using the form control as the source.

Perhaps there is a better way to do this?

 

[bhoran]

I got it working by referencing back to the form,

=((Sum(Abs([LD]="IBM F" & Right([Forms]![RunReports]![cmbRunRptsFY],2))*[1]))/(Sum(Abs([LD]="AOP F" & Right([Forms]![RunReports]![cmbRunRptsFY],2))*[1])))-1

But I wonder if there is a better way?

Cheers

 

[KenReay]

Hi

You did not say that one of the controls was on a form, so yes you need to have the form open, and you need to qualify the control name with the name of the form (eg Forms!MyForm!MyControl). The comments regarding referencing a control or column in a table by name or by ordinal position still hold true (eg Me.MyControl, Me("MyControl&quot, Me(0) )

Anyway, pleased you got it to work

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK