Re: Report Parameters - no results

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.

--
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: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: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Using SQL statement to append a recordset to a table
    ... To insert a value into the field Priority: ... > all failed mean while each time my report was working fine. ... Used the query> wizard "Find Unmatched Query Wizard" to create a query between these two> tables, changed to an append query, appending to the ... > table (looking at the sql view along the way). ...
    (microsoft.public.access.formscoding)
  • Re: Creating Access DB
    ... database and in the SQL database that your report needs. ... click the query tab. ...
    (microsoft.public.excel.programming)
Loading