Condition in IIF function doesn't work (1 Viewer)

Alvein17

New member
Local time
Today, 16:42
Joined
Sep 2, 2021
Messages
29
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.
1631588521168.png


what's wrong with my querry?

1631588572964.png


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

thank you before,

warm regards
Alvein
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:42
Joined
Oct 29, 2018
Messages
21,357
Hi. It might be easier to help you if you could post a sample copy of your db.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:42
Joined
May 7, 2009
Messages
19,169
Shift: IIF([aoRemarks1] Like "*NON SHIFT","NON SHIFT",[Xshift])
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:42
Joined
May 7, 2009
Messages
19,169
what version is your Access?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2013
Messages
16,553
looks like you have applied arnels solution - otherwise what is the problem?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:42
Joined
May 7, 2009
Messages
19,169
add another "*" (star) at the End:

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

Gasman

Enthusiastic Amateur
Local time
Today, 09:42
Joined
Sep 21, 2011
Messages
14,038
Why not have IIF(aoremarks1) ="NON SHIFT",aoremarks1,xshift)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:42
Joined
Feb 28, 2001
Messages
26,996
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:

Code:
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

New member
Local time
Today, 16:42
Joined
Sep 2, 2021
Messages
29
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:

Code:
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
 

Users who are viewing this thread

Top Bottom