NOT LIKE '*[a-z]*'

[JohS]

Hi
I am trying to make a Query. I will populate suspected corrupted data from a
Table with a single Field which contains email addresses stored as text.

How can I populate the data which contains only: NOT LIKE
"abcdefghijklmnopqrstuvwxyz123456789@._-"?

(A standard Query with NOT LIKE '*[a-z]*' is not working. Do I need to make
a SubQuery or a Funtion do deal with this ?)

JohS

 

[Guest]

Hi,

You probably need multiple OR clauses in the criteria. Put the following on
seperate lines in the criteria under the field in question.

Not Like '*[0-9]*'
Not Like '*[a-b]*'
Not Like '*[@]*'
Not Like '*[.]*'
Not Like '*["]*'
Not Like '*[-]*'
Not Like '*[_]*'

 

[JohS]

I can't see how this is going to work. I have these data in tblContacts
(only two of them is not corrupted emails in this example - and I want to
populate only the corrupted) :

ab
abc
a12
246
(e-mail address removed)
(e-mail address removed)
[email protected]
Bj°[email protected]
bjorn@norbj?rn.com
"Fjellsaune, J?rn"

Now, I do this qryContacts :

SELECT tblContacts.Email
FROM tblContacts
WHERE (((tblContacts.Email) Not Like '*[0-9]*'))
OR (((tblContacts.Email) Not Like '*[a-z]*'))
OR (((tblContacts.Email) Not Like '*[@]*'))
OR (((tblContacts.Email) Not Like '*[.]*'))
OR (((tblContacts.Email) Not Like '*[-]*'))
OR (((tblContacts.Email) Not Like '*[_]*'));

What do I miss?
JohS

Hi,

You probably need multiple OR clauses in the criteria. Put the following
on
seperate lines in the criteria under the field in question.

Not Like '*[0-9]*'
Not Like '*[a-b]*'
Not Like '*[@]*'
Not Like '*[.]*'
Not Like '*["]*'
Not Like '*[-]*'
Not Like '*[_]*'

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi
I am trying to make a Query. I will populate suspected corrupted data
from a
Table with a single Field which contains email addresses stored as text.

How can I populate the data which contains only: NOT LIKE
"abcdefghijklmnopqrstuvwxyz123456789@._-"?

(A standard Query with NOT LIKE '*[a-z]*' is not working. Do I need to
make
a SubQuery or a Funtion do deal with this ?)

JohS

 

[MH]

Try:

Like "*[!abcdefghijklmnopqrstuvwxyz123456789@._-]*"

The exclamation mark means "not any of the following".

MH

 

[JohS]

Thanks. I think thoug it is the same, or I mean it has the same effekt. I
can't rule out eksample the one with "b" if there is other errors in the
field.
I need to test towards the combinations, more like hakking a password.
I'm just not good in Access, but could it be something with:
WHERE InStr([start, ]string1, string2[, compare])

Try:

Like "*[!abcdefghijklmnopqrstuvwxyz123456789@._-]*"

The exclamation mark means "not any of the following".

MH

Hi
I am trying to make a Query. I will populate suspected corrupted data
from a Table with a single Field which contains email addresses stored as
text.

How can I populate the data which contains only: NOT LIKE
"abcdefghijklmnopqrstuvwxyz123456789@._-"?

(A standard Query with NOT LIKE '*[a-z]*' is not working. Do I need to
make a SubQuery or a Funtion do deal with this ?)

JohS

 

[Arvin Meyer [MVP]]

My experience with corrupt data is that you can not query for it, or read it
in any way through SQL or code.

What I do is divide the table in half and open each half. The corrupted half
will complain. Keep doing that until you isolate the corrupt record(s) and
delete them manually if you can.

Generally, it is easier to import the tables into a new empty database, if
you can. Corrupt rows generally won't import.

 

[MH]

Did you try the example I gave you?

If so, what is the problem with it?

MH

Thanks. I think thoug it is the same, or I mean it has the same effekt. I
can't rule out eksample the one with "b" if there is other errors in the
field.
I need to test towards the combinations, more like hakking a password.
I'm just not good in Access, but could it be something with:
WHERE InStr([start, ]string1, string2[, compare])

Try:

Like "*[!abcdefghijklmnopqrstuvwxyz123456789@._-]*"

The exclamation mark means "not any of the following".

MH

Hi
I am trying to make a Query. I will populate suspected corrupted data
from a Table with a single Field which contains email addresses stored
as text.

How can I populate the data which contains only: NOT LIKE
"abcdefghijklmnopqrstuvwxyz123456789@._-"?

(A standard Query with NOT LIKE '*[a-z]*' is not working. Do I need to
make a SubQuery or a Funtion do deal with this ?)

JohS

 

[Dirk Goldgar]

In

Thanks. I think thoug it is the same, or I mean it has the same
effekt. I can't rule out eksample the one with "b" if there is other
errors in the field.
I need to test towards the combinations, more like hakking a password.
I'm just not good in Access, but could it be something with:
WHERE InStr([start, ]string1, string2[, compare])

Try:

Like "*[!abcdefghijklmnopqrstuvwxyz123456789@._-]*"

The exclamation mark means "not any of the following".

I suggest you look again at what MH posted, think about it, and try it
out.

Though I'd probably simplify it to

Like "*[!a-z1-9@._-]*"

which amounts to the same thing.

 

[JohS]

There were a problem with the suggestions given and that is that I was
"sleeping" whilst doing the tests. It worked perfectly when I did open my
eyesJ. Thanks

Did you try the example I gave you?

If so, what is the problem with it?

MH

Thanks. I think thoug it is the same, or I mean it has the same effekt. I
can't rule out eksample the one with "b" if there is other errors in the
field.
I need to test towards the combinations, more like hakking a password.
I'm just not good in Access, but could it be something with:
WHERE InStr([start, ]string1, string2[, compare])

Try:

Like "*[!abcdefghijklmnopqrstuvwxyz123456789@._-]*"

The exclamation mark means "not any of the following".

MH

Hi
I am trying to make a Query. I will populate suspected corrupted data
from a Table with a single Field which contains email addresses stored
as text.

How can I populate the data which contains only: NOT LIKE
"abcdefghijklmnopqrstuvwxyz123456789@._-"?

(A standard Query with NOT LIKE '*[a-z]*' is not working. Do I need to
make a SubQuery or a Funtion do deal with this ?)

JohS

 

[MH]

You're welcome, you may wish to include zeros in the list of characters to
exclude though.

MH

There were a problem with the suggestions given and that is that I was
"sleeping" whilst doing the tests. It worked perfectly when I did open my
eyesJ. Thanks

Did you try the example I gave you?

If so, what is the problem with it?

MH

Thanks. I think thoug it is the same, or I mean it has the same effekt.
I can't rule out eksample the one with "b" if there is other errors in
the field.
I need to test towards the combinations, more like hakking a password.
I'm just not good in Access, but could it be something with:
WHERE InStr([start, ]string1, string2[, compare])


Try:

Like "*[!abcdefghijklmnopqrstuvwxyz123456789@._-]*"

The exclamation mark means "not any of the following".

MH

Hi
I am trying to make a Query. I will populate suspected corrupted data
from a Table with a single Field which contains email addresses stored
as text.

How can I populate the data which contains only: NOT LIKE
"abcdefghijklmnopqrstuvwxyz123456789@._-"?

(A standard Query with NOT LIKE '*[a-z]*' is not working. Do I need to
make a SubQuery or a Funtion do deal with this ?)

JohS