Query fields

[John]

Hi,
Can I make a special form for running querys? Eg. if I want to find
customers account codes beginning with say, ACC******, can I input that in a
form and have it run a query. I have people who wish to find a large range
of different information from a database and I want to design a form that
will allow them do this without them having to go into design view of
queries.
Ideally they could leave many of the fielsd blank and just fill in the ones
they want details on.
John

 

[Nikos Yannacopoulos]

John,

Yes, you can do that. The way to reference a control on a form in a
query criterion, is:

[Forms]![MyFormName]![MyControlName]

where myFormName and MyControlName are just placeholders, they must be
changed to the actual form and control names. So, if you use this a s
the criterion on, say, a name field, and type "Smith" (without the
quotes) in the form control, the query will return all the Smiths in the
table (whole field matches only). Now, if you go back to the query
design and change your criterion to:

Like "*" & [Forms]![MyFormName]![MyControlName] & "*"

the query will return all records where the name field contains the
search string in any position in the field, so you will get the Smiths,
Shithsons, Blacksmiths etc. Also, if you leave the control on the form
blank, the query will now return all records.

You can repeat the above trick on as many text fields as required, and
thus filter on as many as required at runtime.

For numeric fields it's different, though, the above trick won't work
well; if you are looking for number 123, for instance, the above will
return the 123 as well as any other record where the string 123 is part
of the number - which is not desired. You could, of course, use the very
first approach (without the Like), but then you will only get exact
matches, and no records if blank. Alternatively, you could use two
controls on the form, and filter on a range, with a criterion like:

=[Forms]![MyFormName]![Low] And <=[Forms]![MyFormName]![High]

so you could type in a value (twice!) for an exact match, or actually
specify a range when one is desired, or simply key in a range wide
enough to return all records, when that is the desired outcome.
All of those are just half measures, though, I'm afraid; the "neat" way
to treat filtering on numeric fields (and appropriate for text ones, as
well) is to use VBA code to construct a filter string on the fly, which
can then be applied on a listbox/combo or form/report rowsource.

HTH,
Nikos