CODEWITHSUNDEEP
ms-accessreportms-access-2010
JettyJetty
JettyJetty

Reputation: 71

I'm getting a #Type! error when using operators in Access report (Access 2010)

I'll do my best to describe my problem. I'm modifying a report in Access 2010. I have a text box in my detail section that displays the weight of a pallet with boxes on it. =[PalletWeight].

The PalletWeight field is generated from a SQL query that grabs this information from our database. As it so happens in the database that field appears like so: 100.000000000. I don't know the data type for that field in the database because looking isn't as simple as opening SQL Server, but =CDbl([PalletWeight]) does work so unless someone tells me they need to know I didn't obtain that information for this question.

The pallets are grouped by item. So item 1 might need to be carried on 3 pallets, item 2 might need 4, etc...

As mentioned earlier, I used CDbl on the textbox to convert the value to appear as 100.00 on the actual report. I need to add a text box that calculates percentage. For each item, the total weight of the pallets needed per item is a certain percentage of the grand total weight of all pallets produced.

When I attempted do something like the following I get the #Type! error.

=Format([PalletWeight / Sum(PalletWeight) * 100, "Percentage")

I've also tried to refer to the text box by name (name reference) to perform operations on its value instead of the value contained in the text box.

=Format([TextBox1] / [Textbox2] * 100), "Percentage").

This report has various grouping sections. I've tried using hidden text boxes to hold the values I want to manipulate and referencing those but it seems as soon as I use arithmetic I get the #type!.

I'm using the expression builder in the properties Control Source section. Any help would be greatly appreciated.

Upvotes: 0

Views: 1363

Answers (1)

dbmitch
dbmitch

Reputation: 5386

You either need to modify your query to add another calculated field say dblPalletWeight =CDbl([PalletWeight]) and then use that in your formula

=Format(dblPalletWeight / Sum(dblPalletWeight) * 100, "Percentage")

Or modify your formula to do the conversion on the fly

=Format(CDbl([PalletWeight]) / Sum(CDbl([PalletWeight])) * 100, "Percentage")

Upvotes: 1

Related Questions