Kindly help me write an expression in the one of the fields in my access report. I am pretty new

[vinodnair]

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]

[Welshgasman]

Try this function IIF()

Or create your own function in VBA.

What are you going to do when the item is liquid?

[June7]

=IIf([Text45]*[QUANITY RQUIRED]>1000 AND [QUANTITY UNIT]="GM", [Text45]*[QUANITY RQUIRED]/1000 & " KG", [Text45]*[QUANTITY REQUIRED] & " " & [Units])

[Bulzie]

You are on the right track and close, look at what Welch and June7 posted compared to your line. When troubleshooting syntax issues like this, try to just do 1 section at a time and see if it works, then add a 2nd argument and see if it works, etc to pinpoint where the issue is.

[vinodnair]

Got it ! Thank you so much June7

[vinodnair]

Understood. You're right , I will try step by step next time . Thank you so much Bulzie

[vinodnair]

Thank you for your message . For the liquid items, currently we don't have any production for more than 1 L . But now I got the code , I think I can modify it and use if required in the future. Thanks again Welshgasman