LOL! As I stated initially, I didn't originate this database. And I'm not
that fluent in Access so I cannot answer your first question. This is how it
was when I received it, and it worked until I changed the CCC values.
My problem is: I went through the database and changed the CCC values for
several employees. There are 3 reports that print out data for a subset of
CCCs. Two of the reports seemed to work after I modified the CCCs and the
related queries (I'm not totally convinced they are completely correct, but
at least they seem to print out reports based on the specified CCCs). The
third report, however, no longer finds employees with the relevent CCCs and
therefore shows a report with no data. On the old reports, before the
changes, the report would have a page for each employee with the specified
CCCs and the employee's CCC would print on the report in the location of the
combo box. Now, the report has no data and the space where the combo box is
supposed to show a CCC is empty.
The report properties says the Record Source is "Individual by 4cccs:
final". I went into that and added ""D555" Or "D556" Or "D558" Or "D590"" as
criteria for the field "Real CCC" that comes from the query "Individual by
4cccs: final-with-unused-columns".
When I tried the report again, I got the message: There was an error
executing the command.
When I went to the query list and double clicked on "Individual by 4cccs:
final" it gave the message: You tried to execute a query that does not
include the specified expression '[individual by 4cccs:
final-with-unused-columns].[Real CCC]="D555" Or [individual by 4cccs:
final-with-unused-columns].[Real CCC]="D556" Or [individual by 4cccs:
final-with-unused-columns].[Real CCC]="D558" Or [individual by 4cccs:
final-with-unused-colum' as part of an aggregate function.
Not sure what that means.
I removed the Or statement from "Individual by 4ccc: final" and put it into
the CCC criteria of the query "Individual by 4cccs:
final-with-unused-columns" and got a blank report.
As for your last question about the query totals, I haven't even begun to
look at that yet, so I don't yet understand how that works.
Does any of this help???
:
I don't understand how you can have a field like:
[Individual by 4cccs: final-with-unused-columns].[Individual by 4cccs:
pre-final-required-only].[Time Used (hours)]
How do you get three periods in a field name? [ ].[ ].[ ]
Is your problem that you aren't getting records to your report or aren't
seeing a value in a control on your report?
Your Record Source query doesn't appear to have an filter. Have you looked
at the Record Source in datasheet view to see if it looks reasonable?
Also, your query is a "totals" query without calculating any aggregates
(SUM, COUNT,...) Is there a reason for this?
--
Duane Hookom
Microsoft Access MVP
:
Sorry, I misunderstood you.
I think this is what you're looking for (I hope so anyway!
)
SELECT [Individual by 4cccs: final-with-unused-columns].[Real CCC],
[Individual by 4cccs: final-with-unused-columns].Course, [Individual by
4cccs: final-with-unused-columns].[Length (hours)], [Individual by 4cccs:
final-with-unused-columns].[Real Badge], [Individual Indentification].Status,
[Individual Indentification].[Last Name], [Individual Indentification].[First
Name], [Individual by 4cccs: final-with-unused-columns].[Have-taken] AS
[Taken-Required], [Individual by 4cccs:
final-with-unused-columns].[Individual by 4cccs:
pre-final-required-only].[Time Used (hours)] AS [Time Used Required],
[Individual by 4cccs: final-with-unused-columns].[Individual by 4cccs:
pre-final-required-only].[Date Completed] AS [Data Completed Required],
[Individual by 4cccs: final-with-unused-columns].[Required-Not -taken] AS
[Required Courses Needed], [Length (hours)]*[Required Courses Needed] AS
[Hours-needed], [Individual by 4cccs: final-with-unused-columns].[Taken-Not
-Required], [Individual by 4cccs: all-course-with-taken-no-required]![Time
Used (hours)] AS [Time Used(Hours)], [Individual by 4cccs:
all-course-with-taken-no-required]![Date Completed] AS [Data Completed]
FROM [Individual Indentification] INNER JOIN [Individual by 4cccs:
final-with-unused-columns] ON [Individual Indentification].[Badge #] =
[Individual by 4cccs: final-with-unused-columns].[Real Badge]
GROUP BY [Individual by 4cccs: final-with-unused-columns].[Real CCC],
[Individual by 4cccs: final-with-unused-columns].Course, [Individual by
4cccs: final-with-unused-columns].[Length (hours)], [Individual by 4cccs:
final-with-unused-columns].[Real Badge], [Individual Indentification].Status,
[Individual Indentification].[Last Name], [Individual Indentification].[First
Name], [Individual by 4cccs: final-with-unused-columns].[Have-taken],
[Individual by 4cccs: final-with-unused-columns].[Individual by 4cccs:
pre-final-required-only].[Time Used (hours)], [Individual by 4cccs:
final-with-unused-columns].[Individual by 4cccs:
pre-final-required-only].[Date Completed], [Individual by 4cccs:
final-with-unused-columns].[Required-Not -taken], [Individual by 4cccs:
final-with-unused-columns].[Taken-Not -Required], [Individual by 4cccs:
all-course-with-taken-no-required]![Time Used (hours)], [Individual by
4cccs: all-course-with-taken-no-required]![Date Completed]
ORDER BY [Individual by 4cccs: final-with-unused-columns].[Real Badge];
:
That looks like the SQL view of the Combo box, not the report. I am not
interested at all in the combo box. As I stated before, "There is nothing
relevant in the combo box on the report to the records displayed in the
report."
Did you find the "Record Source" property? This is the primary property
responsible for what displays in your report. Isn't that what you are
interested in?
--
Duane Hookom
Microsoft Access MVP
:
OK, I found the SQL view. It just says:
SELECT CCC.CCC
FROM CCC;
which is pretty much what it says in RowSource.
What next?
:
I think you already provided this. There is nothing relevant in the combo box
on the report to the records displayed in the report.
Find the Record Source of your report and click the builder button [...] to
get to the query designer. You can then find the SQL view which might be like:
SELECT ... FROM ...
The record source determines what is available to display in your report.
The other important property is the Filter property.
--
Duane Hookom
Microsoft Access MVP
:
I'm not sure exactly what you mean by "SQL view" (I don't know Access very
well), but when I right click on the combo box and select properties, I get
the following on the data tab:
Control Source: Real CCC
Input Mask: [empty]
Row Source Type: Table/Query
Row Source: SELECT CCC.CCC FROM CCC;
Bound Column: 1
Limit To List: Yes
Smart Tags: [empty]
Is this what you're looking for? If not, please tell me how to get the SQL
view.
Thanks.
:
The Row Source of a combo box has a query builder also.
Can you provide the SQL view of your report's record source?
--
Duane Hookom
Microsoft Access MVP
:
Thank you for your quick response. However, isn't that what I was doing when
I put the Or statement into the Query Builder as I described in my initial
post?
As near as I can determine, there is no VBA code in this database.
Cheryl
:
The combo box on the report will have nothing to do with how the report is
filtered. If you want to filter the report, this is done by applying a
criteria to the report's record source or using a WHERE CONDITION in the
DoCmd.OpenReport method.
--
Duane Hookom
Microsoft Access MVP
:
Using Access 2003
I am trying to modify a database that I did not create. On one of the
reports there is a combo box (I'm not sure why a combo box was used here).
The report is supposed to print out any record with a CCC value of D500,
D555, D556, D558, or D590.
The combo box properties show:
Control Source: Real CCC
Row Source Type: Table/Query
Row Source: SELECT CCC.CCC FROM CCC;
I have tried putting
"D500" Or "D555" Or "D556" Or "D558" Or "D590"
into the CCC criteria slot on the Query Builder, but when I run the report,
all I get are two inactive employees with a CCC of D551 (an old, no longer
used CCC) and D551 in the spot where the combo box is located on the report.
It is supposed to bring up a report, one page per employee, with the
employee's CCC (one of the five specified above) in that slot, and only those
employees who have one of those five CCCs. Each employee has only one CCC.
Is this a sufficient explanation for someone to tell me what I need to do to
get the report to give the proper records?
Thank you!
