Total based on answer in textbox 1

[niteraven]

I have the following fields:

txtanswer = has 3 text choices (warranty, optional, nonwarranty)

extcost = sum [partprice] *[qty]

Totalcost = i want this to give me the total of all the nonwarranty extcost.

This is in a subform, and i have a report that all of this goes to.

I tried an iif statement and i couldnt get it to work.

ANy help would be appreciated.
Raven

 

[dhookom]

Assuming txtAnswer is a field in your report's record source, try use a control source of:
=Sum(Abs([txtanswer] = "nonwarranty") * [partprice] *[qty])


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]

 

[niteraven]

hi dhookum

that code gives me an #error.

I forgot to say that the txtanswer field is in the subform as a combobox (value list) would this make a difference? It stores in the table as text, not numeric.

Thank you
Raven

 

[dhookom]

Can you try again with a better explaination of significant fields, controls, report sections, etc. Is txtAnswer a field or control and is it actually in a subform or subreport?

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]

 

[niteraven]

Ok here is a more detailed explaination of what i am trying to do.

I have a form, (frmrepair) that has a subform (subrepair). The form is based on one table (one), the subform another (many). In the subform the following fields are on there:
partno = text
partdesc = text
partqty = number
partprice = number
extcost = sum [partprice]*[partqty]
parttype = text, combobox:value list Nonwarranty, warranty, optional
totalcost (not in the table) = total of all regardless of parttype. (in the form footer)

I need a total cost of all the nonwarranty parts.
i need a total cost of all the warranty parts.
i need a total cost of all the optional parts.

I have a report that is generated from this form. I thought i could on the fly separate the totals as the report loaded, that didnt work. But on the report, i need the breakdown of each parttype total.
So now i am trying to get it to work on the form to send totals to the report.

Thank you for your time
Raven

 

[dhookom]

I don't see any field that might link to the record source of your main form/report. Just create a totals query that groups by some parttype and your linking field and sum your [partprice]*[partqty]. Use this as a subreport (if for a report) or subform (only if needed on a form).


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]

 

[niteraven]

dhookom,

Yes there is a linking field between the two. It is a numeric field. (issueno)
I have tried to write a query and use that in an unbound field, but so far no luck with that either. But i am still playing around with that maybe i will get something to work.

I have used the help in access, this forums faq and the search and still have come up with a solution.

thank you for all your help!
raven

 

[dhookom]

i need the breakdown of each parttype total"

Create a totals query like:

SELECT IssueNo, PartType, Sum([partprice]*[partqty]) as PartTypeTotal
FROM [Many]
GROUP BY IssueNo, PartType;

Use this as the record source of a subreport so you can display the information in your main report.



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]

 

[niteraven]

dhookom,

SOrry it took a few days to get back to you. Thank you so very much! i based the subreport on the above query, and it worked like a champ.

good job, have a star!
raven