Complicated Query?

  • Thread starter Thread starter Vivista Eastbourne
  • Start date Start date
V

Vivista Eastbourne

hi,

Complicated query?

I have 4 sets of searches on a form

For each set there is a lstselectfrom named lstselectfrom1-4

under each of these boxes are a couple of textboxes

txtsearch11
txtsearch12 (only enabled if date range)

txtsearch21
txtsearch22 (only enabled if date range)

txtsearch31
txtsearch32 (only enabled if date range)

txtsearch41
txtsearch42 (only enabled if date range)

the second of each of these boxes only appears if the corresponding
lstselectfrom box is a date field

What I need to do is check each of the search boxes, ignore if null, and
create a query which will set SelectForView=-1

Query will need to allow for either text or date range (dd/mm/yyyy).

I have tried several methods but have been unsuccessful so far

Thanks

A
 
This will become a maintenance nightmare if you try to do all this in the
query.

Instead, build the filter part based on just the boxes you actually want to
filter on. You can then apply this as the Filter of a form or the
WhereCondition of OpenReport. If you must have it as a query, you can tack
this WHERE clause into the query string and assign the entire thing to the
SQL property of the QueryDef.

For an example of how to do that, download this database:
http://allenbrowne.com/unlinked/Search2000.zip
Access 2000 or later.

From your last paragraph, I was not clear if want to apply the techinques
both to date fields and also to text fields (that's fine), or if you have a
text field that contains text that (sometimes?) looks like a date. If the
latter, the filtering will not work properly or efficiently.
 
Thanks for your response Allen,

I have managed to do this in the code as an SQL query for 4 searches.
The idea was simple, but the formatting to get the SQL query just right was
a bit more difficult (as you'd expect).
The zip file showed me exactly howw to achieve my aims, so Many Thanks for
the advise.

Andy
 
Back
Top