Invalid Use of Null (1 Viewer)

[YNWA]

Hi.

I have a query feeding a form. It displays Active Orders. So the SQL is:

SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Customer ID], Orders.[Order Date], Orders.[Shipped Date], [Order Price Totals].[Price Total] AS [Sub Total], Orders.[Shipping Fee], Orders.Taxes, [Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total], Orders.[Ship Name], Orders.[Ship Address], Orders.[Paid Date], [Orders Status].[status name] AS Status
FROM [Orders Status] RIGHT JOIN (Orders LEFT JOIN [Order Price Totals] ON Orders.[Order ID] = [Order Price Totals].OrderID) ON [Orders Status].[Status ID] = Orders.[Status ID]
ORDER BY Orders.[Order ID] DESC;

It has worked for the past year. However today is gives me an Invalid Use of Null error and will not load.

Any ideas why?

Thanks

 

[jdraw]

What has changed?
Have you tried step debugging -- i'm assuming the error occurs in code behind the form?

 

[YNWA]

Yes it does. It is an error in the query.

I know what query it is, and that query is built up of a couple of tables/queries.

How do I step debug?

 

[YNWA]

The message I get is:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

 

[spikepl]

Errors in Code/SQL that worked for ages but ceases to do so are in many cases caused by data. Copy the db, in tyhe copy erase the data for the last few days and try again.

 

[YNWA]

I aint changed anything. The client has the database and says they haven't changed anything. So they might have and not say.

I have a backup that does work and nothing seems to have changed in the structure.

I will delete some data and see what happens.

 

[YNWA]

Deleted all of Feb data and still getting the message.

 

[jdraw]

YNWA,

I agree with spikepl. It could be a data problem.

However, you have 890+ posts, and it's hard to believe your

How do I step debug?

See the link(s) at bottom of my post. That info will save you a lot of frustration.

PS: Protect that backup. If a copy of the backup does still work, then you ay have some sort of corruption(maybe) in the original.

 

[YNWA]

If I input an order its ok. Its just the displaying of active and completed orders on the forms. I think.

 

[BlueIshDan]

SELECT 
	Orders.[Order ID], 
	Orders.[Employee ID], 
	Orders.[Customer ID], 
	Orders.[Order Date], 
	Orders.[Shipped Date], 
	[Order Price Totals].[Price Total] AS [Sub Total], 
	Orders.[Shipping Fee], 
	Orders.Taxes, 
	[Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total], 
	Orders.[Ship Name], 
	Orders.[Ship Address], 
	Orders.[Paid Date], 
	[Orders Status].[status name] AS Status

FROM 
	[Orders Status] 

	RIGHT JOIN (
		[COLOR="Red"]Orders 
		LEFT JOIN [Order Price Totals] 
			ON Orders.[Order ID] = [Order Price Totals].OrderID
		[/COLOR]) 

	ON [Orders Status].[Status ID] = Orders.[Status ID]

ORDER BY Orders.[Order ID] DESC;

remember you must have a complete query in your braces.

RIGHT JOIN (
               
	RIGHT JOIN (
		SELECT * FROM [COLOR="RoyalBlue"]' MATCHING FIELDS AND ORDER TO THE UPPER QUERY'[/COLOR]
		Orders 
		LEFT JOIN [Order Price Totals] 
			ON Orders.[Order ID] = [Order Price Totals].OrderID
		)

Unless I'm mixing it up with another database' query syntax, it happens from time to time! lol

 

[YNWA]

SELECT 
	Orders.[Order ID], 
	Orders.[Employee ID], 
	Orders.[Customer ID], 
	Orders.[Order Date], 
	Orders.[Shipped Date], 
	[Order Price Totals].[Price Total] AS [Sub Total], 
	Orders.[Shipping Fee], 
	Orders.Taxes, 
	[Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total], 
	Orders.[Ship Name], 
	Orders.[Ship Address], 
	Orders.[Paid Date], 
	[Orders Status].[status name] AS Status

FROM 
	[Orders Status] 

	RIGHT JOIN (
		[COLOR="Red"]Orders 
		LEFT JOIN [Order Price Totals] 
			ON Orders.[Order ID] = [Order Price Totals].OrderID
		[/COLOR]) 

	ON [Orders Status].[Status ID] = Orders.[Status ID]

ORDER BY Orders.[Order ID] DESC;

remember you must have a complete query in your braces.

RIGHT JOIN (
               
	RIGHT JOIN (
		SELECT * FROM [COLOR="RoyalBlue"]' MATCHING FIELDS AND ORDER TO THE UPPER QUERY'[/COLOR]
		Orders 
		LEFT JOIN [Order Price Totals] 
			ON Orders.[Order ID] = [Order Price Totals].OrderID
		)

Unless I'm mixing it up with another database' query syntax, it happens from time to time! lol

Says Syntax error.

The queries are set up using Query Builder not pure SQL.

 

[BlueIshDan]

Did you take out the ' MATCHING FIELDS AND ORDER TO THE UPPER QUERY'

 

[YNWA]

It is working now.

 

[YNWA]

Yes. But now if I go to proper database and try what you said it doesnt work.

Not sure if another step has effected it.

But not working.

 

[jdraw]

??? Does the query you posted in post#1 actually return the data you need?

Is it a multiuser database? Is it a split database FE/BE?

 

[smig]

As your query is a the SQL text of the query builder I suggest you save it into a new name and slowly start removing expressions that you suspect causing the error, until you eliminate where the error come from.

I will start by removing the [Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total] as this is the only calculated field I see.

 

[YNWA]

It did do yes. For the past year.

Now today it doesn't.

 

[YNWA]

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

Order SubTotal:

SELECT [Order Details].[Order ID], Sum(CCur([Unit Price]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal
FROM [Order Details]
GROUP BY [Order Details].[Order ID];

Order Price Totals:

SELECT [Order Details Extended].[Order ID] AS OrderID, Sum([Order Details Extended].[Extended Price]) AS [Price Total]
FROM [Order Details Extended]
GROUP BY [Order Details Extended].[Order ID];

I am useless with code hence my cry for help.

 

[YNWA]

As your query is a the SQL text of the query builder I suggest you save it into a new name and slowly start removing expressions that you suspect causing the error, until you eliminate where the error come from.

I will start by removing the [Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total] as this is the only calculated field I see.

Order Price Totals is calculated to give Sub Total.

it will be the Expressions used to calculate but why would it error today when its had a years worth of data in?

 

[BlueIshDan]

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];