Dynamic queries..?

[kenjoswe]

Hi all,
I have a form with 6 textboxes.
The form is intended to query a table.
A user can leave some textboxes blank and fill in a value in 1-6 (at least one) textboxes and in any combination.

If I try to create fixed queries to handle all possible combinations I will end up with a lot of queries and I will probably lack some queries anyway.

How can I deal with this problem more dynamically?

/Kent J.

 

[tlbroadbent]

You can probably handle every thing in one saved query.

Assume you have

a form - frmSearch
6 text boxes - text1, text2, ..., text6
a table with several fields -
6 fields to search on - col1, col2, ..., col6

Create a query in query designer grid.
[tt]
Field: col1 col2 ... col6
Table: tbl1 tbl1 ... tbl1
Sort:
Show: Y Y Y
Criteria: See below
See below
See below

Note: Criteria for col1 thru col6 are each on a separate line.

Criteria for col1 thru col6:
=Forms!frmSearch.text1 Or Forms!frmSearch.text1 Is Null
=Forms!frmSearch.text2 Or Forms!frmSearch.text2 Is Null[/tt]
Etc.

Alternate criteria to search for partial strings:
Like "*" & Forms!frmSearch.text1 & "*"
Like "*" & Forms!frmSearch.text2 & "*"
Etc.

Save the query then open it using DoCmd.OpenQuery in code for a button click. The form must be open for the query to work properly.

Let me know if you have any questions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kenjoswe]

Terry,

I have not got this to work.

This is how the SQL looks in Access:

SELECT TblKartor.AvsLand, TblKartor.AnkLand
FROM TblKartor
WHERE (((TblKartor.AvsLand)=[Forms]![Urval]![CmbAvsLand])) OR ((([Forms]![Urval]![CmbAvsLand]) Is Null)) OR (((TblKartor.AnkLand)=[Forms]![Urval]![CmbMotLand])) OR ((([Forms]![Urval]![CmbMotLand]) Is Null));

..as you can see I have tried on just two fields.

/Kent J.

 

[tlbroadbent]

Sorry. I see the error of my ways. All the criteria should be on the same line rather than different lines. The OR then becomes AND.

SELECT TblKartor.AvsLand, TblKartor.AnkLand
FROM TblKartor
WHERE (TblKartor.AvsLand=[Forms]![Urval]![CmbAvsLand]
OR [Forms]![Urval]![CmbAvsLand] Is Null)
AND (TblKartor.AnkLand=[Forms]![Urval]![CmbMotLand]
OR [Forms]![Urval]![CmbMotLand] Is Null); Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kenjoswe]

Terry,
Thanks! Now it works better.
But I noted a strange result.
Let's say I put a value in a textbox and run the query.
I'll get a certain result.

If I then blank (null) the same textbox on the form and run the query once again I will have the same result as the first time.

How can I avoid this?


/Kent J.

 

[tlbroadbent]

There is no reason for that to happen unless you have something in other text boxes that effectively filter the same records. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

After you delete the content of the text box do you move to another text box or hit enter to force the update to actually occur? You may be getting the old value of the text box. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.