IIF, but Case Sensitive

[mr-tom]

Hi,

I'm trying to do something which I'm sure must be simple, but I can't see
how to do.

In Excel, I can do an =IF(x=y,do this,do that)
But it isn't case sensitive.
The case sensitive version is EXACT, so I would have:
=IF(EXACT(x=y),do this,do that)

I'm trying to achieve the same thing in the Expression Builder in Access
Query.

SO I can easily get =IIF(x=y, do this,dothat)
But I can't find an equivalent of EXACT.

I need to make a case sensitive comparison.

Can this easily be achieved?

Cheers,

Tom.

 

[Jeff Boyce]

Tom

"...easily ..." for whom?<g>

If I recall correctly, Access isn't case sensitive in searches and
comparisons. But one way to work around this is using the ASC() and/or
CHR() functions inside your IIF() statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mr-tom]

Thanks, Jeff.

This could be fun... Not...

I'm looking to flag each time I see a string containing "ANO"...

 

[John W. Vinson]

I need to make a case sensitive comparison.

Can this easily be achieved?

Not easily but it is possible. The InStr() function has an optional argument
to specify case sensitivity. See the VBA help.

For your posted example you could use

InStr([yourfield], "ANO", vbBinaryCompare)

This will return the position of the string ANO in your field if it's there,
and 0 if not.

 

[Jeff Boyce]

THANK YOU, John!

That's my "learned something new today" for today...

Jeff B.

I need to make a case sensitive comparison.

Can this easily be achieved?

Not easily but it is possible. The InStr() function has an optional
argument
to specify case sensitivity. See the VBA help.

For your posted example you could use

InStr([yourfield], "ANO", vbBinaryCompare)

This will return the position of the string ANO in your field if it's
there,
and 0 if not.

 

[mr-tom]

Thanks, John.

I'm going to avoid VBA as I am not yet Jedi, I'll look into using the
options outlined for the expression builder.

Cheers,

Tom.

I need to make a case sensitive comparison.

Can this easily be achieved?

Not easily but it is possible. The InStr() function has an optional argument
to specify case sensitivity. See the VBA help.

For your posted example you could use

InStr([yourfield], "ANO", vbBinaryCompare)

This will return the position of the string ANO in your field if it's there,
and 0 if not.

 

[Michel Walsh]

There is no need to be a Jedi, simply use the function itself. You can also
use:


0 = StrComp(yourFieldName , "ANO", 0)


as computed expression (computed 'field') in your query. You have to use
the value of the constant, 0, rather than its name, vbBinaryCompare, in a
query.



Vanderghast, Access MVP

 

[John W. Vinson]

There is no need to be a Jedi, simply use the function itself. You can also
use:


0 = StrComp(yourFieldName , "ANO", 0)


as computed expression (computed 'field') in your query. You have to use
the value of the constant, 0, rather than its name, vbBinaryCompare, in a
query.



Vanderghast, Access MVP

Michel, that would be more efficient if Mr. Tom is looking for a field
containing the exact text "ANO" - my (possibly incorrect) understanding is
that he wants to search for strings containing ANO (presumably surrounded by
other text). Tom, which is it?

 

[mr-tom]

Thanks to both of you.

It was a field with "ANO" in it.

Essentially ANO denotes a planned member of staff, so the list would be:
John Smith
Fred Bloggs
LondonANO1
etc.

The problem being that if "ano" or similar was part of somebody's name (it
is) then I'd get a false positive.

I've actually worked around it this afternoon.

Also available is the Branch Name, so I've got:
Expr1: IIF(Left([dim_Advisers]![DisplayName],Len([dim_Branch]![Branch])+3) =
[dim_Branch]![Branch]&"ANO",do this if match, otherwise do that)

Having manually reviewed the output, this is working well.

Appreciate it is inelegant, but it's certainly a lot closer to my level of
ability.

Thanks again for all the help.

Tom.