Multiple Variable Querying Multiple Fields

[rdecle01]

Re: Multiple Variable Querying Multiple Fields

I now have this query working the way it supposed to on the form. How will I go about allowing variables to be blank. For instance, if I leave all the variables blank I want to get the entire table. Like wise if I leave the title variables blank and type 07C in tg2 I need to get all titles that contain 07C. Here's where I'm at now:

'Date Created: 13-Sep-2006 11:12:26 AM
'Last Updated: 13-Sep-2006 11:46:05 AM
'Created By  : Tom
'Updated By  : Randy

dim t1 as C
t1 = parentform:Listbox1.value
dim t2 as C 
t2 = parentform:Listbox2.value
dim t3 as C 
t3 = parentform:Listbox3.value
dim tg1 as C 
tg1 = parentform:Listbox4.value
dim tg2 as C
tg2 = parentform:Listbox5.value

tbl = table.open("title table")

query.order = ""
query.options = "N"

filter1 = "inlist(title1,t1, t2, t3)"  'filter for the titles

'now do the filter for the groups
filter2 = "inlist2(var->tg1, titleg1+','+titleg2+','+titleg3+','+titleg4+','+titleg5).or. inlist2(var->tg2,titleg1+','+titleg2+','+titleg3+','+titleg4+','+titleg5)"

'now AND them
filter = "("+ filter1 + ") .and. ("+ filter2 +")"

query.filter = filter
ix = tbl.query_create()
tbl.close()

[Tom Cone Jr]

Re: Multiple Variable Querying Multiple Fields

Rob, this difficulty was foreseeable. You will need a separate query filter for each possible combination, or a script that can build the filter expression depending upon which fields are empty and which are filled by the user. Such a thing is possible but very time consuming. I'm not able to volunteer any more assistance. Perhaps someone else will give you a hand. What do you think the next step might be?

-- tom

[rdecle01]

Re: Multiple Variable Querying Multiple Fields

Can you recomend anyone who can write this type of code on a contract basis.

I was going to use this title table example, and attempt to expand it to include all of my search criteria. We maintain a 100,000+ database with many more fields/tables. We are attempting to move our data into Alpha from our antiquated system. We need to query the data often and we run fairly complex querys.

For example, the fields in the various tables of the database include:
Region, Group, State, Zip (we would need to search by zip ranges), Major City Code, Class Code, Title Code, Title Group, PA code, Source Code, Subclass Code, Subclass Description, Area Code.

Thanks for all your help Tom

[Tom Cone Jr]

Re: Multiple Variable Querying Multiple Fields

Rob,

I had some time last night and decided to tackle your problem on spec. Took about an hour and a half to get a model working for you. If interested, call me at 813.949.9698 or email me [email protected].

If you wish to pursue other alternatives information about Alpha Five consultants is available here: http://www.alphasoftware.com/services/default.asp