Help with an error message that shows as "#Error"

[smurf01]

I have a multitable query where I am using all the records from the left query and only records from the right query that match one of the fields from the right query is "StartDateTime" which is formatted a shown

23/03/2004 09:34:00

My problem is that where the field contains no dateTime then I am getting the error message in the field as shown below

#Error

Can anyone explain what this error means please



Regards

Paul

 

[Golom]

Probably because the value is NULL. You can either use the NZ function to convert nulls to something that can be processed or you can use

WHERE DateField IS NOT NULL

to eliminate nulls in the output.

 

[smurf01]

Golom Thanks for the speedy reply I have tried the Nz function without any success. I do need to show the rows where the "#Error" message is, any other ideas

Regards

Paul

 

[TonyJollans]

Hi smurf01,

What field are you getting #Error in? I would only normally expect it where a Function is involved, not in a normal field (null or otherwise).

Can you post the SQL you are using?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[smurf01]

Tony,
Thanks for responding I am posting the SQL for you to look at The field I am having problems with is QryMDWorkip.StartDateTime this looks for all orders that are in progress then I have a left join on QryMDLoads. The problem is that QryLoads has more records than QryWorkip and as I need to show all records from QryLoads when I run this query then if there is a corresponding record it shows the date&time otherwise it shows #Error

SELECT QryMDLoads.DueDate, QryMDLoads.ST, QryMDLoads.Customer, QryMDLoads.SpecNo, QryMDLoads.MBNo, QryMDLoads.OrderQty, QryMDLoads.NumPallets, QryMDLoads.Product, IIf([QryMDStockLoads]![StockPallets]>0,[QryMDStockLoads]![StockPallets],0) AS StPallets, IIf([QryMDStockLoads]![StockQty]>0,[QryMDStockLoads]![StockQty],0) AS StQty, [COLOR=red]QryMDWorkip.StartDateTime,[/color red] QryMDWorkip.MBNum, QryMDWorkip.EstQty
FROM (QryMDLoads LEFT JOIN QryMDStockLoads ON QryMDLoads.SpecNo = QryMDStockLoads.SpecNo) [COLOR=red]LEFT JOIN QryMDWorkip ON QryMDLoads.MBNo = QryMDWorkip.MBNum[/color red]
ORDER BY IIf([QryMDStockLoads]![StockPallets]>0,[QryMDStockLoads]![StockPallets],0) DESC , IIf([QryMDStockLoads]![StockQty]>0,[QryMDStockLoads]![StockQty],0) DESC;

Regards

Paul

 

[TonyJollans]

Hi Paul,

I will have a proper look in a minute but my immediate question is .. What does this field show as in the underlying query? .. Can you post the SQL for that as well?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Golom]

Try this. I have made a few cosmetic changes for readability and included the NZ function where NULLs may be causing a problem.
[blue][tt]
SELECT L.DueDate, L.ST, L.Customer, L.SpecNo, L.MBNo,
L.OrderQty, L.NumPallets, L.Product,

IIf(NZ(S.StockPallets)>0,NZ(S.StockPallets),0) AS StPallets,

IIf(NZ(S.StockQty)>0,NZ(S.StockQty),0) AS StQty,

W.StartDateTime, W.MBNum, W.EstQty

FROM (QryMDLoads L LEFT JOIN QryMDStockLoads S ON L.SpecNo = S.SpecNo)
LEFT JOIN QryMDWorkip W ON L.MBNo = W.MBNum

ORDER BY 9 DESC , 10 DESC;
[/tt][/blue]

 

[lespaul]

What a pretty query, golom!

Leslie

 

[Golom]

Leslie
Thank you. Only a true technophile could look at something like that and describe it as "pretty". Hadn't seen you around TT for a while. "Real work" to do?

 

[lespaul]

Yeah, our courthouse moved in January and I've been playing at Network Administrator! Setting up printers on the network, setting up PC's, basic troubleshooting ALL OVER!! Can't imagine how many things don't work after a move!

I'm finally back at my "Real" job, sitting at my desk, browsing threads and occasionally doing some programming. Much happier now!

And OK, maybe pretty wasn't the proper word, let's try "elegant". How's that?

Les

 

[Golom]

OK ... I'll buy that. It does remind me of a comment by a colleague many years ago. I had described some code as "elegant" and his response was "Women are elegant. Code is merely clever." ... Oh, well ...