Parameter or if Null select all?

[James]

I am trying to make a report that asks for a Branch Name. If the user does
not enter a branch name, I then want it to default to showing all branches.
How do I go about doing this?

 

[adam.vogg]

I think you want your criteria looking like this;

Like Nz([Enter Branch Name], "*")


this way it will return * if left blank, resulting in all branches.
That NZ function simply means to use the 2nd option if the first is
null.

 

[Duane Hookom]

I would create a form with a combo box (cboBranchName) for users to select
the Branch Name. Then use the command button wizard to create a button that
opens your report. Open the code window that was created by the wizard and
change it like:
Dim stDocument as String
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboBranchName) Then
strWhere = strWhere & " AND [Branch Name] =""" & _
Me.cboBranchName & """ "
End If
stDocument ="your report name"
DoCmd.OpenReport stDocument, acPreview, , strWhere

 

[Amy Blankenship]

I am trying to make a report that asks for a Branch Name. If the user does
not enter a branch name, I then want it to default to showing all branches.
How do I go about doing this?

http://msdn2.microsoft.com/en-us/library/aa140073.aspx
http://support.microsoft.com/kb/209261

 

[James]

OK, I just realized something, I am basing the report on a table, not a
query...how to I even ask for criteria when the report is based on a table?
Or is this not possible?

 

[Douglas J. Steele]

If you want to be able to prompt for parameters, you must use a query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, I just realized something, I am basing the report on a table, not a
query...how to I even ask for criteria when the report is based on a
table?
Or is this not possible?

I think you want your criteria looking like this;

Like Nz([Enter Branch Name], "*")


this way it will return * if left blank, resulting in all branches.
That NZ function simply means to use the 2nd option if the first is
null.

 

[Ken Sheridan]

No reason you can't base the report on a table provided you use the
WhereCondition argument of the OpenReport method to open the report. as in
Duane's solution. Another similar approach would be to create an unbound
dialogue form and again add an unbound combo box to the form and a button to
open the report. The RowSource property for the combo box, cboBranchName
say, would be along these lines to list all braches alpahabetically:

SELECT BranchName FROM Branches ORDER BY BranchName;

And the code for the button's Click event procedure would be like this:

Dim strCriteria As String

strCriteria = "BranchName = """ & Me.cboBranchName & _
""" Or " & (Len(Me.cboBranchName & "") = 0)

DoCmd.OpenReport "YourReportNameGoesHere", _
View:=acViewPreview, _
WhereCondition:=strCriteria

This would open the report in print preview, filtering it to the selected
branch or returning all braches if the combo box is left blank. You might
want a second button to print the report, using exactly the same code but
with the omission of the line:

View:=acViewPreview, _

Ken Sheridan
Stafford, England

OK, I just realized something, I am basing the report on a table, not a
query...how to I even ask for criteria when the report is based on a table?
Or is this not possible?

I think you want your criteria looking like this;

Like Nz([Enter Branch Name], "*")


this way it will return * if left blank, resulting in all branches.
That NZ function simply means to use the 2nd option if the first is
null.

 

[James]

Ok, i got this to work...one issue apparently.
If I enter in a branch name the report runs for that branch as expected.
If I don't enter in a branch name it runs for all branches. But I have a
detail footer. That footer now only shows up on the last branch, and is
missing from all other branches...any suggestions?

 

[Duane Hookom]

I don't believe there is a section "detail footer". There are Report, Group,
and Page footers. Perhaps you need to add a Group Footer on Branch.

--
Duane Hookom
Microsoft Access MVP

Ok, i got this to work...one issue apparently.
If I enter in a branch name the report runs for that branch as expected.
If I don't enter in a branch name it runs for all branches. But I have a
detail footer. That footer now only shows up on the last branch, and is
missing from all other branches...any suggestions?

I think you want your criteria looking like this;

Like Nz([Enter Branch Name], "*")


this way it will return * if left blank, resulting in all branches.
That NZ function simply means to use the 2nd option if the first is
null.