Run Query doesn't ask for Params, Run Report does

[mcupito]

I have a report comprised of 3 different queries.

One query simply lists the values in the table.

The second query compares each "Value" to the previous record's "value".

The third query compares each "value" to the previous year's "value" (similar to comparing year-end value's of some Account X).

It's the second and third queries that are bothering me, as they ask for parameters when the report is ran, but not when each query is ran on their own.

The parameter for each query (it's the same for both):

NAV_Tbl.NAV_Date

Here is the SQL for the 2 queries:

SELECT t.NAV_Date, ([NetAssetValue]/[prev_value])-1 AS QTD
FROM (SELECT t.*, (select top 1 [NetAssetValue]  from NAV_Tbl AS t2  where t2.NAV_Date < t.NAV_Date  order by t2.NAV_Date desc) AS prev_value FROM NAV_Tbl AS t)  AS t
WHERE (((([NetAssetValue]/[prev_value])-1) Is Not Null))
ORDER BY t.NAV_Date;

For the year-to-date:

SELECT (y1.NetAssetValue/YearEndValue)-1 AS YTD, (SELECT TOP 1 y2.NetAssetValue
        FROM NAV_Tbl AS y2
        WHERE Year(y2.NAV_Date) = Year(y1.NAV_Date) -1
        ORDER BY y2.NAV_Date DESC
    ) AS YearEndValue, y1.NAV_Date
FROM NAV_Tbl AS y1
ORDER BY y1.NAV_Date;

Even if there is a null value, I don't want it to ask for parameters.

For example: in the test data, the NAV_Tbl has two fields - Date and Value.

For the Year-To-Date query, there will be a record where the Value is 0 because we did not keep track of it at that date in time.(Looking back a year, that is)

How can I stop this from happening?

Thanks!

[NeoPa]

Did you mean :

NAV_Tbl.NAV_Date

or rather :

NAV_Date

[mcupito]

Where at, NeoPa? The Parameter box that pops up when the report is ran does indeed ask for NAV_Tbl.NAV_Dat e.

[NeoPa]

That's what I meant Mark. Not sure I can help in that case I'm afraid. If anything occurs to me I'll come back.

[mcupito]

I'll try to squelch it by using "Is Not Null" - that's the only solution I can think of at the moment. I'll get back to you.