Help...Access 2007 crashes when I run my query

[trisoft]

When I run my query access crashes. If I remove the SuperPrice select statement it works fine. I'm using Windows7 Ultimate 64, Access 2007 SP2. Any help would be appreciated.

SELECT Parts.*, DealerParts.Bin, DealerParts.Note, (SELECT RetailPrice FROM Parts P WHERE P.PartNumber=Parts.SuperceededBy) AS SuperPrice
FROM Parts LEFT JOIN DealerParts ON Parts.PartNumber=DealerParts.PartNumber
WHERE DateDiff('d',parts.dateadded,Now())<=365
ORDER BY Parts.PartNumber;

Thanks,

Richard..

 

[vbaInet]

Strange one. How many fields does your Parts table contain? And how many records does it contain too?

Can you post a stripped-down version of your db so we can replicate the problem.

Welcome to AWF by the way

 

[boblarson]

If you run just this part of the query:

SELECT RetailPrice FROM Parts P WHERE P.PartNumber=Parts.SuperceededBy

Does it return a SINGLE record? It would need to do so in order for it to work with your current query.

 

[dfenton]

If you run just this part of the query:

SELECT RetailPrice FROM Parts P WHERE P.PartNumber=Parts.SuperceededBy

Does it return a SINGLE record? It would need to do so in order for it to work with your current query.

Since it's a correlated subquery, you can't run it by itself.

 

[trisoft]

I have come up with a temporary solution. If I remove the ORDER BY clause it works just fine! go figure.

Thanks for all your help guys!

Richard..

 

[vbaInet]

Hmm... I don't see how an ORDER BY could have caused the problem. I think your problem could lie in P.PartNumber=Parts.SuperceededBy. Are you sure those two fields are essentially the same or shouldn't you be linking it with PartNumber field? That is: P.PartNumber=Parts.PartNumber.