Builder to Multiply from new Query in Form Design Veiw

[RuffianRan]

I select my Query from the Exp Elements list, then double click on Quantity and UnitPrice from Exp Categories and get:



[qryInvoiceTotal]![Quantity] «Expr» [qryInvoiceTotal]![UnitPrice]

Then I put the multiplier in the middle and the equal sign in the beginning and it doesn't work. I tried putting it in without the qry in the front but that didn't work either.

I was instructed to create this Query just for this New Field to be put into the Form while in Design View.

Any help would be a blessing, thank you!

[vicsaccess]

RuffianRan, back up and give a little more background. what are you wanting to do, populate a text box on a form?

[RuffianRan]

RuffianRan, back up and give a little more background. what are you wanting to do, populate a text box on a form?

The instructions say: "Use the Builder to add a new field that displays the Quantity field multiplied by the UnitPrice fields and name this field InvAmt."

This is right after we created the Query qryInvoiceTotal that has both the Quantity & UnitPrice fields. I'm putting this into Design View with the Label "Total of Invoice:" "insert what I'm trying to calculate for the unbound field"

[vicsaccess]

ok, not sure what "instructions" you are referring to and i'm not accustomed to using the builder since for me its easier to use the control source of the text box and enter what i need. with my limited use of the builder i see that your order of doing things is off. click on [qryInvoiceTotal]![Quantity] then your multiplier then [qryInvoiceTotal]![UnitPrice] then close it. done. In mine it automatically puts the "=" in for me. is this for a class project or for yourself? there's easier ways, is the quanity and unitprice fields in your form? if so then enter in the rowsource of the unbound text box =quantity*unitprice

[RuffianRan]

I've typed in InvAmt: [Quantity]*[UnitPrice] which is what another student says is what to type, and what I thought from the beginning, but get and error message: "The expression you've entered contains invalid syntax"

If I go through the Builder and select the Query then double click on 'Quantity' add * double click 'UnitPrice' it puts this in the box:
[qryInvoiceTotal]![Quantity]*[qryInvoiceTotal]![UnitPrice] and it gives me a result of #Name?

[RuffianRan]

Yes, it's for a class. Sorry, didn't see your reply before I posted again. I'm new here. Mine does automatically but the = in front, I've noticed, not sure why the book tells us to do it. I'm not sure what we have to have showing as our formula for the class but at this point I don't care, just so it works and I can add the Subform. I'll try the last thing you said

[RuffianRan]

Nope, still get #Name? Is there something wrong with my program???

[vicsaccess]

you have two objectives here that need to be done separately. first the builder, that error is common if you have a space somewhere that you aren't suppose to such as between your fields and the multiplier. it should look like

=[qryInvoiceTotal]![Quantity]*[qryInvoiceTotal]![UnitPrice]

when you are done if you look at the row source of the text box, no spaces between anything. if that does not fix it then look at this http://access.mvps.org/access/forms/frm0018.htm or google "access #NAME" , there's lots of other people that have had the same issue.
after that is working then go to the properties of the text box and rename it "InvAmt" on the other tab.

[RuffianRan]

I'm so sorry for wasting your time. I was totally missing the point here. I'm supposed to be putting this field in the Query itself in Design View, not the Form. Oh, how much other schoolwork I could have got done if I weren't so dense! Not sure why I'm going back to school...

Thank you very much for your help and patience!
God bless

[vicsaccess]

Glad to help, that makes more sense. One thing about databases that I've learned is that there is as many ways to do something as there are people trying to do it. that's why i was questioning it earlier that there are easier ways. please mark as solved

[RuffianRan]

Can I ask you a new question?

[vicsaccess]

Sure, but if it has to do with your query it may be better to start a new thread in the query section so that everyone can learn and help.

[RuffianRan]

and how do you mark it as solved?

[vicsaccess]

use the thread tools above

[RuffianRan]

Actually, it's with adding a subform to my form. When I get to the part in the Wizard about linking it to the rest of the form the option is to "Choose from list" which is "Show qryInvoiceTotal for each record in tblCustomer using CustID" but when I try to click "Next" I get a message

"The link you selected can't be used. This link was created based on relationships between source tables.To use this link, you must go back to the previous page and include the missing fields."