Percentage Expression Help...

[forbudt4u]

Okay, I am new to Access expressions, but have many years of experience with Excel.... so I can get this to work quite easily in Excel, but can't figure it out in an Access Report I have built. I have included a screen-cap of the report I have made and as you can see, the % Missing (by $) in the far right column is blank, as this is where I'm having my issue. The report is grouped by Holder with the details hidden as I am only producing a summary report. I get the correct corresponding count and value for all of the listed Holder's Assets. What I'm trying to do is this...

Each Holder has Assets that fall into different status' (such as assigned, damaged, missing... etc). I need the expression to sum the total dollar amount for just the assets that user has listed as MISSING status and then dividing that amount by the total asset value so I can get a percentage of that holders missing assets based on dollar amount. The field names from my table is as follows:

[Holder] - Self explanatory... report already grouping the correct way


[Status] - "Missing" Assets only
[Current Value] - Dollar amount assigned to the respective asset's record
[Asset Value #] - The Report Field that already calculates the TOTAL dollar amount for all asset's assigned, regardless of status for the given Holder

Any guidance you guys could give would be appreciated!

[ranman256]

add a text box with the formula:
=txtBoxAmt / txtBoxAssetVal

set the format property to %,
and the # decimals.

Note, the formulas use TEXT BOX NAMES, not field names. (but they could both be the same name)

[June7]

Expression like:

=Sum(IIf(Status="Missing", [Current Value], Null))/Sum([Current Value])

Advise not to use spaces nor punctuation/special characters in naming convention.

[forbudt4u]

June7, yours did it... thank you!!! I actually changed it to =Nz(Sum(IIf(Status="Missing", [Current Value], Null))/Sum([Current Value]),0) because I forgot to mention I needed to it say 0% if the holder didn't have missing assets. Also, in regards to your advice on the spaces and characters... I totally agree it's good habit, but my grammar OCD'ness gets the best of me

Ranman... appreciate the terminology lesson - I forget I'm in a different world with Access, will have to get used to this.