Wildcard search for single character only

[Guest]

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

 

[Guest]

Using a query with Like.

Select * From TableName Where FieldName Like "*" & [Please write a letter] &
"*"

Or in another way, in the query under the field write the criteria

Like "*" & [Please write a letter] & "*"


The user will be prompt with a message to enter a letter

 

[James A. Fortune]

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

If you are using A2K or greater, try something like (untested):

If InStr(Nz(MyFieldOrVariable), ""), "W", 1) > 0 And
Len(Replace(Replace(Nz(MyFieldOrVariable, ""), ":", "W"), "W", "")) = 0
Then ...

The field or variable must contain a "W" and then after changing ":"'s
to "W"'s and changing any resulting "W"'s to empty strings there can be
nothing left. I think Ofer read your post a little too quickly and
missed the part about no other letter.

James A. Fortune
(e-mail address removed)

 

[Guest]

Thanks Ofer, But this would find all entries which contain a W, i want it to
contain a W and no other letter (apart from more W's or :'s).

Using a query with Like.

Select * From TableName Where FieldName Like "*" & [Please write a letter] &
"*"

Or in another way, in the query under the field write the criteria

Like "*" & [Please write a letter] & "*"


The user will be prompt with a message to enter a letter
--
Good Luck
BS"D

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

 

[Guest]

Thanks James, but i can't appear to get this to work

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

If you are using A2K or greater, try something like (untested):

If InStr(Nz(MyFieldOrVariable), ""), "W", 1) > 0 And
Len(Replace(Replace(Nz(MyFieldOrVariable, ""), ":", "W"), "W", "")) = 0
Then ...

The field or variable must contain a "W" and then after changing ":"'s
to "W"'s and changing any resulting "W"'s to empty strings there can be
nothing left. I think Ofer read your post a little too quickly and
missed the part about no other letter.

James A. Fortune
(e-mail address removed)

 

[Guest]

Try this
1. Copy this function to a module

Function CheckForLetter(FieldName As String, LetterToCheck As String) As
Boolean
Dim I As Integer
CheckForLetter = True
FieldName = Replace(Replace(FieldName,":","")," ","")
For I = 1 To Len(FieldName)
If Mid(FieldName, I, 1) <> LetterToCheck Then
CheckForLetter = False
Exit Function
End If
Next I
End Function


2. Run this SQL using the function as a criteria
SELECT TableName.*
FROM TableName
WHERE CheckForLetter([FieldName],[Select a letter])=True

--
Good Luck
BS"D

Thanks Ofer, But this would find all entries which contain a W, i want it to
contain a W and no other letter (apart from more W's or :'s).

Using a query with Like.

Select * From TableName Where FieldName Like "*" & [Please write a letter] &
"*"

Or in another way, in the query under the field write the criteria

Like "*" & [Please write a letter] & "*"


The user will be prompt with a message to enter a letter
--
Good Luck
BS"D

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

 

[Guest]

Another way, without the function
Select * From TableName Where
Len(Replace(Replace([FieldName], [Select a letter], ""), ":", "")) > 0

--
Good Luck
BS"D

Thanks Ofer, But this would find all entries which contain a W, i want it to
contain a W and no other letter (apart from more W's or :'s).

Using a query with Like.

Select * From TableName Where FieldName Like "*" & [Please write a letter] &
"*"

Or in another way, in the query under the field write the criteria

Like "*" & [Please write a letter] & "*"


The user will be prompt with a message to enter a letter
--
Good Luck
BS"D

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

 

[James A. Fortune]

Thanks James, but i can't appear to get this to work

:

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

If you are using A2K or greater, try something like (untested):

If InStr(Nz(MyFieldOrVariable), ""), "W", 1) > 0 And
Len(Replace(Replace(Nz(MyFieldOrVariable, ""), ":", "W"), "W", "")) = 0
Then ...

The field or variable must contain a "W" and then after changing ":"'s
to "W"'s and changing any resulting "W"'s to empty strings there can be
nothing left. I think Ofer read your post a little too quickly and
missed the part about no other letter.

James A. Fortune
(e-mail address removed)

How are you trying to use it? Are you actually trying to use IIf in a
query instead of If? Are you trying to use this in VBA code. I think
we're getting close, but a little more information might help us get
there more quickly. Also, try Ofer's latest attempt. If that works,
one of the three of us can add the InStr part in conjunction with using
the same query parameter prompt technique.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

i want it to
contain a W and no other letter (apart from more W's or :'s)

Try:

my_col NOT LIKE '*[!W:]*'

There is no need to explicitly handle null values because of the
inherent behaviour of NULL values in SQL DDL i.e. an 'unknown' value
cannot be known to fail a validation rule, therefore it is allowed to
pass.

While we are on the subject of wildcard characters, you should
explicitly handle both 'ANSI' and 'non-ANSI' flavours of (multi-)
character wildcard, otherwise you validation rule may be thwarted
simply by switching from DAO to ADO e.g.

my_col NOT LIKE '%[!W:]%' AND my_col NOT LIKE '*[!W:]*'

Jamie.

--

 

[James A. Fortune]

i want it to
contain a W and no other letter (apart from more W's or :'s)

Try:

my_col NOT LIKE '*[!W:]*'

There is no need to explicitly handle null values because of the
inherent behaviour of NULL values in SQL DDL i.e. an 'unknown' value
cannot be known to fail a validation rule, therefore it is allowed to
pass.

While we are on the subject of wildcard characters, you should
explicitly handle both 'ANSI' and 'non-ANSI' flavours of (multi-)
character wildcard, otherwise you validation rule may be thwarted
simply by switching from DAO to ADO e.g.

my_col NOT LIKE '%[!W:]%' AND my_col NOT LIKE '*[!W:]*'

Jamie.

Jamie,

I think your basic idea is great, even elegant, but the second example I
tried -- "::" -- didn't work correctly.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

Try:

my_col NOT LIKE '*[!W:]*'

you should
explicitly handle both 'ANSI' and 'non-ANSI' flavours of (multi-)
character wildcard

my_col NOT LIKE '%[!W:]%' AND my_col NOT LIKE '*[!W:]*'

I think your basic idea is great, even elegant, but the second example I
tried -- "::" -- didn't work correctly.

I didn't use "::" in my second example.

Jamie.

--

 

[Phillip Windell]

Perhaps the best question would be "Why do you have such a situation that
would require this kind of query?" Maybe you are approaching it wrong to
start with, or maybe the database design needs altered as to not created
such a situation.

I believe that, generally speaking, if something is very difficult and messy
to do,...it is probably being done wrong to start with.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Thanks James, but i can't appear to get this to work

Hi,

I have a field wihich contain a variety of letters, examples below;

W:YMW
:W
W:WWW
Y:Y9
9:W9
W:W
Y:YW

ETC....

i need to pick out only those rows that have a W, no matter how many W's,
but must have no other letter, but can have the : character. How can this be
include into a IF statement, like below

If([Column Header] = ???? , "E", "I"

Hope you can help Thanks

If you are using A2K or greater, try something like (untested):

If InStr(Nz(MyFieldOrVariable), ""), "W", 1) > 0 And
Len(Replace(Replace(Nz(MyFieldOrVariable, ""), ":", "W"), "W", "")) = 0
Then ...

The field or variable must contain a "W" and then after changing ":"'s
to "W"'s and changing any resulting "W"'s to empty strings there can be
nothing left. I think Ofer read your post a little too quickly and
missed the part about no other letter.

James A. Fortune
(e-mail address removed)