Wildcard for all locations

  • Thread starter Thread starter Lisa Cowan
  • Start date Start date
L

Lisa Cowan

I have a parameter query based on searching for certain trades in certain
counties. However some times I need to do a nationwide search, so a
particular trade but from all counties.

I have tried entering * and # on their own but get no results. I have had a
look a previous questions on wildcards but can't find anything.

Please can some one help, probably very simple and people like me are very
irritating but we are very grateful :)
 
First question doe the County field ALWAYS have a value? If so, you should be
able to enter * if you are using the LIKE comparison operator

Field: County
Criteria: Like [Enter County Name]

If you respond with * then that should return all records where County has a
value. If not, try % as the wild card.

If County can sometimes be null then try
Field: County
Criteria: [Enter County Name] OR [Enter County Name] is Null

WARNING: Access will restructure the criteria section when you save the query.
It may become too complex if you have criteria applied to several fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
SELECT [Master CV In].Name, [Master CV In].[Date Rec], [Master CV In].[Job
Disc 1], [Master CV In].[Job Disc 2], [Master CV In].[Job Disc 3], [Master CV
In].[Job Disc 4], [Master CV In].[Job Disc 5], [Master CV In].[Job Disc 6],
[Master CV In].[Job Disc 7], [Master CV In].[Rec Via], [Master CV In].Town,
[Master CV In].Location, [Master CV In].[Tel Numbers 1], [Master CV In].[Tel
Numbers 2]
FROM [Master CV In]
WHERE ((([Master CV In].[Job Disc 1])=[Trade]) AND (([Master CV
In].Location)=[County])) OR ((([Master CV In].[Job Disc 2])=[Trade]) AND
(([Master CV In].Location)=[County])) OR ((([Master CV In].[Job Disc
3])=[Trade]) AND (([Master CV In].Location)=[County])) OR ((([Master CV
In].[Job Disc 4])=[Trade]) AND (([Master CV In].Location)=[County])) OR
((([Master CV In].[Job Disc 5])=[Trade]) AND (([Master CV
In].Location)=[County])) OR ((([Master CV In].[Job Disc 6])=[Trade]) AND
(([Master CV In].Location)=[County])) OR ((([Master CV In].[Job Disc
7])=[Trade]) AND (([Master CV In].Location)=[County]));

I have entered the SQL for the query (sorry always for get to do that). I
have tried % and that returns nothing as well. Sorry to be a pain, probably
bad design. thanks
Lisa


John Spencer (MVP) said:
First question doe the County field ALWAYS have a value? If so, you should be
able to enter * if you are using the LIKE comparison operator

Field: County
Criteria: Like [Enter County Name]

If you respond with * then that should return all records where County has a
value. If not, try % as the wild card.

If County can sometimes be null then try
Field: County
Criteria: [Enter County Name] OR [Enter County Name] is Null

WARNING: Access will restructure the criteria section when you save the query.
It may become too complex if you have criteria applied to several fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Lisa said:
I have a parameter query based on searching for certain trades in certain
counties. However some times I need to do a nationwide search, so a
particular trade but from all counties.

I have tried entering * and # on their own but get no results. I have had a
look a previous questions on wildcards but can't find anything.

Please can some one help, probably very simple and people like me are very
irritating but we are very grateful :)
Click to expand...
Click to expand...
 
I don't see the LIKE operator in that query at all.

SELECT [Master CV In].Name, [Master CV In].[Date Rec]
, [Master CV In].[Job Disc 1], [Master CV In].[Job Disc 2]
, [Master CV In].[Job Disc 3], [Master CV In].[Job Disc 4]
, [Master CV In].[Job Disc 5], [Master CV In].[Job Disc 6]
, [Master CV In].[Job Disc 7], [Master CV In].[Rec Via]
, [Master CV In].Town, [Master CV In].Location
, [Master CV In].[Tel Numbers 1], [Master CV In].[Tel Numbers 2]
FROM [Master CV In]
WHERE ((([Master CV In].[Job Disc 1])=[Trade])
AND (([Master CV In].Location) LIKE [County]))
OR ((([Master CV In].[Job Disc 2])=[Trade]) AND
(([Master CV In].Location)LIKE[County]))
OR ((([Master CV In].[Job Disc
3])=[Trade]) AND (([Master CV In].Location)=[County]))
OR ((([Master CV In].[Job Disc 4])=[Trade]) AND
(([Master CV In].Location)LIKE [County]))
OR ((([Master CV In].[Job Disc 5])=[Trade]) AND
(([Master CV In].Location) LIKE [County]))
OR ((([Master CV In].[Job Disc 6])=[Trade]) AND
(([Master CV In].Location)LIKE [County]))
OR ((([Master CV In].[Job Disc 7])=[Trade]) AND
(([Master CV In].Location)LIKE[County]));


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Lisa said:
SELECT [Master CV In].Name, [Master CV In].[Date Rec], [Master CV In].[Job
Disc 1], [Master CV In].[Job Disc 2], [Master CV In].[Job Disc 3], [Master CV
In].[Job Disc 4], [Master CV In].[Job Disc 5], [Master CV In].[Job Disc 6],
[Master CV In].[Job Disc 7], [Master CV In].[Rec Via], [Master CV In].Town,
[Master CV In].Location, [Master CV In].[Tel Numbers 1], [Master CV In].[Tel
Numbers 2]
FROM [Master CV In]
WHERE ((([Master CV In].[Job Disc 1])=[Trade]) AND (([Master CV
In].Location)=[County])) OR ((([Master CV In].[Job Disc 2])=[Trade]) AND
(([Master CV In].Location)=[County])) OR ((([Master CV In].[Job Disc
3])=[Trade]) AND (([Master CV In].Location)=[County])) OR ((([Master CV
In].[Job Disc 4])=[Trade]) AND (([Master CV In].Location)=[County])) OR
((([Master CV In].[Job Disc 5])=[Trade]) AND (([Master CV
In].Location)=[County])) OR ((([Master CV In].[Job Disc 6])=[Trade]) AND
(([Master CV In].Location)=[County])) OR ((([Master CV In].[Job Disc
7])=[Trade]) AND (([Master CV In].Location)=[County]));

I have entered the SQL for the query (sorry always for get to do that). I
have tried % and that returns nothing as well. Sorry to be a pain, probably
bad design. thanks
Lisa


John Spencer (MVP) said:
First question doe the County field ALWAYS have a value? If so, you should be
able to enter * if you are using the LIKE comparison operator

Field: County
Criteria: Like [Enter County Name]

If you respond with * then that should return all records where County has a
value. If not, try % as the wild card.

If County can sometimes be null then try
Field: County
Criteria: [Enter County Name] OR [Enter County Name] is Null

WARNING: Access will restructure the criteria section when you save the query.
It may become too complex if you have criteria applied to several fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Lisa said:
I have a parameter query based on searching for certain trades in certain
counties. However some times I need to do a nationwide search, so a
particular trade but from all counties.

I have tried entering * and # on their own but get no results. I have had a
look a previous questions on wildcards but can't find anything.

Please can some one help, probably very simple and people like me are very
irritating but we are very grateful :)
Click to expand...
Click to expand...
Click to expand...
 
I have entered the SQL for the query (sorry always for get to do that). I
have tried % and that returns nothing as well. Sorry to be a pain, probably
bad design. thanks
Click to expand...

The = operator which you are using ignores wildcards and gives you only exact
matches - if you don't have any data from the county named * you won't see
anything. Change the = to LIKE in order to have wildcards work:

SELECT [Master CV In].Name, [Master CV In].[Date Rec], [Master CV In].[Job
Disc 1], [Master CV In].[Job Disc 2], [Master CV In].[Job Disc 3], [Master CV
In].[Job Disc 4], [Master CV In].[Job Disc 5], [Master CV In].[Job Disc 6],
[Master CV In].[Job Disc 7], [Master CV In].[Rec Via], [Master CV In].Town,
[Master CV In].Location, [Master CV In].[Tel Numbers 1], [Master CV In].[Tel
Numbers 2]
FROM [Master CV In]
WHERE ((([Master CV In].[Job Disc 1])=[Trade]) AND (([Master CV
In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc 2])=[Trade]) AND
(([Master CV In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc
3])=[Trade]) AND (([Master CV In].Location) LIKE [County])) OR ((([Master CV
In].[Job Disc 4])=[Trade]) AND (([Master CV In].Location) LIKE [County])) OR
((([Master CV In].[Job Disc 5])=[Trade]) AND (([Master CV
In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc 6])=[Trade]) AND
(([Master CV In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc
7])=[Trade]) AND (([Master CV In].Location) LIKE [County]));

HOWEVER... your table design *is wrong* and you need to reconsider it. If one
CV can have multiple Trades, then you need another table, related one to many
to this one. The same would apply to job disc and tel number. You're using a
relational database, not a spreadsheet - use it relationally!
 
Thank you for your help regarding the query. Your comments regarding design -
when you say relational, please could you explain. The current table is a one
to many relationship, Currently there is one main table, one for Locations
and one for trades. One form is used to enter the details. thanks Lisa

John W. Vinson said:
I have entered the SQL for the query (sorry always for get to do that). I
have tried % and that returns nothing as well. Sorry to be a pain, probably
bad design. thanks
Click to expand...

The = operator which you are using ignores wildcards and gives you only exact
matches - if you don't have any data from the county named * you won't see
anything. Change the = to LIKE in order to have wildcards work:

SELECT [Master CV In].Name, [Master CV In].[Date Rec], [Master CV In].[Job
Disc 1], [Master CV In].[Job Disc 2], [Master CV In].[Job Disc 3], [Master CV
In].[Job Disc 4], [Master CV In].[Job Disc 5], [Master CV In].[Job Disc 6],
[Master CV In].[Job Disc 7], [Master CV In].[Rec Via], [Master CV In].Town,
[Master CV In].Location, [Master CV In].[Tel Numbers 1], [Master CV In].[Tel
Numbers 2]
FROM [Master CV In]
WHERE ((([Master CV In].[Job Disc 1])=[Trade]) AND (([Master CV
In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc 2])=[Trade]) AND
(([Master CV In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc
3])=[Trade]) AND (([Master CV In].Location) LIKE [County])) OR ((([Master CV
In].[Job Disc 4])=[Trade]) AND (([Master CV In].Location) LIKE [County])) OR
((([Master CV In].[Job Disc 5])=[Trade]) AND (([Master CV
In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc 6])=[Trade]) AND
(([Master CV In].Location) LIKE [County])) OR ((([Master CV In].[Job Disc
7])=[Trade]) AND (([Master CV In].Location) LIKE [County]));

HOWEVER... your table design *is wrong* and you need to reconsider it. If one
CV can have multiple Trades, then you need another table, related one to many
to this one. The same would apply to job disc and tel number. You're using a
relational database, not a spreadsheet - use it relationally!
Click to expand...
 
Thank you for your help regarding the query. Your comments regarding design -
when you say relational, please could you explain. The current table is a one
to many relationship, Currently there is one main table, one for Locations
and one for trades. One form is used to enter the details. thanks Lisa
Click to expand...

Any time you have *repeating fields* in your table design - such as Job Disc
1, Job Disc 2, Job Disc 3 - you're "committing spreadsheet" and using a non
normalized design. This sounds like a classic many to many relationship: each
CV may involve zero, one, *OR MANY* Job Disc values; each Job Disc value may
apply to zero, one or many CVs.

This structure is handled relationally using three tables: a table of CVs
(without ANY job disc information); a table of Job Discs, with a primary key
JobDiscID and a text job description; and a third table relating the two, with
a link to the primary key of each table.

You might want to review one of the tutorials below; Crystal gives a good
overview of the principles involved in her "Normalization" chapter.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Back
Top