Re: Report Parameters - no results

If [Year] is a text field, just use:

WHERE (([Forms]![frmInputRelatedFindingsDialog]![Year] Is Null)
OR ([Forms]![frmInputRelatedFindingsDialog]![Year] = "(All Years)")
OR ([Table1].[Year] = [Forms]![frmInputRelatedFindingsDialog]![Year]

--
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:ADF7E163-6481-43DA-A694-4AA4B2CADD05@xxxxxxxxxxxxxxxx
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.

"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: 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: Problem emailing query results
    ... This query generates only one ... Allen Browne - Microsoft MVP. ... "See attached blah blah", True ... assign it to the SQL property of the QueryDef before calling SendObject, ...
    (microsoft.public.access.modulesdaovba)
  • 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)