Calculation problem in Query

[cvgope]

I have created a query for "Party Payments" in which I have given formulas for obtaining Advance, 1st RA Bill, 2nd RA Bill, Pro-rata Bill, Final Bill Payable values. In which one calculation field called "Advance Paid Gross (Amount paid Upto Pro Rata Bill)" i ve given calculation like this: CCur(NZ([Party Payments].[Advance Bill Value],0)+NZ([1st RA Bill Accounted]+[2nd RA Bill Accounted]+[3rd RA Bill Accounted]+[Pro Rata Accounted],0)).
The thing is when I enter values for all these Advance, 1st RA, 2nd RA, Pro-rata values it is showing the right values in the "Advance Paid Gross(Amount paid Upto Pro Rata Bill)" field whereas if I enter the values only for the Advance & 1st RA Bills sections, the caluculation field is showing "Zero" for "Advance Paid Gross (Amount Paid upto Pro Rata Bill).

Pls. advise on this.

 

[DCrake]

You need to wrap the Nz() around each element of the equasion.

CCur([B]Nz[/B](Advance Bill Value],0)+[B]Nz[/B]([1st RA Bill Accounted],0)+[B]Nz[/B]([2nd RA Bill Accounted],0)+[B]Nz[/B]([3rd RA Bill Accounted],0)+[B]Nz[/B]([Pro Rata Accounted],0))

David

 

[cvgope]

But still I am not able to get the result. I want to attach the file for ur reference. where can i find the attachment tab. Pls. reply

 

[cvgope]

Still i am not getting the result. Is there any other way of getting the result.
Pls. help ASAP.

 

[DCrake]

Look for the little paper clip icon above the message text area and follow the prompts.

David

 

[cvgope]

Attached word file in which i have given calculations for obtaining 'Advance Bill, 1st RA Bill, 2nd RA Bill, 3rd RA Bill, Pro rata Bill, Final Bill Payable' values. I dont know whether it is the right method of getting the result or not. pls. go thro and help me out. Is there any other way of doing this?

 

[DCrake]

I have had a quick glance at the sql and I think a snippet from the mdb would be more beneficial. can you supply an extract that contains the bit that does not work.

David

 

[cvgope]

Sir, attached pls. find the Excel sheet where I have entered Bill details for 3 Invoices. I want to send the total Database to you but it is 48 MB in size. Pls. advise.

 

[cvgope]

Sir, I am attaching the same Excel File with modifications for clear understanding of the problem. The highlighted ones are the missing values which i am not able to get. I have created 5 Nos. Subforms for Data Entry Purpose which you can find in the attached file in order. The SQL Query is already attached.

 

[cvgope]

Anyone can help me out with this problem. Its urgent. Dont think otherwise.

 

[cvgope]

I will be greatful for the help. This is very urgent.

 

[Edgarr8]

In the statement below,
CCur(NZ([Party Payments].[Advance Bill Value],0)
Are you telling it to come back as zero if the value is not set to currency?
and do all of your fields have the same data type?

 

[cvgope]

In the statement below,
CCur(NZ([Party Payments].[Advance Bill Value],0)
Are you telling it to come back as zero if the value is not set to currency?
and do all of your fields have the same data type?

No. What I need is the total of all values of "Advance Paid Gross" in all the stages i.e., 1st RA Bill, 2nd RA Bill, 3rd RA Bill etc.. is to be displayed in the Final Bill whether I enter all the bill values or skip some bill values.

I am getting the total of the above only when of entering bill values of all the bills. But when I skip entering any bill value in between Advance Bill & Final Bill, the total of advances paid is not showing in the final bill - Advance paid field.

I have set the currency to fixed.

Pls. see the attachment in Excel format where I have entered data for 3 Nos. Bills. The fields in Bold are the problem.

Pls. suggest what to do in this case.