Link form text fields into a report

[the_fuzz]

Hi All,

I am using 2010 and on my form I have text fileds with expressions (calculations) in the form which are workiing perfectly. I now would like to generate a report to include these values of the text boxes per record so I am using for example =[Forms]![Client Information Main Form]![Text322] in the control source propert of text boxes in the report.

On average I have approximately 25 results at the same time when generating the report but for some reason all of the results in these linked text boxes are the same per record so what I need is for the report results to generate the specified answer per record.

Any assistance would be appreciated as I have searched this forum and cannot seem to find the answer which I am sure is going to be a simple one.

Thanks

Richard

 

[JHB]

You have to do the calculations in the query for the report, or in the report it self.
=[Forms]![Client Information Main Form]![Text322], is only giving you one value/result, so you can't do it on this way.

 

[the_fuzz]

You have to do the calculations in the query for the report, or in the report it self.
=[Forms]![Client Information Main Form]![Text322], is only giving you one value/result, so you can't do it on this way.

Thanks JHB. So you mean I need to put the calculation expression in the criteria in the query for the report as an expression?

 

[JHB]

You have to do the calculation in the query or in the report.
Why do you want to have it as a criteria?

 

[the_fuzz]

You have to do the calculation in the query or in the report.
Why do you want to have it as a criteria?

Sorry pal. I do not want the calculation as a criteria. Where do I insert the calculation as an expression in the query?

 

[JHB]

An example, the query creates a column with the name "Calc" where the value from [SomeValue] and [OtherValue] is multiplied:

SELECT Sometext, SomeValue, OtherValue, [SomeValue]*[OtherValue] AS Calc
FROM MyTable;

For futher help we need to see your query and how you want the calculation.

 

[the_fuzz]

An example, the query creates a column with the name "Calc" where the value from [SomeValue] and [OtherValue] is multiplied:
For futher help we need to see your query and how you want the calculation.

Hi

Thanks for the reply. I am using a selct query and the textboxes I want to use the calculation for are unbound so how do I select one to relfect in the query. If you are able to show me how to do this it would be great otherwise I will upload the simplified database. I am using 2010 so let me know which version you are using so I can upload the correct one Thanks for your help so far.

 

[JHB]

I'm using 2010 - zip your file else you can't post it.

 

[the_fuzz]

I'm using 2010 - zip your file else you can't post it.

Thanks JHB. I have attached my zipped 2010 database where I have only included the necessary queries, forms and the report I am battling with.

On the form you will see at tab called "Comm" so this is where the calculated textboxes are used with their respective formulas. These are the formulas I need to appear in the report.

The query called commission veification is the one linked to the report.

Please let me know if you need anything else pal.

 

[JHB]

Hi, could you please control if Gross commi ... for policenumber 1157196824 is correct, I get a different value.

 

[JHB]

Here is the database back, I've added the fields.

 

[the_fuzz]

Here is the database back, I've added the fields.

Yu are an absolute star pal. I cant thank you enough

 

[JHB]

You're welcome.
Did you control Gross commi ... for policenumber 1157196824 is correct, I get a different value?

 

[the_fuzz]

You're welcome.
Did you control Gross commi ... for policenumber 1157196824 is correct, I get a different value?

Well sptted. The calculation included is GrossCommission: IIf([age]>26,[new premium]*12*0.85,[new premium]*12*0.0325*[age]) where it should be GrossCommission: IIf([age]>26,[new premium]*12*0.85,[new premium]*12*0.0325*[term])

The last part of the calculation should be [term] and not [age]

Thanks again. Rich

PS - JHB are you able to also include the term calculation in the query you did for me to link up with the Term in the report?

 

[JHB]

Now term is in the query.

 

[the_fuzz]

Now term is in the query.

Thanks agan JHB. Works perfectly. Have a great weekend

 

[the_fuzz]

Thanks agan JHB. Works perfectly. Have a great weekend

JHB, one last question. In the [age] calculation filed where this formula is in - =IIf(IsNull([Forms]![Client information main form]![Policyholder id number]),0,DateDiff("yyyy",DateSerial(Left([Forms]![Client information main form]![Policyholder id number],2),Mid([Forms]![Client information main form]![Policyholder id number],3,2),Mid([Forms]![Client information main form]![Policyholder id number],5,2)),Now())+Int(Format(Now(),"mmdd")<Format(DateSerial(Left([Forms]![Client information main form]![Policyholder id number],2),Mid([Forms]![Client information main form]![Policyholder id number],3,2),Mid([Forms]![Client information main form]![Policyholder id number],5,2)),"mmdd")))

The above calculation generates the persons age based on the ID number and todays date,

My question is that if I open this record in 2 years time will the [age] value change which is what I DONT want.

Is this also part of your expertise?

Rich

 

[JHB]

Ofcourse I think so, is the client in 2 years - not 2 years older?
But you can check it by changing you computer system time, and the refresh/requery the form. I can't check it here, because the system time by me is coming from a central server.

 

[the_fuzz]

Ofcourse I think so, is the client in 2 years - not 2 years older?
But you can check it by changing you computer system time, and the refresh/requery the form. I can't check it here, because the system time by me is coming from a central server.

Thanks. I cahnged the system time and the [age] did nfortunately change.

Is there anyay to set the claculation that the [age] wont change as this [age] is used to calculate commission on a policy and if I want to reference this in years to come I need the [age] to be the same as when I first entered the information.

Can you help?

 

[JHB]

From when must the age be locked? When the client is registreted or when?