Re: Report Parameters - no results

Hi Allen,

I did use the WHERE clause you suggested, and now I'm getting the "wrong
number of arguments used with function in query expression" error for the
WHERE clause. I noticed that the WHERE clause included the Val function.
Should it be used if the Year field is a now text and not a number? When
doing some research on argument errors in Microsoft Help,
brackets/parenthesis are mentioned as possibly being incorrect, but they
appear correct in the sql.

This is the first Access dB I've created, so I apologize if I'm asking
questions that appear simple.

I appreciate your help :)
Audra

"Allen Browne" wrote:

The WHERE clause I posted earlier is the only suggestion I have if you want
to continue with that approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Audra" <Audra@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7BA4D600-54C3-4B7B-B39C-54A1AEEA422B@xxxxxxxxxxxxxxxx
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

"Allen Browne" wrote:

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.

"Audra" <Audra@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C285B047-49D5-4BF0-B59F-1D69D9B5C30E@xxxxxxxxxxxxxxxx
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



"Allen Browne" wrote:

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

"Audra" <Audra@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:19E50304-D528-4796-B94F-2AF20F38B2E0@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Need help applying SQL statement to Access2000 Query
    ... >WHERE clause, but with an ORDER BY clause. ... >construct the filter string and then open the main form (or report). ... to build the query and open the Main Form or a report ...
    (microsoft.public.access.formscoding)
  • Re: Report Parameters - no results
    ... Is it indicated in the correct spot of my WHERE clause? ... be able to use it for this particular report based on user needs. ... What else can I do to try to correct my sql? ... used with function in query expression" for my where clause. ...
    (microsoft.public.access.reports)
  • RE: Passing Qry as part of Rpt DoCmd.OpenReport
    ... You are, by your description, trying to filter the output of the report. ... If you create a string that is a Where Clause without the word Where, ... And yes my data query is plain vanillia with no where clause only a single ...
    (microsoft.public.access.reports)
  • Re: Report Parameters - no results
    ... 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 "". ... Although the article is about applying a Filter to a form, the code is identical to build the WhereCondition for the report. ... Here is my SQL ...
    (microsoft.public.access.reports)
  • Re: Dim a tables field
    ... appropriate table in the query your report is based on, ... How could I reference a query value in the VBA code to send it to the ... SQL of the report's current recordsource query. ... to it in the code by name, just as in a query expression. ...
    (microsoft.public.access.modulesdaovba)
Loading