Generic Queries

[Guest]

I have a cool little DB that for the most part does what I want. I am
developing it more and more, particularly on the reporting side now. I have
written some stand alone queries (as opposed to queries inside the report)
that I use for reporting. But I'm finding more and more that I want to use
the same, generic, queries but with small tweaks for different things, but I
don't really want a whole lot of very similar queries. For example, I have
an "Invoiced" query that shows me everything that has been invoiced. Now I
want to use this as the basis for a report, linked to my client, so that I
can produce a statement. I can simply do this adding the appropriate
criteria to a new, but otherwise exactly same query. Or I could redesign the
query 'inside' the report. Either way, I still have 2 query's really doing
the work of one generic one. And, i have many examples of this. Any
thoughts, ideas, comments, or heckles are welcome.

 

[Guest]

In the criteria section of the QBE grid, add something like the following:

[Enter True or False for Invoiced]

The square brackets are important because Access will now assume the what's
inside is a value. Since it won't know what "Enter True or False for
Invoiced" is, it will prompt you. Now enter either True or False and hit OK
and the query will run.

Alternatively, yoy can use a dialog form to supply the value and add the
control to the QBE criteria:

[Forms]![frmDialog]![txtBoxName]

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Albert D. Kallal]

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typically designed for
user interface face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions inside of the query.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

 

[Guest]

You can use parameters in a query to enable you to restrict its result set
differently at runtime. Say you have a report based on a query which
includes a ClientID column. In query design view you can put the following
in the criteria row of the ClientID column:

[Enter Client ID:]

When you open the report you'll be prompted to enter the Client ID and the
report will be restricted to rows where the ClientID column's values match
the one you entered.

A much better way of doing the same thing however, would be to reference a
control on a form as the parameter. The form can be a bound one or an
unbound dialogue form. With a bound form based on a Clients table for
instance you could add a button to open a report for the currently selected
client by making the parameter a reference to the form's ClientID control,
e.g.

Forms!frmClients!ClientID

With an unbound dialogue form you could have a combo box listing all clients
and reference the combo box in the same way. When a client is selected in
the combo box and a button on the form clicked to open the report the report
would show rows for the selected client only.

parameters can be more complex than this, for instance you can restrict by a
date range by having start and end date text boxes on a dialogue form and
referencing thse in the query: e.g.

PARAMETERS
Forms!frmInvoiceDlg!txtStartDate DATETIME,
Forms!frmInvoiceDlg!txtEndtDate DATETIME;
SELECT *
FROM Invoices
WHERE Invoicedate >= Forms!frmInvoiceDlg!txtStartDate
AND Invoicedate < Forms!frmInvoiceDlg!txtEndDate +1;

Note two things about this query; firstly the parameters are declared as
DATETIME data type. This is particularly important with dates as a value
entered into a control on a form in short date format can be interpreted as
an arithmetical expression, not a date if the parameter is not declared.
This will give the wrong results. Secondly, note how the date range is
defined by being on or after (>=) the start date and before (<) the day after
the end date. This takes account of possibly sloppy data where the 'dates'
might inadvertently include non-zero times of day (there is no such thing in
Access as a date value without a time of day). If a BETWEEN…AND operation
were used to define the date range any rows with dates with non-zero times of
day on the last day of the range would be omitted. Don't assume that if you
can't see a time of day in a field it doesn't include one; the formatting
might well be hiding the time. Where a field should not include non-zero
times of day this should be prevented in the table design, but it is not
often done so.

You can also make parameters optional by testing them for NULL, e.g.

PARAMETERS
Forms!frmInvoiceDlg!txtStartDate DATETIME,
Forms!frmInvoiceDlg!txtEndtDate DATETIME;
SELECT *
FROM Invoices
WHERE (Invoicedate >= Forms!frmInvoiceDlg!txtStartDate
OR Forms!frmInvoiceDlg!txtStartDate ISNULL)
AND (Invoicedate < Forms!frmInvoiceDlg!txtEndDate +1
OR Forms!frmInvoiceDlg!txtEndDate IS NULL);

In this case both, one or none of the parameters can be given values. If
only a start or end date is given a value all rows after or before the date
will be returned. If both are left blank all rows in the table will be
returned.

Ken Sheridan
Stafford, England

 

[John Vinson]

I have a cool little DB that for the most part does what I want. I am
developing it more and more, particularly on the reporting side now. I have
written some stand alone queries (as opposed to queries inside the report)
that I use for reporting. But I'm finding more and more that I want to use
the same, generic, queries but with small tweaks for different things, but I
don't really want a whole lot of very similar queries. For example, I have
an "Invoiced" query that shows me everything that has been invoiced. Now I
want to use this as the basis for a report, linked to my client, so that I
can produce a statement. I can simply do this adding the appropriate
criteria to a new, but otherwise exactly same query. Or I could redesign the
query 'inside' the report. Either way, I still have 2 query's really doing
the work of one generic one. And, i have many examples of this. Any
thoughts, ideas, comments, or heckles are welcome.

Use Parameter Queries. They're the basis of any productive Access
application!

Rather than "J. R. Jones" as a criterion on the ClientName field, use
either

[Enter client name:]

or, better, use a little unbound form frmCrit with a Combo Box
cboClient selecting the clients, and a criterion

=[Forms]![frmCrit]![cboClient]

Put a command button on frmCrit to launch your report based on the
query, and you don't need to ever even SEE the query, much less change
it.

John W. Vinson[MVP]