B
Boba Fett
Hi,
I apologize that this is so long, but I wanted to be as detailed as
possible. Any help is greatly appreciated!
The facts:
-I have a form (frmSearch) with several combo boxes and several text
fields on it.
-The form and fields are unbound.
-The Row Type Soure and Row Source properties of the combo boxes are
tied to a table (but the combo boxes' Control Source is not bound to
anything).
-The form is used to search all of the fields in a particular table
(tblMain), based on what a user types into the text boxes and selects
with the combo boxes. After the user has specified all the data s/he
wants, a button is clicked which launches a new form (frmResults).
frmResults is bound to a query, the criteria of which is set to the
values specified on frmSearch and pulls matching records from a table.
frmResults displays the results. In other words, frmSearch is like a
query tool for tblMain and frmResults displays the results of that
query.
-Currently, I have the Default Value property of each field on
frmSearch set to "*" so that if the user doesn't want to specify a
value in any of the fields, the query's criteria will search based on a
wildcard value and return all results for that field. Here's an
example of one of the fields in my query:
Like "*" & [Forms]![frmSearch]![Name] & "*"
My dilema/question:
While this accomplishes what I want, it's a little cumbersome. I'd
also prefer to just have the fields on my form default to a null value,
so that a bunch of unsightly asterisks aren't displayed. I know the
instr() function can be used in place of the SQL I have above, but
can't figure out how to return all values if the user doesn't want to
specify anything for a particular field.
An example of what I tried for the "Name" field:
instr(1,[tblMain].[Name],[forms]![frmSearch]![Name])
I then set the criteria to >0.
This returns the results if a user specifies something in the Name
field of frmSearch, but I need some way of also returning all values
for the Name field if the user leaves it blank. In plain English, I
need to come up with a way to say "if there is a value in the Name
field then use the instr() function to return the matching fields from
tblMain.Name OR if the Name field is null then return all values from
tblMain.Name". Plus, I need to be able to apply this logic to each
field on the form (keep in mind that each of the 10 fields on the form
could either have a value or be left blank).
Any ideas how I can do this either in a query or in a VBA proceedure?
I'm thinking I could set the Record Source property of frmResults to a
SQL statement, but I can't figure out how to write the SQL statement to
do what I wrote in the previous paragraph. If there is some entirely
different way of doing this, please feel free to enlighten me!
Thanks Greatly!!!
BF
I apologize that this is so long, but I wanted to be as detailed as
possible. Any help is greatly appreciated!
The facts:
-I have a form (frmSearch) with several combo boxes and several text
fields on it.
-The form and fields are unbound.
-The Row Type Soure and Row Source properties of the combo boxes are
tied to a table (but the combo boxes' Control Source is not bound to
anything).
-The form is used to search all of the fields in a particular table
(tblMain), based on what a user types into the text boxes and selects
with the combo boxes. After the user has specified all the data s/he
wants, a button is clicked which launches a new form (frmResults).
frmResults is bound to a query, the criteria of which is set to the
values specified on frmSearch and pulls matching records from a table.
frmResults displays the results. In other words, frmSearch is like a
query tool for tblMain and frmResults displays the results of that
query.
-Currently, I have the Default Value property of each field on
frmSearch set to "*" so that if the user doesn't want to specify a
value in any of the fields, the query's criteria will search based on a
wildcard value and return all results for that field. Here's an
example of one of the fields in my query:
Like "*" & [Forms]![frmSearch]![Name] & "*"
My dilema/question:
While this accomplishes what I want, it's a little cumbersome. I'd
also prefer to just have the fields on my form default to a null value,
so that a bunch of unsightly asterisks aren't displayed. I know the
instr() function can be used in place of the SQL I have above, but
can't figure out how to return all values if the user doesn't want to
specify anything for a particular field.
An example of what I tried for the "Name" field:
instr(1,[tblMain].[Name],[forms]![frmSearch]![Name])
I then set the criteria to >0.
This returns the results if a user specifies something in the Name
field of frmSearch, but I need some way of also returning all values
for the Name field if the user leaves it blank. In plain English, I
need to come up with a way to say "if there is a value in the Name
field then use the instr() function to return the matching fields from
tblMain.Name OR if the Name field is null then return all values from
tblMain.Name". Plus, I need to be able to apply this logic to each
field on the form (keep in mind that each of the 10 fields on the form
could either have a value or be left blank).
Any ideas how I can do this either in a query or in a VBA proceedure?
I'm thinking I could set the Record Source property of frmResults to a
SQL statement, but I can't figure out how to write the SQL statement to
do what I wrote in the previous paragraph. If there is some entirely
different way of doing this, please feel free to enlighten me!
Thanks Greatly!!!
BF