Invalid Use of Null (1 Viewer)

[jdraw]

I'm confused with

It did do yes. For the past year.
Now today it doesn't.

and

I notice I get this error on 2 other queries which feed that code above.

When did you try these other 2 queries?
I don't think we're getting the whole story.

 

[YNWA]

I'm confused with


and


When did you try these other 2 queries?
I don't think we're getting the whole story.

The error happened today. The client last used the DB on Friday and it was fine.

The 2 other queries calculate fields to feed into the original query error.

Having just been looking closer at the make up of the original query, I found those 2 queries feeding it. I tried to run them to see if they worked and they both pulled a Null error as reported in the first post.

So I think it is them 2 queries pulling the error not the original query.

Why would I not give the whole story, whats that meant to mean?

 

[YNWA]

SELECT [Order Details Extended].[Order ID] AS [COLOR="red"]OrderID[/COLOR], Sum([Order Details Extended].[Extended Price]) AS [Price Total]
FROM [Order Details Extended]
[COLOR="Red"]GROUP BY [Order Details Extended].[Order ID][/COLOR];

What are you trying to convey?

 

[YNWA]

I think it is down to this bit of code:

Extended Price: CCur([Quantity]*[Unit Price]*(1-[Discount]))

Could that be the issue?

 

[YNWA]

Think I fixed it with Nz function.

 

[BlueIshDan]

By the sounds of your tables, you've got a lot of messy ones.

What I do when I'm rushed to clean a database that I didn't design is create queries that I can call on that represent each dirty table.

something like tblTest would be called from qrySelect_Clean_tblTest.

in that query you would do all of your NZ and formatting and what not.
Remember not to say put any where conditions unless it is to clear out entirely blank records.

 

[YNWA]

By the sounds of your tables, you've got a lot of messy ones.

What I do when I'm rushed to clean a database that I didn't design is create queries that I can call on that represent each dirty table.

something like tblTest would be called from qrySelect_Clean_tblTest.

in that query you would do all of your NZ and formatting and what not.
Remember not to say put any where conditions unless it is to clear out entirely blank records.

Alot of it came from Northwind mate so all over the place.

 

[BlueIshDan]

Alot of it came from Northwind mate so all over the place.

Working in the same situation.

 

[jdraw]

My earlier comment on the whole story relates to
-we haven't seen the table and database design
-we don't know if tables are normalized
-we don't know the relationships or referential integrity available
-we don't know the quality of the data
-we don't know the amount or completeness of data validation
-we don't know if it's single or multiuser
-etc

 

[Brianwarnock]

I think it is down to this bit of code:

Extended Price: CCur([Quantity]*[Unit Price]*(1-[Discount]))

Could that be the issue?

But where was that in any of the queries posted? It is similar to the subtotal however you also said you got the error in the extended price query and there appeared to be no reason there. Are we getting the complete story?

Brian