Report on a selected field value

[Aaron]

Hi,
I've been learning access by the seat-of-my-pants at work
here, and I've come across a couple of stumbling blocks. I hope you all
can help. This reports question is actually the simplest of my issues
(I think), but my other issues are with forms and queries/tables, so
I'll post elsewhere for them.

Basically, let's say I have a report on Wal-Mart sales. Each
sale in the Wal-Mart table is linked to a department, and to a location
(separate tables with data about the departments and locations are
joined). I currently have a report ordered by Location (header and
footer), department (header and footer), and sale item (with various
other data about it)--no header and footer on that one as it's part of
the tabular section of the report.

Now, each Wal-Mart location is inside one large report,
called up by the switchboard item "Location Report." There is also a
"Department Report," but I'm assuming the same answer will work for
both...

My boss wants each location to be in a separate report. I'm
hoping I can make the switchboard button cause a query (or whatever the
right function is) that makes you CHOOSE a location (or "all") from a
drop-down list or some other list. Can you tell me how to make this
happen?

Second option is to have 9 (# of locations) different
"Location Reports," but that means 8 more switchboard items and 8 more
reports in the database, and that's just for the location reports. Not
ideal. Plus, I don't even know how to do this.

Please help! Thanks!

-Aaron

 

[Klatuu]

This is fairly straight forward. You need a combo box to list all your
locations. If you have a table that has each location as a unique record,
use the location field from that table in the row source. If you only have
tables where the location code or name is listed multiple times, you can boil
it down to one row per location.

SELECT DISTINCT [location] FROM LocationTable;

Then to filter the report, use the Where argument of the OpenReport method
to return only those records for the selected location. This you can do with
the Click event of a command button:

Dim strWhere As String

strWhere = "[location] = '" & Me.MyCombo & "'"
Docmd OpenReport, "LocationReport", , , strWhere

Now, there are two things that make Access fairly simple to start using if
you are not familiar with VBA. They are the Switchboard and Macros. There
are also two things you should wiene yourself from as fast as you can because
they limit the functionality you can achieve with Access. They are the
Switchboard and Macros.

What I have described above should be done in a small form. You can open
the form from the switchboard, but you should put the combo box to select the
location and the command button to run the report on the form.

 

[Marshall Barton]

I've been learning access by the seat-of-my-pants at work
here, and I've come across a couple of stumbling blocks. I hope you all
can help. This reports question is actually the simplest of my issues
(I think), but my other issues are with forms and queries/tables, so
I'll post elsewhere for them.

Basically, let's say I have a report on Wal-Mart sales. Each
sale in the Wal-Mart table is linked to a department, and to a location
(separate tables with data about the departments and locations are
joined). I currently have a report ordered by Location (header and
footer), department (header and footer), and sale item (with various
other data about it)--no header and footer on that one as it's part of
the tabular section of the report.

Now, each Wal-Mart location is inside one large report,
called up by the switchboard item "Location Report." There is also a
"Department Report," but I'm assuming the same answer will work for
both...

My boss wants each location to be in a separate report. I'm
hoping I can make the switchboard button cause a query (or whatever the
right function is) that makes you CHOOSE a location (or "all") from a
drop-down list or some other list. Can you tell me how to make this
happen?

Second option is to have 9 (# of locations) different
"Location Reports," but that means 8 more switchboard items and 8 more
reports in the database, and that's just for the location reports. Not
ideal. Plus, I don't even know how to do this.

Your "second option" is not an option, but a reseipe for a
maintenance disaster ;-)

The usual way to select data for a report is to create an
unbound form with a combo box where users can select the
location and a button that opens the report. The button's
Click event procedure would then specify the report's where
condition in the combo box:

Dim strWhere As String
If Not IsNull(cboLocation) Then
strWhere = "[location field name] = " + cboLocation
End If
DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere

 

[Aaron]

Marshall Barton wrote:

Ok, I combined the code that both of you were writing to get me
something that works...sort of:

Private Sub Command2_Click()
Dim strWhere As String
If Not IsNull(EIIList) Then
strWhere = "[EII_Acronym] = " ' & EIIList & '
End If
DoCmd.OpenReport "EII_Report", acViewPreview, strWhere
End Sub

Command2 is the button name, as best I can tell.

EIIList is the name of the combo box, as best I can tell (I think I
renamed it correctly)

EII_Report is the Report I'm trying to call.

EII_Acronym is the field I want the report based on.


I'm still getting the entire report, instead of just the part of the
report for that EII_Acronym. Why?

Note: I also made it an "On Change" procedure for the combo box itself.
Same result.

-Aaron

I've been learning access by the seat-of-my-pants at work
here, and I've come across a couple of stumbling blocks. I hope you all
can help. This reports question is actually the simplest of my issues
(I think), but my other issues are with forms and queries/tables, so
I'll post elsewhere for them.

Basically, let's say I have a report on Wal-Mart sales. Each
sale in the Wal-Mart table is linked to a department, and to a location
(separate tables with data about the departments and locations are
joined). I currently have a report ordered by Location (header and
footer), department (header and footer), and sale item (with various
other data about it)--no header and footer on that one as it's part of
the tabular section of the report.

Now, each Wal-Mart location is inside one large report,
called up by the switchboard item "Location Report." There is also a
"Department Report," but I'm assuming the same answer will work for
both...

My boss wants each location to be in a separate report. I'm
hoping I can make the switchboard button cause a query (or whatever the
right function is) that makes you CHOOSE a location (or "all") from a
drop-down list or some other list. Can you tell me how to make this
happen?

Second option is to have 9 (# of locations) different
"Location Reports," but that means 8 more switchboard items and 8 more
reports in the database, and that's just for the location reports. Not
ideal. Plus, I don't even know how to do this.

Your "second option" is not an option, but a reseipe for a
maintenance disaster ;-)

The usual way to select data for a report is to create an
unbound form with a combo box where users can select the
location and a button that opens the report. The button's
Click event procedure would then specify the report's where
condition in the combo box:

Dim strWhere As String
If Not IsNull(cboLocation) Then
strWhere = "[location field name] = " + cboLocation
End If
DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere

 

[Marshall Barton]

When you post code with a question, please use Copy/Paste so
we don't waste time chasing typos.

You have some errors in the posted code. First, the quotes
are wrong. If EII_Acronym is a text field, it should be:
strWhere = "[EII_Acronym] = '" & EIIList & "' "
or if EII_Acronym might possible contain an apostrophe, use:
strWhere = "[EII_Acronym] = """ & EIIList & """ "

If EII_Acronym is a mumeric type field, use:
strWhere = "[EII_Acronym] = " & EIIList

The other error I spotted is the wrong number of commas in
the OpenReport line:
DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere
--
Marsh
MVP [MS Access]

Ok, I combined the code that both of you were writing to get me
something that works...sort of:

Private Sub Command2_Click()
Dim strWhere As String
If Not IsNull(EIIList) Then
strWhere = "[EII_Acronym] = " ' & EIIList & '
End If
DoCmd.OpenReport "EII_Report", acViewPreview, strWhere
End Sub

Command2 is the button name, as best I can tell.

EIIList is the name of the combo box, as best I can tell (I think I
renamed it correctly)

EII_Report is the Report I'm trying to call.

EII_Acronym is the field I want the report based on.


I'm still getting the entire report, instead of just the part of the
report for that EII_Acronym. Why?

Note: I also made it an "On Change" procedure for the combo box itself.
Same result.

Your "second option" is not an option, but a reseipe for a
maintenance disaster ;-)

The usual way to select data for a report is to create an
unbound form with a combo box where users can select the
location and a button that opens the report. The button's
Click event procedure would then specify the report's where
condition in the combo box:

Dim strWhere As String
If Not IsNull(cboLocation) Then
strWhere = "[location field name] = " + cboLocation
End If
DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere

 

[Aaron]

When you post code with a question, please use Copy/Paste so
we don't waste time chasing typos.

I DID use copy/paste. I just modified the code that was posted because
it wasn't working for me. Sorry for being new at programming in Visual
Basic/SQL, but I believe I already indicated that I was new to this in
my original post.

So please don't assume I'm trying to make this harder for you. I WANT
your help.

I'll try this below. What was posted before wasn't working, but perhaps
with the extra explanation you provide below, I can sort it out.

Thank you. I'll let you know if it works!

-Aaron

You have some errors in the posted code. First, the quotes
are wrong. If EII_Acronym is a text field, it should be:
strWhere = "[EII_Acronym] = '" & EIIList & "' "
or if EII_Acronym might possible contain an apostrophe, use:
strWhere = "[EII_Acronym] = """ & EIIList & """ "

If EII_Acronym is a mumeric type field, use:
strWhere = "[EII_Acronym] = " & EIIList

The other error I spotted is the wrong number of commas in
the OpenReport line:
DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere
--
Marsh
MVP [MS Access]

Ok, I combined the code that both of you were writing to get me
something that works...sort of:

Private Sub Command2_Click()
Dim strWhere As String
If Not IsNull(EIIList) Then
strWhere = "[EII_Acronym] = " ' & EIIList & '
End If
DoCmd.OpenReport "EII_Report", acViewPreview, strWhere
End Sub

Command2 is the button name, as best I can tell.

EIIList is the name of the combo box, as best I can tell (I think I
renamed it correctly)

EII_Report is the Report I'm trying to call.

EII_Acronym is the field I want the report based on.


I'm still getting the entire report, instead of just the part of the
report for that EII_Acronym. Why?

Note: I also made it an "On Change" procedure for the combo box itself.
Same result.

Aaron wrote:
I've been learning access by the seat-of-my-pants at work
here, and I've come across a couple of stumbling blocks. I hope you all
can help. This reports question is actually the simplest of my issues
(I think), but my other issues are with forms and queries/tables, so
I'll post elsewhere for them.

Basically, let's say I have a report on Wal-Mart sales. Each
sale in the Wal-Mart table is linked to a department, and to a location
(separate tables with data about the departments and locations are
joined). I currently have a report ordered by Location (header and
footer), department (header and footer), and sale item (with various
other data about it)--no header and footer on that one as it's part of
the tabular section of the report.

Now, each Wal-Mart location is inside one large report,
called up by the switchboard item "Location Report." There is also a
"Department Report," but I'm assuming the same answer will work for
both...

My boss wants each location to be in a separate report. I'm
hoping I can make the switchboard button cause a query (or whatever the
right function is) that makes you CHOOSE a location (or "all") from a
drop-down list or some other list. Can you tell me how to make this
happen?

Second option is to have 9 (# of locations) different
"Location Reports," but that means 8 more switchboard items and 8 more
reports in the database, and that's just for the location reports. Not
ideal. Plus, I don't even know how to do this.

Your "second option" is not an option, but a reseipe for a
maintenance disaster ;-)

The usual way to select data for a report is to create an
unbound form with a combo box where users can select the
location and a button that opens the report. The button's
Click event procedure would then specify the report's where
condition in the combo box:

Dim strWhere As String
If Not IsNull(cboLocation) Then
strWhere = "[location field name] = " + cboLocation
End If
DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere

 

[Aaron]

When you post code with a question, please use Copy/Paste so
we don't waste time chasing typos.

You have some errors in the posted code. First, the quotes
are wrong. If EII_Acronym is a text field, it should be:
strWhere = "[EII_Acronym] = '" & EIIList & "' "
or if EII_Acronym might possible contain an apostrophe, use:
strWhere = "[EII_Acronym] = """ & EIIList & """ "

Bingo! This one did it. The one before probably would work, but I
couldn't figure out where the apostrophes and quotes began and ended...
I hope the second one won't cause errors.

Thank you thank you thank you!

-Aaron

If EII_Acronym is a mumeric type field, use:
strWhere = "[EII_Acronym] = " & EIIList

The other error I spotted is the wrong number of commas in
the OpenReport line:
DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere
--
Marsh
MVP [MS Access]

Ok, I combined the code that both of you were writing to get me
something that works...sort of:

Private Sub Command2_Click()
Dim strWhere As String
If Not IsNull(EIIList) Then
strWhere = "[EII_Acronym] = " ' & EIIList & '
End If
DoCmd.OpenReport "EII_Report", acViewPreview, strWhere
End Sub

Command2 is the button name, as best I can tell.

EIIList is the name of the combo box, as best I can tell (I think I
renamed it correctly)

EII_Report is the Report I'm trying to call.

EII_Acronym is the field I want the report based on.


I'm still getting the entire report, instead of just the part of the
report for that EII_Acronym. Why?

Note: I also made it an "On Change" procedure for the combo box itself.
Same result.

Aaron wrote:
I've been learning access by the seat-of-my-pants at work
here, and I've come across a couple of stumbling blocks. I hope you all
can help. This reports question is actually the simplest of my issues
(I think), but my other issues are with forms and queries/tables, so
I'll post elsewhere for them.

Basically, let's say I have a report on Wal-Mart sales. Each
sale in the Wal-Mart table is linked to a department, and to a location
(separate tables with data about the departments and locations are
joined). I currently have a report ordered by Location (header and
footer), department (header and footer), and sale item (with various
other data about it)--no header and footer on that one as it's part of
the tabular section of the report.

Now, each Wal-Mart location is inside one large report,
called up by the switchboard item "Location Report." There is also a
"Department Report," but I'm assuming the same answer will work for
both...

My boss wants each location to be in a separate report. I'm
hoping I can make the switchboard button cause a query (or whatever the
right function is) that makes you CHOOSE a location (or "all") from a
drop-down list or some other list. Can you tell me how to make this
happen?

Second option is to have 9 (# of locations) different
"Location Reports," but that means 8 more switchboard items and 8 more
reports in the database, and that's just for the location reports. Not
ideal. Plus, I don't even know how to do this.

Your "second option" is not an option, but a reseipe for a
maintenance disaster ;-)

The usual way to select data for a report is to create an
unbound form with a combo box where users can select the
location and a button that opens the report. The button's
Click event procedure would then specify the report's where
condition in the combo box:

Dim strWhere As String
If Not IsNull(cboLocation) Then
strWhere = "[location field name] = " + cboLocation
End If
DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere

 

[Marshall Barton]

Sorry if my advice didn't come across as intended. I was
just trying to help you help us help you reach a
satisfactory solution ;-)

Quoting quotes is a confusing topic. Just try to wrap your
head around the following explanation if you had any
thoughts of it being straightforward ;-)

A general rule is to use two double quotes where you want
one double quote inside an outer set of double quotes.

In queries, but not in VBA, an alternative is to use a
single quote (apostrophe) inside the outer double quotes
instead of two double quotes (or vice versa). This makes
things a little easier to read, but the odds of a name
containing an apostrophe (e.g. O'Hare) are fairly high so
sometimes you can run into trouble using this syntax.
--
Marsh
MVP [MS Access]

When you post code with a question, please use Copy/Paste so
we don't waste time chasing typos.

I DID use copy/paste. I just modified the code that was posted because
it wasn't working for me. Sorry for being new at programming in Visual
Basic/SQL, but I believe I already indicated that I was new to this in
my original post.

So please don't assume I'm trying to make this harder for you. I WANT
your help.

I'll try this below. What was posted before wasn't working, but perhaps
with the extra explanation you provide below, I can sort it out.

You have some errors in the posted code. First, the quotes
are wrong. If EII_Acronym is a text field, it should be:
strWhere = "[EII_Acronym] = '" & EIIList & "' "
or if EII_Acronym might possible contain an apostrophe, use:
strWhere = "[EII_Acronym] = """ & EIIList & """ "

If EII_Acronym is a mumeric type field, use:
strWhere = "[EII_Acronym] = " & EIIList

The other error I spotted is the wrong number of commas in
the OpenReport line:
DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere

Ok, I combined the code that both of you were writing to get me
something that works...sort of:

Private Sub Command2_Click()
Dim strWhere As String
If Not IsNull(EIIList) Then
strWhere = "[EII_Acronym] = " ' & EIIList & '
End If
DoCmd.OpenReport "EII_Report", acViewPreview, strWhere
End Sub

Command2 is the button name, as best I can tell.

EIIList is the name of the combo box, as best I can tell (I think I
renamed it correctly)

EII_Report is the Report I'm trying to call.

EII_Acronym is the field I want the report based on.


I'm still getting the entire report, instead of just the part of the
report for that EII_Acronym. Why?

Note: I also made it an "On Change" procedure for the combo box itself.
Same result.


Aaron wrote:
I've been learning access by the seat-of-my-pants at work
here, and I've come across a couple of stumbling blocks. I hope you all
can help. This reports question is actually the simplest of my issues
(I think), but my other issues are with forms and queries/tables, so
I'll post elsewhere for them.

Basically, let's say I have a report on Wal-Mart sales. Each
sale in the Wal-Mart table is linked to a department, and to a location
(separate tables with data about the departments and locations are
joined). I currently have a report ordered by Location (header and
footer), department (header and footer), and sale item (with various
other data about it)--no header and footer on that one as it's part of
the tabular section of the report.

Now, each Wal-Mart location is inside one large report,
called up by the switchboard item "Location Report." There is also a
"Department Report," but I'm assuming the same answer will work for
both...

My boss wants each location to be in a separate report. I'm
hoping I can make the switchboard button cause a query (or whatever the
right function is) that makes you CHOOSE a location (or "all") from a
drop-down list or some other list. Can you tell me how to make this
happen?

Second option is to have 9 (# of locations) different
"Location Reports," but that means 8 more switchboard items and 8 more
reports in the database, and that's just for the location reports. Not
ideal. Plus, I don't even know how to do this.


Marshall Barton wrote:
Your "second option" is not an option, but a reseipe for a
maintenance disaster ;-)

The usual way to select data for a report is to create an
unbound form with a combo box where users can select the
location and a button that opens the report. The button's
Click event procedure would then specify the report's where
condition in the combo box:

Dim strWhere As String
If Not IsNull(cboLocation) Then
strWhere = "[location field name] = " + cboLocation
End If
DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere

 

[Marshall Barton]

It's good to hear that it's working.

One way to figure out which quotes are where is to click in
their area and use the right and left arrow keys to move
through them one character at a time.

As I kind of implied earlier, I personally prefer the syntax
that you chose to use for the same reason. The only way
that could cause a problem is if the value in EIIList
contained a double quote character, which should be extemely
unlikely.
--
Marsh
MVP [MS Access]

When you post code with a question, please use Copy/Paste so
we don't waste time chasing typos.

You have some errors in the posted code. First, the quotes
are wrong. If EII_Acronym is a text field, it should be:
strWhere = "[EII_Acronym] = '" & EIIList & "' "
or if EII_Acronym might possible contain an apostrophe, use:
strWhere = "[EII_Acronym] = """ & EIIList & """ "

Bingo! This one did it. The one before probably would work, but I
couldn't figure out where the apostrophes and quotes began and ended...
I hope the second one won't cause errors.

Thank you thank you thank you!

If EII_Acronym is a mumeric type field, use:
strWhere = "[EII_Acronym] = " & EIIList

The other error I spotted is the wrong number of commas in
the OpenReport line:
DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere