Calcualtion Field

[madvalpreston]

Hi

I have a subform with 10 fields for prices, if I just fill in the first 3, my total field remains balnk unless I fill in all 10 with somthing (0.00 for example). it then toals up no problem.

Is it posisble to amke the calcualtion even if the other fields are blank. This is my control source for the Total

=[building price1]+[buildingprice2]+[buildingprice3]+[buildingprice4]+[buildingprice5]+[buildingprice6]+[buildingprice7]+[buildingprice8]+[buildingprice9]+[buildingprice10]

Thanks

 

[PHV]

=Nz([building price1],0)+Nz([buildingprice2],0)+ ... +Nz([buildingprice10],0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

You can probably use NZ functions (null to zero).

=NZ([building Price1],0) + NZ([building Price2],0) ...

However, in the bigger picture your data looks non-normalized. These are all price fields related to some record. So normally done something like

tblCosts
costType
costName
costAmount
foreignKeyToMainTable

 

[madvalpreston]

Hi

Thanks for the replies, bsically currently I ahve it setout like this

Bulding descrition Building price
builddecp1 buildprice1
builddesc2 buildprice2

et..... all the way up to 10 possible ones

The building description is a free test field for the user to add in their own description. I have deafualted all the files to have 0.00 and it adds up ok....

It is now the report I have problems wiht as it shows all 10 lines even though they are empty... do I need to open a new thread or any ideas on this.

It is listed int eh same way but I need it not to show any blank lines where the fileds are blank

Thanks

Thanks

 

[dhookom]

IMO, take MajP's advice and normalize your table structures. I wouldn't continue to work with this table structure.

Duane
Hook'D on Access
MS Access MVP

 

[madvalpreston]

Hi

The structure is I have a quotetable and a buildingtable for this.
The quote form as the subform for the buildings and we could potentially add up to 10 buildings or less of course.

I dont understand where or how to use the suggestion

=NZ([building Price1],0) + NZ([building Price2],0) or how then this relates to report side of things.

I would appreciate any step by step explanation and give it a go

Thanks

 

[randy700]

I dont understand where or how to use the suggestion

Use it in place of the formula that isn't working properly.


Randy

 

[traingamer]

The NZ(1stparm, 2ndparm) function evaluates the expression (or variable or field/column) in the first position. If the expression evaluates to a null or zero, it returns the value in the second position.

Thus

NZ([building Price1],0)

evaluates [building Price1] - if it is null or zero, it uses the zero (the second parameter); otherwise it uses the value of [building Price1].

The reason for this is that for instance, [blue]1 + 2 + 3 + null = null[/blue] (any expression with a null evaluates to null - the cause of your original problem).

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[madvalpreston]

Hi All

I have this in the code now

=[build1desc] & Nz( ([building price1],0)+(Chr(13) & Chr(10))) & [build2desc],0) &Nz( ([buildingprice2]+(Chr(13) & Chr(10))

I am trying to add the description to the price, but it does not liek what I am doing, can you advise how the cirst bit of code should be.

Thanks

 

[dhookom]

Your intial posting was simply attempting to add the numbers across to get a total. At what point did this change to adding text fields, carriage returns, and line feeds?

If your table structure was corrected, this would probably be easier.

Duane
Hook'D on Access
MS Access MVP

 

[madvalpreston]

Hi

Yes sorry I did not explain myself very good to start with and the table strucutre appears ok and links in the the quote form ok, if I add a new record it now adds up ok.

My problem now is getting in the report and not showing the balnk fields.

Thanks

 

[madvalpreston]

Hi All

I have taken a think on how to design the databse better and have now created a new table

I have a quote form and a sub form for quote lines within the quote form.
On the sub form I have 4 main fields

ID

Building description

Price

Report price


The quote may have many lines of bulding prices, so I have used an Add button for this in the sub form

On the final line I also put in the report price.

I now need a Total price field adding in somehow, but cant figure out how to add it in.
The prices to add have the quote id next to them, so I need a total of all prices and report price where it is equal to the quotenumber it is attached to (hope that makes sense)


Hope this is a better way to attack it , so any advice on how to toal this would be great.

Thanks

 

[dhookom]

You can sum any numeric field/expression in a report or group header or footer section with a control source like:

=Sum([Your Field or Expression Here])

Duane
Hook'D on Access
MS Access MVP

 

[madvalpreston]

Hi

I really need it to add per the quotenumber it is linked to

I have added a textbox in with control source of

=Sum([price]+[reortpriece])

inot the form But this adds it for all records.

So when I put it in as a sub form within the quote it is only adding up from the quoteid it is attached to

Any ideas please

 

[madvalpreston]

Hi all

I have added the subform now into the quote table and it links and adds u[p as per the quote. However I run into another problem on the subreport, it list like this

Building 1 £1.00
Report Price £0.00

Building 2 £1.00
Report Price £0.00

Building 3 £0.00
Report price £1.00

Total £3.00

Not to pretty to look at, so what I need on the sub report is to only see values with amounts in and not repeat the report price, there will only be one of these and show it as the lasts entry.

Thanks

 

[dhookom]

Your report prices are not all the same in your example. You have done a good job of normalizing but why still two price fields?

You can set a control source to something like:

=IIf(Nz([Your Field Name],0)=0,Null,[Your Field Name])

Then set the Can Shrink to Yes.

Duane
Hook'D on Access
MS Access MVP

 

[madvalpreston]

Hi

The reasoning for the 2 prices are that the Building ones are free text fields and can be called anything, but there must always be a report price field in no matter what, but of course we only want it to appear once so was not sure how to do this.

I have taken the report price out now and decided to either the user types in report price in free text, or we may go down the way of having a drop down combo box and they select from a list of things.

Thanks for your patience on this, I certainly learnt to think through table structure.

Thanks

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886