Invalid use of Null

[Guest]

Hi there, here is my problem...

I have a query for Design and Development Costs, grouping values by
Business, FiscalYear, Quarter and Project. I also have a query Forecast that
group the forecast data by Project and FiscalYear, giving the values by
quarters (Not every project has a specified forecast)

I am designing a report which should show all Design information with the
respective forecast so:

SELECT [Design - PreFilter].Business, [Design - PreFilter].FiscalYear,
[Design - PreFilter].Quarter, [Design - PreFilter].ProjectID, [Design -
Forecast].TotalForecast
FROM [Design - PreFilter] LEFT JOIN [Design - Forecast] ON ([Design -
PreFilter].FiscalYear = [Design - Forecast].Year) AND ([Design -
PreFilter].ProjectID = [Design - Forecast].Project_ID);

* I have simplified the query to show my problem

Instead of get a NULL value as TotalForecast as usual for those projects
with no forecast, I am getting a #ERROR. In my complete query, which is a
Group Query, I get the error: Invalid use of Null.

[Design - PreFilter] and [Design - Forecast] are queryes that filter Null's
out.

Can you help me?

Thanks

Mauricio Silva

 

[Peter R. Fletcher]

Generally speaking, you get this error if you pass a Null as an
argument to a Function (one of yours or one of Access's) that expects
a Number, a Date, or a String, none of which can be Null. You may be
doing this in one of your subQueries.

Hi there, here is my problem...

I have a query for Design and Development Costs, grouping values by
Business, FiscalYear, Quarter and Project. I also have a query Forecast that
group the forecast data by Project and FiscalYear, giving the values by
quarters (Not every project has a specified forecast)

I am designing a report which should show all Design information with the
respective forecast so:

SELECT [Design - PreFilter].Business, [Design - PreFilter].FiscalYear,
[Design - PreFilter].Quarter, [Design - PreFilter].ProjectID, [Design -
Forecast].TotalForecast
FROM [Design - PreFilter] LEFT JOIN [Design - Forecast] ON ([Design -
PreFilter].FiscalYear = [Design - Forecast].Year) AND ([Design -
PreFilter].ProjectID = [Design - Forecast].Project_ID);

* I have simplified the query to show my problem

Instead of get a NULL value as TotalForecast as usual for those projects
with no forecast, I am getting a #ERROR. In my complete query, which is a
Group Query, I get the error: Invalid use of Null.

[Design - PreFilter] and [Design - Forecast] are queryes that filter Null's
out.

Can you help me?

Thanks

Mauricio Silva

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Guest]

The subqueries work fine by themselves, and I have a clause where to
eliminate NULL from coming to this query. The problem only happens when I put
them both together. :/

Generally speaking, you get this error if you pass a Null as an
argument to a Function (one of yours or one of Access's) that expects
a Number, a Date, or a String, none of which can be Null. You may be
doing this in one of your subQueries.

Hi there, here is my problem...

I have a query for Design and Development Costs, grouping values by
Business, FiscalYear, Quarter and Project. I also have a query Forecast that
group the forecast data by Project and FiscalYear, giving the values by
quarters (Not every project has a specified forecast)

I am designing a report which should show all Design information with the
respective forecast so:

SELECT [Design - PreFilter].Business, [Design - PreFilter].FiscalYear,
[Design - PreFilter].Quarter, [Design - PreFilter].ProjectID, [Design -
Forecast].TotalForecast
FROM [Design - PreFilter] LEFT JOIN [Design - Forecast] ON ([Design -
PreFilter].FiscalYear = [Design - Forecast].Year) AND ([Design -
PreFilter].ProjectID = [Design - Forecast].Project_ID);

* I have simplified the query to show my problem

Instead of get a NULL value as TotalForecast as usual for those projects
with no forecast, I am getting a #ERROR. In my complete query, which is a
Group Query, I get the error: Invalid use of Null.

[Design - PreFilter] and [Design - Forecast] are queryes that filter Null's
out.

Can you help me?

Thanks

Mauricio Silva

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher