Re: Report Parameters - no results

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: export form filtered data to excel
    ... ' There's no ORDER BY in the SQL. ... ' Remove the semi-colon from the end, then append the WHERE clause ... Or, better yet, look at the actual SQL for your query and see what's ... Dim qdfTemp As DAO.QueryDef ...
    (microsoft.public.access.formscoding)
  • Re: Query to Count Duplicate Values in a Given Date Range
    ... Show" to note whether the client showed up at the pantry. ... Now, if I try to run the query with that field, it returns no results. ... Here's my final SQL code if you see anything else that may need adjusting. ... use a WHERE clause rather than HAVING for the dates, ...
    (microsoft.public.access.queries)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)