IIF not returning correct value (1 Viewer)

[tmyers]

In a query for my report, I am testing if a field contains a certain string, then if so, override the value to "Approved". I have tried several different variations, but it never seems to flag true.

Mfr: IIf(InStr(1,[OverrideType],"Over",1)>0,"Approved",[Manufacturer])

I have also tried:
Mfr: IIf([OverrideType] = "Override", "Approved", [Manufacturer])

Neither seem to ever be true and return [Manufacturer] even if [OverrideType] is "Override".

 

[June7]

Expressions look fine. I would have to review data. Could provide db for analysis.

 

[cheekybuddha]

Is OverrideType a Lookup Field?

 

[tmyers]

Is OverrideType a Lookup Field?

It is not. It is Short Text and is filled via a combo on a form with a value list of set values (Override, Hide)

 

[cheekybuddha]

Ok, please paint more of the picture for us.

If OverrideType the name of the combo?

You say it's a short text field. Is that a field in a table/query that the combo its bound to (ie is that the ControlSource of the combo)?

 

[tmyers]

OverrideType is the name of the field but the field is populated by a value list combo with only those two options. If OverrideType = Override, I want to "mask" the value with Approved.

Also, while mucking around with another expression, I managed to crash Access and when it came back up the expression with the InStr() now works. No idea why it didn't before.

 

[The_Doc_Man]

You say it is now working. A couple of thoughts come to mind.

1. Possible transient corruption. When you crash Access, it often makes a backup copy of your DB, which if it did so would NOT copy corruption. Check to see if there is a backup copy of the FE or monolithic file. If so, that would tend to confirm a corruption situation. Immediately make a "real" backup copy and then attempt to do a Compact & Repair. If this was an FE-only, that should do it. If it was a monolithic (i.e. non-split) DB, you might also want to check for lost data.

2. IF this kind of thing happens again, put a breakpoint on the line that would execute the query so that you can examine the fields in question to see what they actually contain. It usually helps to look at the tested field to know what it actually holds.

 

[tmyers]

Thankfully this is still very much in active development, so what data is in it is extremely small (only like 3 full sets of records). I already deleted it all out and did a compact and repair.

 

[cheekybuddha]

OverrideType is the name of the field but the field is populated by a value list combo

Sorry to be dense, is OverrideTypea field in a table/query?

If so, is that table/query the RecordSource of the form?

What is the name of the combo box?

 

[tmyers]

Sorry to be dense, is OverrideTypea field in a table/query?

If so, is that table/query the RecordSource of the form?

What is the name of the combo box?

No problem.
[OverrideType] is a Short Text field in tblFixture, which is the control source for combo box OverrideCmb with a row source set to value list and "Hide", "Override" as the values.

 

[cheekybuddha]

Ok, since this is a report, I think you should lose the combo box and, place the expression in a query to use as its RecordSource:

SELECT
  *,
  IIf([OverrideType] = "Override", "Approved", [Manufacturer]) AS Mfr,
FROM tblFixture
;

Then bind a textbox to field Mfr

 

[cheekybuddha]

To explain above, since your combo had a RowSource of only two values, it could never display the Manufacturer value.

It might have been possible to use a separate textbox placed on top which you made visible depending on the override value, but that would be more work

 

[June7]

OP says this expression is in a query and that it fails there.

OP has not confirmed combobox carried over to report. If combobox is in table design, pulling field onto report would apply combobox setup but that really isn't an issue.

I tested combobox with IIf() expression in ControlSource on report. Works just fine even if LimitToList is set to Yes. Report treats combobox like textbox.