Forum Discussion
Kindly help me write an expression in my MS ACCESS report
I have following fields in the report
'Item quantity required per piece' bound field- number field : Field name in the report is [QUANTITY REQUIRED]
'unit'- text filed- bound field: (Unit can be 'GM'-for gram,'KG'- Kilogram,'PCS'- Pieces,'ML'-Milli Liter,'LIT'- Liter)- field name in the report is [QUANTITY UNIT]
'total production': number field: bound field: field name in the report [Text45]
Total item required for the production : Unbound : Field name in the report is [Text371]
For field [Text371], i want to create an expression . If the product of [QUANTITY REQUIRE] and [Text45] is greater than 1000 and if the unit is 'GM' then i want [Text371]=[Text45]*[QUANITY RQUIRED]/1000 &" "&"KG"( basically i want to convert the result to Kilogram if it's more than 1000), otherwise, the result should be [Text371]=[Text45]*[QUANTITY REQUIRED] & " " & [Units] (basically if the product is not more than 1000 and the unit is not gram , i want the result to be just the product and the respective unit ) . I wrote the expression something like below, but it didn't work :(. Could someone please help
IFF [Text45]*[QUANITY RQUIRED]>1000 & [QUANTITY UNIT]="GM", [Text371]=[Text45]*[QUANITY RQUIRED]/1000 &" "&"KG",[Text371]=[Text45]*[QUANTITY REQUIRED] & " " & [Units]
I think you'll be ahead creating the calculated fields in the query that provides records to the report, rather than relying on calculations in controls on the report itself.
That said, you refer to default control names on the report (e.g. [Text45], etc.). Those names add a layer of ambiguity to the discussion, so renaming them to reflect their actual contents would be really helpful going forward.
Also, it looks like you have a value list for Units somewhere. It's probably more efficient to create a look up table of UOM with a Primary Key and two value fields, one for FullName and one for Abbreviation. That way you can add the lookup field to the query for the report and just retrieve the appropriate values (full or short) for the controls on the report.
The same is true for the conversion to KG from GM. Do that in a calculated field in the query and simply return one field to the report with the appropriate value already available.
Sometimes we have to step back and rethink the process to see where we can gain efficiency by pushing values down into the underlying source; in this case the query.
You appear to have use an ampersand rather than the Boolean AND operator. If you are assigning a value to the Text371 control in code try this as the ControlSource property of the Text371 control:
=IFF [Text45]*[QUANITY RQUIRED]>1000 AND [QUANTITY UNIT]="GM", [Text45]*[QUANITY RQUIRED]/1000 & " KG",[Text45]*[QUANTITY REQUIRED] & " " & [Units]
Or if [Text371] is a bound control and you are assigning the value to the control in code Try this:
[Text371] = IFF [Text45]*[QUANITY RQUIRED]>1000 AND [QUANTITY UNIT]="GM", [Text45]*[QUANITY RQUIRED]/1000 & " KG",[Text45]*[QUANTITY REQUIRED] & " " & [Units]
