Search with multiple lines

[Marilor]

Hello all, I am trying to create a search form, but it involves multiple lines, so I don’t know if it’s possible or not.


S1 AGENC = “DIA” OR AGENC = “DOS”

S2 SET = "SOUTHERN CONE"

S3 S1 AND S2

S4

S5


This is kind of what the form would look like, now the user can put criteria in only the first line or in multiple lines and can refer to the previous line with the name of it, just like the example shows. I am at a complete lost, any kind of ideas would be much appreciated. Is this crazy??

Thanks,
Maria

 

[afloyd]

I just finished a similar exercise recently. I have a form with four unbound text boxes the user can enter criteria in. I had to use 13 SQL strings to cover all the bases in order to run the search. This probably wasn't the most efficient way, but it did work in the end.

The basis for my solution was to do a NULL check first to establish which criteria had been entered.

IF ISNULL (CRITERIA 1) = False AND
IF ISNULL (CRITERIA 2) = TRUE THEN

search on CRITERIA 1 using SQL SELECT string setting the WHERE clause to CRITERIA 1.

I had to repeat this for each condition CRITERIA 1 NULL, CRITERIA 2 NOT NULL, etc.

All this was done in a callback function so I could display the results in a list box on my form.


I hope this makes sense. It took me several weeks to get the syntax down. I wish I would have known about this message board then!

-Al

 

[Marilor]

ok, but...

Thanks afloyd,

your suggestion solves part of my problem, but know i need to determine if the user entered the name of the criteria line instead of the criteria itself, like it said in my first post, I could have

S1: Agenc = "DIA"
S2: Set = "Argentina"
S3: S1 AND S2


so it's when I go down to S3 that my problem starts. I don't know if this makes sense or not, but that's the way they want their search if it's possible.

Thanks again,
Maria

 

[afloyd]

I hope that can help clarify..I think you need to group you criteria in your WHERE clause, i.e.

SELECT fields
FROM tables
WHERE "table.field = S1" AND/OR "Table.field = S2" AND/OR "Table.field = S1 AND S2"

I hope this doesn't confuse the issue further.

-Al

 

[Marilor]

???

Hey Al, i don't really understand what you mean... or maybe you don't understand what I want, because it's hard to explain.

S1: Agenc = "DIA"
S2: Set = "Argentina"
S3: S1 and S2

where when I translate S3 it should be
Agenc = "DIA" and Set = "Argentina"

I hope this clarifies it a little bit.

Thanks,
Maria

 

[afloyd]

If I understand correctly -

the first search criteria (S1) should find all the records in table.AGENC = "DIA" regardless of what is in table.SET

the second search criteria (S2) should find all the records in table.SET = "ARGENTINA" regardless of what is in table.AGENC

the third criteria (S1 AND S2) should find all the records that have table.AGENC = "DIA" AND table.SET = "ARGENTINA"

except that where I'm hard coding DIA and ARGENTINA it actually references the unbound text boxes on the search form.

Is this correct? More imortant question - are AGENC and SET in the same table?

 

[Marilor]

Yep!!!

Yes, that's it!!!! What you said is right, and also the fields AGENCY and SET are in the same table.


Maria

 

[afloyd]

OK! let's solve this bad boy - you need three search strings. one that looks at tbl.AGENCY, one that looks at tbl.SET and the third is the combination


SELECT *
FROM TABLE
WHERE tbl.AGENCY = S1


SELECT *
FROM TABLE
WHERE tbl.SET = S2


SELECT *
FROM TABLE
WHERE "tbl.AGENCY = S1" AND "tbl.SET = S2"

make each one of these search strings a recordset and then you manipulate them to be displayed in a combo box. If you try to use a list box you will need to code a call back function.

you can do this in query design view by setting the criteria row = to the seach form unbound text box name. The third search will have two separate entries. (remember to make three different queries)

 

[Marilor]

thanks..

Thank you Al, i will try what you suggested and keep you posted, it might take me a while because i have a busy week.

Talk to you later,
Maria