using IF in expression builder 1

[davyre]

Hi,

I need help to write IF clause in the expression builder in a Report:
IF the value is TRUE then SHOW "RECEIVED"
ELSE (value is FALSE) then SHOW "PENDING"

=IIf([Status],"Received","Pending")

the problem is it always shows Received, which it should not. And in the textbox that I trying to work on, it shows warning sign saying "Circular Control"

any help on this? Thank you

 

[dhookom]

Make sure you change the name of the control to txtStatus.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

Hi dhookom, can you explain why? Changing the control name did not affect any of the result

 

[dhookom]

Some times designers will add the text box for Status and it will have the name of status. They will then change the control source to something like I suggested. This creates a circular reference since status can refer to both the field and the control which are not the same.

I expect you have something similar happening in your report.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

not sure but,
I have a listbox. The value of the listbox will be populated using query. Now I am in the query builder, that selects fields from table called TmpTblOrderUnitPartStatus. The field that I want to change is called "Status".
So what I want is that in the listbox will not showing True/False, but Received/Pending instead.

Now the error saying "This expression is typed incorrectly, or it is too complex to be evaluated. Try simplifying the expression by assigning parts of the expression to variables".

heres my SQL:

SELECT TmpTblOrderUnitPartStatus.OrderUnitPartID, TmpTblOrderUnitPartStatus.OrderID, TmpTblOrderUnitPartStatus.PartVendor, TmpTblOrderUnitPartStatus.UnitID, TblUnits.BSUnitID, TmpTblOrderUnitPartStatus.PartNumber, TmpTblOrderUnitPartStatus.PartDesc, TmpTblOrderUnitPartStatus.TotalQty, TmpTblOrderUnitPartStatus.Status
FROM TblUnits INNER JOIN TmpTblOrderUnitPartStatus ON TblUnits.UnitID = TmpTblOrderUnitPartStatus.UnitID
WHERE (((TmpTblOrderUnitPartStatus.OrderID)=[Forms]![FrmWholeDelivery].[OrderIDBox]) AND ((TmpTblOrderUnitPartStatus.PartVendor)=[Forms]![FrmWholeDelivery].[VendorBox])) OR (((TmpTblOrderUnitPartStatus.Status)=IIf("status","received","pending")));

 

[dhookom]

This looks wrong:

OR (((TmpTblOrderUnitPartStatus.Status)=IIf("status","received","pending")));

Shouldn't the "status" be [status]?
If it is supposed to be the Status field then it should always equal the status field unless it is null.

I would think you want to add the IIf() in the field select like:

SELECT TmpTblOrderUnitPartStatus.OrderUnitPartID,
 TmpTblOrderUnitPartStatus.OrderID, TmpTblOrderUnitPartStatus.PartVendor,
 TmpTblOrderUnitPartStatus.UnitID, TblUnits.BSUnitID, TmpTblOrderUnitPartStatus.PartNumber,
 TmpTblOrderUnitPartStatus.PartDesc, TmpTblOrderUnitPartStatus.TotalQty, TmpTblOrderUnitPartStatus.Status,
 IIf(Status,"received","pending") As strStatus
FROM TblUnits
 INNER JOIN TmpTblOrderUnitPartStatus ON TblUnits.UnitID = TmpTblOrderUnitPartStatus.UnitID
WHERE (((TmpTblOrderUnitPartStatus.OrderID)=[Forms]![FrmWholeDelivery].[OrderIDBox]) 
 AND ((TmpTblOrderUnitPartStatus.PartVendor)=[Forms]![FrmWholeDelivery].[VendorBox]));

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

great! Thanks for the help!