Creating Expressions in Access (2010) Forms

Topic

New Reply

I am having problems creating expressions in Access 2010 forms.
I either get a “type mismatch” error in Design View or on the form it displays “#Type!”.

What I want to do is to create, in the section footer (not the report footer) an expression as follows:

__ items purchased from ________________ at a cost of __________________

The first blank would be the sum of the quantity for that vendor.
The second blank would be the vendor’s name.
The third blank would be the total cost for that vendor.

Here’s what I’d like the expression to look like on the form:

6 items purchased from ABC Train Shop at a cost of $ 123.45

The first blank is from my field “Qty”
The second blank is from my field “Sold_by”
The third blank is from my field “Calc_Total”

The reason for putting all this in one expression is because the vendor’s name varies in length and I’d like the total cost to be immediately after the vendor’s name – not way across the page.

I can format the individual control sources, but when I try to put them together in one text box, that’s when I get the errors.

Thanks

Reply | Quote

Replies

The Controlsource for a single textbox would be:

=[Qty] & ” items purchased from ” & [Sold_By] & ” at a cost of ” & format([Calc_Total],”currency”)

Reply | Quote

The Controlsource for a single textbox would be:

=[Qty] & ” items purchased from ” & [Sold_By] & ” at a cost of ” & format([Calc_Total],”currency”)

Since the above control source gave me only the qty for the last item, I changed [Qty] to Sum([Qty]). Now it gives me the total quantity for the vendor.

How do I format the currency portion? In other places I use the format of “$ #,##0.00” so it looks like $ 123.45. I’m presuming I would put that somewhere in the control source text box, but where?

Thanks!

Reply | Quote

Figured out how to format the currency portion of the text box.

I need to add the sum function somewhere in the Calc_Total portion as it is currently only displaying the Calc_Total of the last item.

Currently the text box has this as the Control Source:

=Sum([Qty]) & ” items purchased from ” & [Sold_By] & ” at a total cost of ” & Format([Calc_Total],”$ #,##0.00″)

I know it should go somewhere after the last “&”, but have tried different places and it keeps giving me errors.

Reply | Quote

What was wrong with: format([Calc_Total],”currency”)

Reply | Quote

format([Calc_Total],”currency”) only formatted the last item in the group. “Calc_Total” is the purchase price plus shipping & handling or sales tax for each item. I need to sum the total of all the records in a group.

Reply | Quote

Further update!

The text box is as follows:

=Sum([Qty]) & ” items purchased from ” & [Sold_By] & ” at a total cost of ” & Format([Purch_Total],”$ #,##0.00″)

Qty is correct; however, the Purch_Total is only including the Purch_Total from the first item – regardless of how many items are in the group.

I need to Purch_Total to be the total Purch_Total of all the items in the group.

I need to add the sum function to the Purch_Total part of the expression, but no matter where I place it, I get an error that says the expression is incorrect.

Reply | Quote