Couple of Access quickies for someone with a better memory than me

[Dave G]

Hi Group...

In Access I have a large table of supplier addresses that I have extracted
from a system

Some of the names in the name field are preceded by *BLOCKED* including the
wildcard stars. E.g.

*BLOCKED* Bobs Pet Shop

I have two questions that someone in this knowledgeable group will be able
to answer very quickly.

What do I put in the search field if I want to search and replace *BLOCKED*
in all of the records... including the wildcard stars

and

In a query, if I want to identify those records that feature *BLOCKED* what
do I use as my criteria

Many thanks in advance

 

[DL]

Assuming yr using simple query design, you add the relevant table then
fields.
The criteria for the query, assuming Clientname is yr name field, and Client
is the tablename
Left([Client]![Client Name],9)="*BLOCKED*"

will then identify all records containing *BLOCKED*

what you do next depends in which format the names are in, eg
*BLOCKED*Bobs Pet Shop or
*BLOCKED* Bobs Pet Shop NB space between * and Bob
and if all are in the same format

David
PS make a copy of yr Table/db before manipulating

 

[Dave G]

Thanks for the tip...

However, I eventually managed it by using the following as my query
criteria... (Thanks to Doug Steele in microsoft.public.access

Like "[*]BLOCKED*"

In fact, I used Not Like "[*]BLOCK*" because I wanted to exclude the blocked
records from my final query.

It works a treat and doesn't depend on numbers of characters which is just
as well as there are instances where the word blocked is incomplete or there
are extra spaces between *Blocked* and the start of the supplier name.

Regards

Dave George

Assuming yr using simple query design, you add the relevant table then
fields.
The criteria for the query, assuming Clientname is yr name field, and Client
is the tablename
Left([Client]![Client Name],9)="*BLOCKED*"

will then identify all records containing *BLOCKED*

what you do next depends in which format the names are in, eg
*BLOCKED*Bobs Pet Shop or
*BLOCKED* Bobs Pet Shop NB space between * and Bob
and if all are in the same format

David
PS make a copy of yr Table/db before manipulating

Hi Group...

In Access I have a large table of supplier addresses that I have extracted
from a system

Some of the names in the name field are preceded by *BLOCKED* including the
wildcard stars. E.g.

*BLOCKED* Bobs Pet Shop

I have two questions that someone in this knowledgeable group will be able
to answer very quickly.

What do I put in the search field if I want to search and replace *BLOCKED*
in all of the records... including the wildcard stars

and

In a query, if I want to identify those records that feature *BLOCKED* what
do I use as my criteria

Many thanks in advance