Report Generator Question

[Tim]

I have a table containing inventory. I have created a form that will act as a
"report generator." Users will select values in unbound combo and text boxes
for criteria. Once the criteria is selected a button will launch a
query/report based on the values.

I have an issue with how to handle some selections. For example I have a
combo titled "cmbLocation" The associated field that will be queried is a
field titled "Location" that contains two possible values (In Store, On Rent.)

I want to approach this one of two ways. One, add a third choice to the
combo box of "All Locations", OR just leave the combo box blank if all
locations is desired. I want to return all values if "All locations" is
selected or possiblly leave the combo box with a null value.

I would prefer the "All Locations" option but being that my field does not
literally contain the value "All Locaitons" I'm not sure how to do this. The
null value option may be the most practical.

Suggestions?


Thanks for any help!!!!


Tim

 

[Albert D. Kallal]

here is what I do:

use the "where" clause, and make the reports sql *without* any
parameters..and you not need to change the params..

So, you can 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.

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 controls 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

There's no question that the above approach means you have to write a little
bit of code, but the advantage is you wind up with a clean report and query
that does not have a bunch of difficult to maintain forms expressions right
in the SQL itself. The above approach also solves the problem of your "all"
question, as the user can simply leave the control blank, and as my screen
shot shows this simply means all in the selection...

 

[Tim]

Albert,

While I truly appreciate your time and effort for the detailed answer,
unfortunatlely this method may be a bit over my head. I might be able to
figure it out eventually, but I would really hope for a simpler solution.

I could be wrong but it really doesn't seem that complex. If the value of
the combo box is null return all records.

Any other suggestions?


Thanks!!!!

Tim

 

[John W. Vinson]

I could be wrong but it really doesn't seem that complex. If the value of
the combo box is null return all records.

Add a criterion

OR [Forms]![YourForm]![ComboBox] IS NULL

to the combo criterion.

 

[Tim]

Well... I tried that and it still doesn't give me all records if the value is
null.
It works fine if I supply values to all the controls but if one is blank I
get no records.
Or It works of I only apply the select criteria to one control. Add it to
the second control and it stops functioning.

This should be very basic. It would function just like the "Filter by Form"
built in option. The form seclects the values for the query, the query
filters the records,
the records are passed to a report.

I'm sure this is done all the time. Any ideas what I'm doing wrong?


Thanks!

Tim

I could be wrong but it really doesn't seem that complex. If the value of
the combo box is null return all records.

Add a criterion

OR [Forms]![YourForm]![ComboBox] IS NULL

to the combo criterion.

 

[John W. Vinson]

Well... I tried that and it still doesn't give me all records if the value is
null.
It works fine if I supply values t

Please post the SQL of your query. Anytime you use OR it can get tricky and
you may need to tweak the parentheses.

 

[Tim]

Thanks John,

Here is my SQL, sorry if it's messy. I'm not really sure how to organize for
readability.

SELECT [Computers-All].Store, [Computers-All].Manufacturer,
[Computers-All].Model, [Computers-All].[Machine Type],
[Computers-All].Location, [Computers-All].Host, [Computers-All].Agent_Name,
[Computers-All].[Serial#], [Computers-All].[Agent_Machine#]
FROM [Computers-All]
GROUP BY [Computers-All].Store, [Computers-All].Manufacturer,
[Computers-All].Model, [Computers-All].[Machine Type],
[Computers-All].Location, [Computers-All].Host, [Computers-All].Agent_Name,
[Computers-All].[Serial#], [Computers-All].[Agent_Machine#]
HAVING ((([Computers-All].Store)=[Forms]![ReportGenerator]![cmbStore] Or
([Computers-All].Store) Is Null) AND
(([Computers-All].Manufacturer)=[Forms]![ReportGenerator]![cmbManufacturer]
Or ([Computers-All].Manufacturer) Is Null) AND
(([Computers-All].Model)=[Forms]![ReportGenerator]![cmbModel] Or
([Computers-All].Model) Is Null) AND (([Computers-All].[Machine
Type])=[Forms]![ReportGenerator]![cmbMachineType] Or
([Computers-All].[Machine Type]) Is Null) AND
(([Computers-All].Location)=[Forms]![ReportGenerator]![cmbLocation] Or
([Computers-All].Location) Is Null));


Thanks again for the help!!!

 

[John Spencer]

SELECT [Computers-All].Store, [Computers-All].Manufacturer,
[Computers-All].Model, [Computers-All].[Machine Type],
[Computers-All].Location, [Computers-All].Host, [Computers-All].Agent_Name,
[Computers-All].[Serial#], [Computers-All].[Agent_Machine#]

FROM [Computers-All]

Where ([Computers-All].Store=[Forms]![ReportGenerator]![cmbStore] Or
[Forms]![ReportGenerator]![cmbStore] Is Null)
AND
([Computers-All].Manufacturer=[Forms]![ReportGenerator]![cmbManufacturer]
Or [Forms]![ReportGenerator]![cmbManufacturer] Is Null)
AND
([Computers-All].Model=[Forms]![ReportGenerator]![cmbModel] Or
[Forms]![ReportGenerator]![cmbModel] Is Null)
AND ([Computers-All].[Machine Type]=
[Forms]![ReportGenerator]![cmbMachineType] Or
[Forms]![ReportGenerator]![cmbMachineType] Is Null)
AND
([Computers-All].Location=[Forms]![ReportGenerator]![cmbLocation] Or
[Forms]![ReportGenerator]![cmbLocation] Is Null)

Access MAY find this too complex to execute.

IF your fields ALWAYS have a value AND they are text fields you can use

SELECT [Computers-All].Store, [Computers-All].Manufacturer,
[Computers-All].Model, [Computers-All].[Machine Type],
[Computers-All].Location, [Computers-All].Host, [Computers-All].Agent_Name,
[Computers-All].[Serial#], [Computers-All].[Agent_Machine#]

FROM [Computers-All]

Where
[Computers-All].Store LIKE NZ([Forms]![ReportGenerator]![cmbStore],"*")
AND
[Computers-All].Manufacturer LIKE
NZ([Forms]![ReportGenerator]![cmbManufacturer],"*")
AND
[Computers-All].Model LIKE NZ([Forms]![ReportGenerator]![cmbModel],"*")
AND [Computers-All].[Machine Type] LIKE
NZ([Forms]![ReportGenerator]![cmbMachineType],"*")
AND
[Computers-All].Location LIKE
NZ([Forms]![ReportGenerator]![cmbLocation],"*")

IF your fields don't ALWAYS have a value You can force the field to have
a value in a query by using a calculated expression.

Where
[Computers-All].Store & "" LIKE
NZ([Forms]![ReportGenerator]![cmbStore],"*")
AND ...

This last criteria will lose the benefit of any indexes you may have on
the field, but with small tables (a few thousand records) there should
be no detectible difference in performance.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Tim]

John, if I could I would buy you lunch!!! Works perfectly!! Thank you very
much!

-Tim

SELECT [Computers-All].Store, [Computers-All].Manufacturer,
[Computers-All].Model, [Computers-All].[Machine Type],
[Computers-All].Location, [Computers-All].Host, [Computers-All].Agent_Name,
[Computers-All].[Serial#], [Computers-All].[Agent_Machine#]

FROM [Computers-All]

Where ([Computers-All].Store=[Forms]![ReportGenerator]![cmbStore] Or
[Forms]![ReportGenerator]![cmbStore] Is Null)
AND
([Computers-All].Manufacturer=[Forms]![ReportGenerator]![cmbManufacturer]
Or [Forms]![ReportGenerator]![cmbManufacturer] Is Null)
AND
([Computers-All].Model=[Forms]![ReportGenerator]![cmbModel] Or
[Forms]![ReportGenerator]![cmbModel] Is Null)
AND ([Computers-All].[Machine Type]=
[Forms]![ReportGenerator]![cmbMachineType] Or
[Forms]![ReportGenerator]![cmbMachineType] Is Null)
AND
([Computers-All].Location=[Forms]![ReportGenerator]![cmbLocation] Or
[Forms]![ReportGenerator]![cmbLocation] Is Null)

Access MAY find this too complex to execute.

IF your fields ALWAYS have a value AND they are text fields you can use

SELECT [Computers-All].Store, [Computers-All].Manufacturer,
[Computers-All].Model, [Computers-All].[Machine Type],
[Computers-All].Location, [Computers-All].Host, [Computers-All].Agent_Name,
[Computers-All].[Serial#], [Computers-All].[Agent_Machine#]

FROM [Computers-All]

Where
[Computers-All].Store LIKE NZ([Forms]![ReportGenerator]![cmbStore],"*")
AND
[Computers-All].Manufacturer LIKE
NZ([Forms]![ReportGenerator]![cmbManufacturer],"*")
AND
[Computers-All].Model LIKE NZ([Forms]![ReportGenerator]![cmbModel],"*")
AND [Computers-All].[Machine Type] LIKE
NZ([Forms]![ReportGenerator]![cmbMachineType],"*")
AND
[Computers-All].Location LIKE
NZ([Forms]![ReportGenerator]![cmbLocation],"*")

IF your fields don't ALWAYS have a value You can force the field to have
a value in a query by using a calculated expression.

Where
[Computers-All].Store & "" LIKE
NZ([Forms]![ReportGenerator]![cmbStore],"*")
AND ...

This last criteria will lose the benefit of any indexes you may have on
the field, but with small tables (a few thousand records) there should
be no detectible difference in performance.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a table containing inventory. I have created a form that will act as a
"report generator." Users will select values in unbound combo and text boxes
for criteria. Once the criteria is selected a button will launch a
query/report based on the values.

I have an issue with how to handle some selections. For example I have a
combo titled "cmbLocation" The associated field that will be queried is a
field titled "Location" that contains two possible values (In Store, On Rent.)

I want to approach this one of two ways. One, add a third choice to the
combo box of "All Locations", OR just leave the combo box blank if all
locations is desired. I want to return all values if "All locations" is
selected or possiblly leave the combo box with a null value.

I would prefer the "All Locations" option but being that my field does not
literally contain the value "All Locaitons" I'm not sure how to do this. The
null value option may be the most practical.

Suggestions?


Thanks for any help!!!!


Tim