Query Help

[Guest]

I have a table that has six columns: Project Name, Project ID, Address, Legal
Description, Cross-Reference, and Box Number. I want to create a query where
a person can search for records using any of these fields. I know i can
create a parameter query for each field, but is there a way I can combine all
those parameter queries so a person can choose one parameter then be prompted
with the search question/box?

 

[MGFoster]

I have a table that has six columns: Project Name, Project ID, Address, Legal
Description, Cross-Reference, and Box Number. I want to create a query where
a person can search for records using any of these fields. I know i can
create a parameter query for each field, but is there a way I can combine all
those parameter queries so a person can choose one parameter then be prompted
with the search question/box?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a Form to run the query. Set up the form to hold the query's
criteria. E.g.: Text boxes for Project Name, Project ID, etc. Then
set up the query something like this:

SELECT ProjectName, ProjectID, Address, LegalDescription,
CrossReference, BoxNo
FROM table_name
WHERE ((ProjectName LIKE "'*" & Forms!FormName!ProjectName & "*'" AND
Forms!FormName!ProjectName IS NOT NULL) OR (Forms!FormName!ProjectName
IS NULL)
AND ((ProjectID = & Forms!FormName!ProjectName AND
Forms!FormName!ProjectID IS NOT NULL) OR (Forms!FormName!ProjectID IS
NULL)
.... same for other fields ...

See this URL for more info:

http://support.microsoft.com/kb/q103181/

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQvEqbIechKqOuFEgEQK3wACg72BOS2/PiR1qflKsOmGKsIK0+fUAn3dE
JJhHkNH18YaLLhLiKJuvLY7h
=yaPE
-----END PGP SIGNATURE-----

 

[Michel Walsh]

Hi,


If just one column will ever be selected, but you don't know which one, you
can try something like:


SELECT * FROM myTable
WHERE SWITCH( [Which column]='f1', f1,
[Which column]='f2', f2,
[Which column]='f3', f3,
[Which column]='f4', f4,
[Which column]='f5', f5,
true, f6) = [Which value
you scan for]





I assumed the column names are f1, f2, f3, f4, f5 and f6. The query asks
for two parameters, the field name to look into (or any other similar info,
just rewrite the various tests), and the value to scan.

That could be TERRIBLY SLOW on large table, since indexes would not be put
to contribution.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

So I Put in what you wrote down and changed the f1-6 to the column names.
But when I run the query it asks for the column i want to search by then asks
for the parameters of that column and each subsequent column. Is there a way
to change this so it only asks for the parameters of the column I choose?
Here is what I currently have down:

SELECT * FROM [Central Records Storage]

WHERE SWITCH( [Which column]='Project Name', Project_Name,
[Which column]='Project ID', Project_ID,
[Which column]='Address', Address,
[Which column]='Legal Description', Legal_Description,
[Which column]='Cross Reference', Cross_Reference,
true, Location) = [Which
value you scan for]

So for example I searched by address and then it asked for the parameters of
address, Legal, Cross Reference, and Location. I have also created a
Parameter query for each column that works fine. Would it be easier to
create a query that runs one of the parameter queries depending on which
column is selected? If so could you show me how that would be set up.

Hi,


If just one column will ever be selected, but you don't know which one, you
can try something like:


SELECT * FROM myTable
WHERE SWITCH( [Which column]='f1', f1,
[Which column]='f2', f2,
[Which column]='f3', f3,
[Which column]='f4', f4,
[Which column]='f5', f5,
true, f6) = [Which value
you scan for]





I assumed the column names are f1, f2, f3, f4, f5 and f6. The query asks
for two parameters, the field name to look into (or any other similar info,
just rewrite the various tests), and the value to scan.

That could be TERRIBLY SLOW on large table, since indexes would not be put
to contribution.




Hoping it may help,
Vanderghast, Access MVP

I have a table that has six columns: Project Name, Project ID, Address,
Legal
Description, Cross-Reference, and Box Number. I want to create a query
where
a person can search for records using any of these fields. I know i can
create a parameter query for each field, but is there a way I can combine
all
those parameter queries so a person can choose one parameter then be
prompted
with the search question/box?

 

[Michel Walsh]

Hi,



I am not sure I understand. Is the field name, as example, is Project ID
(with a space)? if so, you should use the field name too, with [ ] around
it. If Project_ID IS NOT the field name, it is the standard behavior, for
Access, to ask for that unknown entity.



Vanderghast, Access MVP