Condition in IIF function doesn't work

[Alvein17]

Hello Guys,

Good day,

i have some trouble in my access table. my query:

Shift: IIF([aoRemarks1]="NON SHIFT","NON SHIFT",[Xshift]), as you can see in the table result below in Shift column not showing the right result.

what's wrong with my querry?

and i have some problem with same cases. can you correct my querry?

thank you before,

warm regards
Alvein

 

[theDBguy]

Hi. It might be easier to help you if you could post a sample copy of your db.

 

[arnelgp]

Shift: IIF([aoRemarks1] Like "*NON SHIFT","NON SHIFT",[Xshift])

 

[Alvein17]

Hi. It might be easier to help you if you could post a sample copy of your db.

here i attach my db

 

[Alvein17]

Shift: IIF([aoRemarks1] Like "*NON SHIFT","NON SHIFT",[Xshift])

Hello arnelgp,

i was trying but didn't work. can you help me to check my db?

 

[arnelgp]

what version is your Access?

 

[CJ_London]

looks like you have applied arnels solution - otherwise what is the problem?

 

[arnelgp]

add another "*" (star) at the End:

Shift: IIF([aoRemarks1] Like "*NON SHIFT*","NON SHIFT",[Xshift])

 

[Gasman]

Why not have IIF(aoremarks1) ="NON SHIFT",aoremarks1,xshift)

 

[Alvein17]

what version is your Access?

i'm using access 2016 version.

 

[The_Doc_Man]

Technically, to your original question, "What's wrong with my query?"

Nothing is wrong with the original query because in your sample, you never have [aoRemarks1] equal to just "NON SHIFT" - though that field might contain "NON SHIFT" as part of what is there. The equals-sign says the IIF expression matches EXACTLY or it is not a match. Since the fields contain MORE than "NON SHIFT" it is not a match, so you ALWAYS get the XMatch string - which appeas to be empty.

If I read this correctly, you might be able to do something as simple as:

SELECT FiscalDate, SchedSchedNum, aoRemarks1, XShift, IIF( INSTR( 1, aoRemarks1, "NON SHIFT", vbTextCompare ) > 0, "NON SHIFT", aoRemarks1 ) FROM Shift ;

That is, ask it to look for the sub-string in that longer string. The vbTextCompare style of comparison ignores case by default and will return 0 if the sub-string is not found in the main string.

 

[Alvein17]

Technically, to your original question, "What's wrong with my query?"

Nothing is wrong with the original query because in your sample, you never have [aoRemarks1] equal to just "NON SHIFT" - though that field might contain "NON SHIFT" as part of what is there. The equals-sign says the IIF expression matches EXACTLY or it is not a match. Since the fields contain MORE than "NON SHIFT" it is not a match, so you ALWAYS get the XMatch string - which appeas to be empty.

If I read this correctly, you might be able to do something as simple as:

SELECT FiscalDate, SchedSchedNum, aoRemarks1, XShift, IIF( INSTR( 1, aoRemarks1, "NON SHIFT", vbTextCompare ) > 0, "NON SHIFT", aoRemarks1 ) FROM Shift ;

That is, ask it to look for the sub-string in that longer string. The vbTextCompare style of comparison ignores case by default and will return 0 if the sub-string is not found in the main string.

Hello The Doc Man,

thank you, yes i mean contains not equal. i would try first and i will update the result