Solved Alternative to IN clause

[Kayleigh]

Hi
I have the following query as a form recordsource. It run fairly quickly on Jet but now that I have moved to SQL server it crashes the DB. If I would convert to PT it would mean the form is not editable. Are there any alternatives?
This is the SQL Server version:

SELECT * FROM tblorders
WHERE ([fldOrderID] IN (
SELECT DISTINCT (dbo.tblOrders.fldOrderID)
FROM (dbo.tblOrders INNER JOIN (
SELECT dbo.tblAddress.fldAddressID, concat([fldAAddress1], '  ', [fldastreet], ', ', [fldacity], ', ', [fldapostcode]) AS cfAddress,
IIf([fldCBusiness] Is Null, concat([fldClastname], ', ', [fldcfirstname]),[fldCbusiness]) AS cfClient, dbo.tblClients.fldCPhone1, dbo.tblClients.fldCEmail1, dbo.tblClients.fldClientID,
dbo.tblClients.fldCTradeRetailID, dbo.tblAddress.fldAContactName, dbo.tblAddress.fldAContactPhone
FROM dbo.tblClients INNER JOIN dbo.tblAddress ON dbo.tblClients.fldClientID = dbo.tblAddress.fldAClientID
) lkpqryAddress1 ON dbo.tblOrders.fldOAddressID = lkpqryAddress1.fldAddressID) INNER JOIN dbo.tblOrderDetails ON dbo.tblOrders.fldOrderID = dbo.tblOrderDetails.fldODOrderID
WHERE (((dbo.tblOrderDetails.fldODOrdered) Is Not Null) AND ((dbo.tblOrderDetails.fldODReceived) Is Null))));

 

[theDBguy]

Have you tried using a View and then linking to it?

 

[Kayleigh]

Will a view be updated automatically ?

 

[Minty]

Have you pasted that into SSMS and run it to see if it gives you a more meaningful error.

I can't see anything obviously wrong, although prefixing every field with fld would drive me mad typing it everytime...

 

[theDBguy]

Will a view be updated automatically ?

You make a view updateable.

 

[Kayleigh]

Thanks for that advice. I've tried it and it seems to work very successfully!

 

[theDBguy]

Thanks for that advice. I've tried it and it seems to work very successfully!

Not sure which advice that is, but glad to hear you got it sorted out. Good luck with your project.

 

[Kayleigh]

I implemented views. Also appreciate the info about how it updates!