An IIF statement for a single character(s) Only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi please help,

In a field i have variuos characters that represents orders, there is an
indefinative number of combinations, all along the following lines;

W:WY
W:W
W:YY
W:WG
W:QGWY
:WW
:YWQ
etc...with a hundred possible options including an unlimited number of
characters.

However, basically all i want is a simple IIF statement, whereby if the
field only contains a ':' or/and 'W' character then put a 1, otherwise put a
0.

For example,
if the field contains W:WW, then i want a 1 to appear
If the field contains WWY, then i want a 0 to appear

I hope this makes sense.

Thanks
 
Thanks but i'm afraid this isn't waht i was quite after because this puts a 1
even if another character exists.

For example, in the cases of 'W:YW' the above formula (
IIf(InStr([fIELD1],"W")>0,1,0)) returns a 1. Howver because it also contains
a Y i need it to return a 0.

Thanks in advance

Douglas J. Steele said:
IIf(InStr([MyField], ":") > 0, 1, 0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sdg8481 said:
Hi please help,

In a field i have variuos characters that represents orders, there is an
indefinative number of combinations, all along the following lines;

W:WY
W:W
W:YY
W:WG
W:QGWY
:WW
:YWQ
etc...with a hundred possible options including an unlimited number of
characters.

However, basically all i want is a simple IIF statement, whereby if the
field only contains a ':' or/and 'W' character then put a 1, otherwise put
a
0.

For example,
if the field contains W:WW, then i want a 1 to appear
If the field contains WWY, then i want a 0 to appear

I hope this makes sense.

Thanks
Click to expand...
Click to expand...
 
You can embed IIF() statements inside other IIF() statements. It sounds
like you need to test first for the ":", then for the "Y". (or do you need
to test for "not-W"?)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

sdg8481 said:
Thanks but i'm afraid this isn't waht i was quite after because this puts a 1
even if another character exists.

For example, in the cases of 'W:YW' the above formula (
IIf(InStr([fIELD1],"W")>0,1,0)) returns a 1. Howver because it also contains
a Y i need it to return a 0.

Thanks in advance

Douglas J. Steele said:
IIf(InStr([MyField], ":") > 0, 1, 0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sdg8481 said:
Hi please help,

In a field i have variuos characters that represents orders, there is an
indefinative number of combinations, all along the following lines;

W:WY
W:W
W:YY
W:WG
W:QGWY
:WW
:YWQ
etc...with a hundred possible options including an unlimited number of
characters.

However, basically all i want is a simple IIF statement, whereby if the
field only contains a ':' or/and 'W' character then put a 1, otherwise put
a
0.

For example,
if the field contains W:WW, then i want a 1 to appear
If the field contains WWY, then i want a 0 to appear

I hope this makes sense.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
So if the field contains any characters except "W" or ":" return zero.

IIF([Field] LIKE "*![:W]*" OR [Field] is Null, 0,1)

I think the above will give you the desired results. Although I could
have made an error in the criteria.

If I recall correctly that says match any thing where there is a
character that is not a colon or a W in the string. If that is true
return zero otherwise return 1.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Hi -

Try this:

Public Function wfind(pstr As String) As Integer
'*******************************************
'Re:
http://www.accessmonster.com/Uwe/Fo...n-IIF-statement-for-a-single-character-s-Only

'Purpose: Return 0 if pstr contains anything
' other that "W" or ":", otherwise
' return 1
'Coded by: raskew
'Inputs: (1) ? wfind("ww:w")
' (2) ? wfind("wy:y")
' (3) ? wfind("www")

'Outputs: (1) 1
' (2) 0
' (3) 1
'*******************************************

Dim phold As String
Dim plen As Integer
Dim n As Integer

phold = Trim(pstr)
plen = Len(phold)
For n = 1 To plen
If InStr("W:", Mid(phold, n, 1)) = 0 Then
wfind = 0
Exit Function
End If
Next n
wfind = 1
End Function

HTH - Bob

John said:
So if the field contains any characters except "W" or ":" return zero.

IIF([Field] LIKE "*![:W]*" OR [Field] is Null, 0,1)

I think the above will give you the desired results. Although I could
have made an error in the criteria.

If I recall correctly that says match any thing where there is a
character that is not a colon or a W in the string. If that is true
return zero otherwise return 1.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Hi please help,
Click to expand...
[quoted text clipped - 22 lines]
Thanks
Click to expand...
Click to expand...
 
Hi, thats exactly what i'm after, unfortunately this formula appaears to be
returning a 1 for everything - any ideas.

John Spencer said:
So if the field contains any characters except "W" or ":" return zero.

IIF([Field] LIKE "*![:W]*" OR [Field] is Null, 0,1)

I think the above will give you the desired results. Although I could
have made an error in the criteria.

If I recall correctly that says match any thing where there is a
character that is not a colon or a W in the string. If that is true
return zero otherwise return 1.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi please help,

In a field i have variuos characters that represents orders, there is an
indefinative number of combinations, all along the following lines;

W:WY
W:W
W:YY
W:WG
W:QGWY
:WW
:YWQ
etc...with a hundred possible options including an unlimited number of
characters.

However, basically all i want is a simple IIF statement, whereby if the
field only contains a ':' or/and 'W' character then put a 1, otherwise put a
0.

For example,
if the field contains W:WW, then i want a 1 to appear
If the field contains WWY, then i want a 0 to appear

I hope this makes sense.

Thanks
Click to expand...
Click to expand...
 
Well, that's curious:

When, from the debug (immediate) window I input this:

? wfind("wy:y")
it returns 0.

Bob
Hi, thats exactly what i'm after, unfortunately this formula appaears to be
returning a 1 for everything - any ideas.
So if the field contains any characters except "W" or ":" return zero.
Click to expand...
[quoted text clipped - 40 lines]
Click to expand...
 
A thought. Did you type the function into a module? If so, suspect a typo.
Try copying/pasting directly from this forum.

Bob
Well, that's curious:

When, from the debug (immediate) window I input this:

? wfind("wy:y")
it returns 0.

Bob
Hi, thats exactly what i'm after, unfortunately this formula appaears to be
returning a 1 for everything - any ideas.
Click to expand...
[quoted text clipped - 4 lines]
Click to expand...
 
I think I made an error in my SQL. The negation operator "!" should
have been inside the brackets. And the null comparison was also
extraneous and introduced bad results since it was going to return 1
whenever the field was null and 0 whenever the field was null no matter
what the other test returned.


IIF([Field] LIKE "*[!:W]*", 0,1)


If you have nulls in your field, how do you want them handled?

Easiest solution would be to use NZ to force a value, Assign a value of
":" if you want 1 returned and a value of "A" if you want 0 returned
for a null value.

IIF(Nz([Field],":") LIKE "*[!:W]*", 0,1)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


John said:
So if the field contains any characters except "W" or ":" return zero.

IIF([Field] LIKE "*![:W]*" OR [Field] is Null, 0,1)

I think the above will give you the desired results. Although I could
have made an error in the criteria.

If I recall correctly that says match any thing where there is a
character that is not a colon or a W in the string. If that is true
return zero otherwise return 1.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi please help,

In a field i have variuos characters that represents orders, there is
an indefinative number of combinations, all along the following lines;

W:WY
W:W
W:YY
W:WG
W:QGWY
:WW
:YWQ
etc...with a hundred possible options including an unlimited number of
characters.

However, basically all i want is a simple IIF statement, whereby if
the field only contains a ':' or/and 'W' character then put a 1,
otherwise put a 0.

For example,
if the field contains W:WW, then i want a 1 to appear
If the field contains WWY, then i want a 0 to appear

I hope this makes sense.

Thanks
Click to expand...
Click to expand...
 
Remember that and IIf statement has three parts: logical test, True and
False
You can test one logical test and if it is false, you can embed another IIf
statement in the false section...

IIf ( [Field] = ":", True Statement, False statement) or defined further
IIf ( [Field] = ":", 1, FalseStatement) or define further
IIf ( [Field] = ":", 1, IIf ( [Field] = "W", 1, IIf ( [Field] = ":W", 1, IIf
( [Field]="W:", 1, 0) ) ) ) This would give you the 4 possible combinations
of : & W
 
Back
Top