Re: Report on a selected field value

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]


Aaron wrote:
Marshall Barton wrote:
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


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

.