Hi Allen,
When I entered the expression in the Immediate window, the result was:
(All
Years). Is it indicated in the correct spot of my WHERE clause?
I appreciate the link for your additional suggestion of a more
efficient
way
to run the report, which I will keep for future use. Unfortunately, I
won't
be able to use it for this particular report based on user needs.
What else can I do to try to correct my sql?
Thanks!
Audra
:
You might need to do some investigating to find out what the value of
the
combo actually is.
Open the form, and selet the "(All Years)" value.
Open the Immediate window (Ctrl+G), and enter:
? [Forms]![frmInputRelatedFindingsDialog]![Year]
Whatever result you get when you press Enter, that's what needs to go
into
the WHERE clause in your query.
Quite honestly, I find it much easier to build the WHERE clause from
only
those boxes that that have a value, and you get to ignore the ones
like
"(All Years)". It is also considerably more efficient to execute.
Details
in:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Although the article is about applying a Filter to a form, the code is
identical to build the WhereCondition for the report.
Hi Allen,
I changed the Year field from number to text, and the report now
displays
records, if a specific year is selected- Yeah! But, as you
predicted,
it
doesn't work when I select 'All Years'.
I then modifided the sql with your suggestion, but now I'm getting
an
error
message when I try to open the report as follows, "Wrong number of
arguments
used with function in query expression" for my where clause. Here
is
my
SQL
Where clause:
WHERE ((([Forms]![frmInputRelatedFindingsDialog]![Year] Is Null)
OR
([Forms]![frmInputRelatedFindingsDialog]![Year] = "(All Years)")
OR
([tbl06Audits].[Year] =
CLng(Nz(Val([Forms]![frmInputRelatedFindingsDialog]![Year],0))))
AND((tbl06AuditFindings.[Unit/Function])=[Forms]![frmInputRelatedFindingsDialog]![Unit/Function])
AND ((tbl06AuditFindings.[Topic of
Concern])=[Forms]![frmInputRelatedFindingsDialog]![TopicofConcern])));
Do you have any suggestions on how I can fix my sql? (FYI, I also
have
"All
Records" for my other 2 combo boxes, but I want to make sure I can
get
it
to
work for the Year field first before attempting the other 2 fields.
Thanks so much!
Audra
:
There's a couple of issues here.
Your query is based on a table that has a field named Year. If you
open
that
table in design view, what is the data type of the field named
Year?
Is
it
Number? Date/Time? Text?
Access will treat the unbound combo's value as Text, since one of
the
items
is the text "(All Years)". The query is therefore attempting to
match
Text
against a number. That's probably why it is returing no records.
Of course, when you do choose (All Years) in the combo, that won't
match
any
years either.
You will therefore need a rather convoluted expression in the query
to
get
this to work. Switching the query to SQL View (View menu), the
WHERE
clause
will probably need to be something like this:
WHERE (([Forms]![frmInputRelatedFindingsDialog]![Year] Is Null)
OR ([Forms]![frmInputRelatedFindingsDialog]![Year] = "(All
Years)")
OR ([Table1].[Year] =
CLng(Nz(Val([Forms]![frmInputRelatedFindingsDialog]![Year],0))))
There is also a problem with the field name Year. Access is likely
to
misinterpret that. You might like to rename the combo and the field
if
it
still fails after all this. For a list of the field names to avoid,
see:
http://allenbrowne.com/AppIssueBadWord.html
Hello! I hope someone can help me. No records are displayed
when I
run
my
report.
I created a query-based report that should show results based on
3
combo-box
input parameters on a single form, which is opened via a macro.
The Row Source for one of the unbound fields on the input
parameter
form
is:
SELECT tblYears.[Year] FROM tblYears UNION Select "(All Years)"
as
Bogus
From tblYears ORDER BY [Year];
The Criteria for the Year field in the underlying query is:
[Forms]![frmInputRelatedFindingsDialog]![Year]
When the report is run, the input parameter form pops up and
displays
accurately, showing the combo-box values correctly and lets me
make
selections, however, no matter what I select, the report preview
shows
the
report with no records.
Any suggestions on where I might need to look to correct this?
Thanks!
Audra
