Trying to limit data in a field

  • Thread starter Thread starter DevilDog1978
  • Start date Start date
D

DevilDog1978

I am pulling data from serveral sources. I created a query (build table
query) selecting the information I want in my customers field. Due to the
wildcards that I used due to the complexity of the customer field I ended up
with information I did not want. I then created a query using the table
previously created to remove the data I did not want using the language Not
Like "information here" The new table that was created lost only 25 records
out of the 70 or so records I wanted eliminated. Can anyone give me some
advice?
 
Specifically, I am trying to make this equation work:
Not (167 or 3304fmep or 4k* or 52* or 93567 or dg21* or dynacorp or electric
or hmla* or hospital or mac* or main* or malay* or mandr or mar* or mas* or
mat* or mtu*)
 
I would expect to see a something like the following as a where clause

WHERE NOT([SomeField]
In("167","3304fmep","93567","dynacorp","Electric","Hospital","mandr")
OR [SomeField] Like "*ma[rstu]*"
OR [SomeField] Like "4k*"
OR [SomeField] Like "52*
OR [SomeField] Like "hmla*"
OR [SomeField] Like "mac*"
OR [SomeField] Like "main*"
OR [SomeField] Like "malay*")

Alternative form of that would be
WHERE [SomeField] NOT In
("167","3304fmep","93567","dynacorp","Electric","Hospital","mandr")
AND [SomeField] NOT Like "*ma[rstu]*"
AND [SomeField] NOT Like "4k*"
AND [SomeField] NOT Like "52*
AND [SomeField] NOT Like "hmla*"
AND [SomeField] NOT Like "mac*"
AND [SomeField] NOT Like "main*"
AND [SomeField] NOT Like "malay*"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
entered Ok the field name is sub_cust. I entered that in the [SomeField]
section of the code and Access responded with:
The expression you entered contains invalid syntax
You may have entered an operand without an operator

On a sidenote, I noticed UMBC in your signature. Does UMBC have an online
class for Access? I am new to Access and I took on a fairly big project at
work creating an Access database because it needed to be done and my company
is not big enough to have a fulltime dba.

John Spencer (MVP) said:
I would expect to see a something like the following as a where clause

WHERE NOT([SomeField]
In("167","3304fmep","93567","dynacorp","Electric","Hospital","mandr")
OR [SomeField] Like "*ma[rstu]*"
OR [SomeField] Like "4k*"
OR [SomeField] Like "52*
OR [SomeField] Like "hmla*"
OR [SomeField] Like "mac*"
OR [SomeField] Like "main*"
OR [SomeField] Like "malay*")

Alternative form of that would be
WHERE [SomeField] NOT In
("167","3304fmep","93567","dynacorp","Electric","Hospital","mandr")
AND [SomeField] NOT Like "*ma[rstu]*"
AND [SomeField] NOT Like "4k*"
AND [SomeField] NOT Like "52*
AND [SomeField] NOT Like "hmla*"
AND [SomeField] NOT Like "mac*"
AND [SomeField] NOT Like "main*"
AND [SomeField] NOT Like "malay*"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Specifically, I am trying to make this equation work:
Not (167 or 3304fmep or 4k* or 52* or 93567 or dg21* or dynacorp or electric
or hmla* or hospital or mac* or main* or malay* or mandr or mar* or mas* or
mat* or mtu*)
Click to expand...
Click to expand...
 
I am pulling data from serveral sources. I created a query (build table
query) selecting the information I want in my customers field. Due to the
wildcards that I used due to the complexity of the customer field I ended up
with information I did not want. I then created a query using the table
previously created to remove the data I did not want using the language Not
Like "information here" The new table that was created lost only 25 records
out of the 70 or so records I wanted eliminated. Can anyone give me some
advice?
Click to expand...

If it's just 70 records and a dozen criteria, and if this is a one-time
operation, I'd just manually go through with several Delete queries changing
the criterion as you go.
 
DevilDog1978 said:
I am pulling data from serveral sources. I created a query (build table
query) selecting the information I want in my customers field. Due to the
wildcards that I used due to the complexity of the customer field I ended
up
with information I did not want. I then created a query using the table
previously created to remove the data I did not want using the language
Not
Like "information here" The new table that was created lost only 25
records
out of the 70 or so records I wanted eliminated. Can anyone give me some
advice?
Click to expand...
 
DevilDog1978 said:
I am pulling data from serveral sources. I created a query (build table
query) selecting the information I want in my customers field. Due to the
wildcards that I used due to the complexity of the customer field I ended
up
with information I did not want. I then created a query using the table
previously created to remove the data I did not want using the language
Not
Like "information here" The new table that was created lost only 25
records
out of the 70 or so records I wanted eliminated. Can anyone give me some
advice?
Click to expand...
 
Back
Top