Query Criteria - Search for ********* - Help!?

[Guest]

I have source data in a table for 4 different fields which is populated with
data, but in some instances it is populated with 9 asterix's e.g. *********

How can I put criteria in my query to filter these out..?

I have tried like "*********" and <>"*********"
also like ********* and Not like *********

but no luck so far - any suggestions would be greatly appreciated..

Thanks!

 

[Guest]

Try this

Where Fieldname = Cstr("*********")

Don't ose Like

 

[Gary Walter]

I have source data in a table for 4 different fields which is populated
with
data, but in some instances it is populated with 9 asterix's e.g.
*********

How can I put criteria in my query to filter these out..?

I have tried like "*********" and <>"*********"
also like ********* and Not like *********

but no luck so far - any suggestions would be greatly appreciated..

Hi Louise,

PMFBI

try

WHERE [somefield] Not Like "[*]"

Apologies again for butting in,

gary

 

[Gary Walter]

WHERE [somefield] Not Like "*[*]*"

but this will also (as a side effect)
filter out all records where
[somefield] is null (if that matters).

Confusing I know, but inverse works "okay"

WHERE [somefield] Like "*[*]*"

will return ONLY records w/ any number
of "*" in [somefield]

Point being that "*" is a wildcard in Access
so theoretically you need to surround it with
brackets to search/filter for it.



"Gary Walter"wrote:

I have source data in a table for 4 different fields which is populated
with
data, but in some instances it is populated with 9 asterix's e.g.
*********

How can I put criteria in my query to filter these out..?

I have tried like "*********" and <>"*********"
also like ********* and Not like *********

but no luck so far - any suggestions would be greatly appreciated..

Hi Louise,

PMFBI

try

WHERE [somefield] Not Like "[*]"

Apologies again for butting in,

gary

 

[John Spencer]

<> "*********" should have worked if the table contains exactly nine
asterisks and not other characters. This should NOT have the word like in
the criteria.

Not Like "*[*]*" should filter out all fields that have at least one
asterisk.

Not Like "*[*][*][*][*][*][*][*][*][*]*" should filter out fields that have
at least 9 consecutive asterisks.

 

[Gary Walter]

examples:

tblMachine
MachineName testast
21A abcdef
21B abc****def
21C ****
21D *
21E
21F abc****def
//////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Like "[*]"

returns

MachineName testast
21D *

///////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Like "[****]"

returns

MachineName testast
21D *

//////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Like "[*][*][*][*]"

returns

MachineName testast
21C ****

//////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Not Like "[*][*][*][*]"

returns

MachineName testast
21A abcdef
21B abc****def
21D *
21F abc****def

[NOTE: did not return 21E which
does not have an "*" but is null]
//////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Not Like "[****]"

returns

MachineName testast
21A abcdef
21B abc****def
21C ****
21F abc****def

//////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Like "*[*]*"

returns

MachineName testast
21B abc****def
21C ****
21D *
21F abc****def

//////////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Not Like "*[*]*"

returns

MachineName testast
21A abcdef

[NOTE: did not return 21E which
does not have an "*" but is null]
/////////////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Like "*"

returns

MachineName testast
21A abcdef
21B abc****def
21C ****
21D *
21F abc****def

[NOTE: did not return 21E which
does not have an "*" but is null]
/////////////////////////////////////
SELECT
tblMachine.MachineName,
tblMachine.testast
FROM tblMachine
WHERE tblMachine.testast Not Like "*"

returns

MachineName testast
21E

[NOTE: returns only 21E which
does not have an "*" but is null]
///////////////////////////////////////

WHERE [somefield] Not Like "*[*]*"

but this will also (as a side effect)
filter out all records where
[somefield] is null (if that matters).

Confusing I know, but inverse works "okay"

WHERE [somefield] Like "*[*]*"

will return ONLY records w/ any number
of "*" in [somefield]

Point being that "*" is a wildcard in Access
so theoretically you need to surround it with
brackets to search/filter for it.



"Gary Walter"wrote:

I have source data in a table for 4 different fields which is populated
with
data, but in some instances it is populated with 9 asterix's e.g.
*********

How can I put criteria in my query to filter these out..?

I have tried like "*********" and <>"*********"
also like ********* and Not like *********

but no luck so far - any suggestions would be greatly appreciated..

Hi Louise,

PMFBI

try

WHERE [somefield] Not Like "[*]"

Apologies again for butting in,

gary

 

[Gary Walter]

just to be sure on point
of when your field has a null

[somefield] Like "anything"

is same as following for null record

null like "anything"

which will evaluate to null,
which is equivalent in this case to "not true",
so record will not be returned


Likewise,

[somefield] Not Like "anything"

is same as following for null record

null not like "anything"

which will evaluate to null,
which is equivalent in this case to "not true",
so record will not be returned

 

[Gary Walter]

the "not like" part in previous post was not correct
for every circumstance!

I was wrong and I cannot logically
sort it out...sorry