Question:
I have a table which has the following fields:
Part Number, Date, Job Number
The Job Number field may contain several actual Job
Numbers (not just one in each line). For example.
Part Number Date Job Number
123456 2/15/03 ABC123 ABD456 BRE598
789321 2/16/03 FGH883 GFT369
I want to create a Query that will search the Job Number
field for one job number, but since I have three job
numbers in one field it won't find just the one I'm
looking for.
I want to run a query that will search for Job
Number "ABD456" and return the following data.
Part Number Date Job Number
123456 2/15/03 ABC123 ABD456 BRE598
Basically I want my Job Number search to be a WILDCARD
search that will search for my string of text anywhere in
the field.
PLEASE HELP
Answer:
-In Design View for your query, in the Criteria row of the column in your
query for [Job Number], you could put:
Like "*ABD456*"
If that works for you, you can then look at the SQL view of the Query to see
the complete SQL string.
-You can put:
Like [Enter the job Number]
in the Criteria row of your query. Then, you will have to instruct your
users to enter the desired job number with * in front of and after the job
number they are searching for.
Or, you could put the following in the Criteria row:
Like [Enter the Job Number with a * at beginning and at end of search
value]
Unfortunately, having to dig out a single value stuffed in among multiple
values in a field forces this sort of work-around.
-simplify the input task you could use
Like "*" & [ENTER THE JOB NUMBER] & "*"
That way the users don't need to worry about entering wild card characters. Of
course, if they ENTER nothing the query will return all the records, which
could be what you want.
Like IIF(IsNull([ENTER THE JOB NUMBER]),
"XXXZZZ",
"*" & [ENTER THE JOB NUMBER] & "*")
Where XXXZZZ is a job number that would never exist would give you no records
returned if the job number was left blank.
