Hey there,
Created code in SQL server, I need to deliver these queries in Access SQL or to my end users- Just having a little trouble getting it converted over: Here is what I created in SQL (which works great)-
Code:
USE *********
SELECT SS.DatePromised
, SS.Customername
, SS.CustomerNum
, ID.MfgCode
, ID.CatNum
, ID.ProductDescription
, ID.Price
, ID.PriceUOM
, SS.OrigRegNum
, SS.CustPO
, PO.PONum
, ID.OrderQty
, PO.PORecdQTY1
, PO.PORecdDate1
, PO.PORecdQTY2
, PO.PORecdDate2
, PO.PORecdQTY3
, PO.PORecdDate3
, ID.SalesType
, SS.SalesRepNumIN
, SS.SalesRepNumOUT
FROM (dbo.OpenInvoiceDetail AS ID INNER JOIN dbo.openSalesSummary AS SS ON ID.OpenSalesSummaryID = SS.OpenSalesSummaryID)
LEFT JOIN dbo.PODetail AS PO ON SS.OrigRegNum = PO.BOCustRegNum1 AND ID.CatNum = PO.CatNum
WHERE SS.DatePromised < getdate()
ORDER BY ID.OpenSalesSummaryID, Id.InvLine;
Now I know in access its a little different - I was told that I need to use pass through queries - So I took a stab at it...
pass through query
Code:
SELECT SS.DatePromised, SS.Customername, SS.CustomerNum, ID.MfgCode, ID.CatNum, ID.ProductDescription, ID.Price, ID.PriceUOM, Cint(SS.OrigRegNum) AS OrigRegNum, SS.CustPO, ID.OrderQty, ID.SalesType, SS.SalesRepNumIN, SS.SalesRepNumOUT
FROM OpenInvoiceDetail AS ID INNER JOIN openSalesSummary AS SS ON ID.OpenSalesSummaryID = SS.OpenSalesSummaryID;
Then I referred to it with this query:
Code:
SELECT openinvoicesub.*, PODetail.PONum, PODetail.PORecdQty1, PODetail.PORecdDate1, PODetail.PORecdQty2, PODetail.PORecdDate2, PODetail.PORecdQty3, PODetail.PORecdDate3
FROM openinvoicesub LEFT JOIN PODetail ON (openinvoicesub.CatNum = PODetail.CatNum) AND (openinvoicesub.OrigRegNum = PODetail.BOCustRegNum1)
WHERE openinvoicesub.DatePromised < [Date];
Now I am getting the error "This expression is typed incorrectly, or is too complex to be evaluated..."
Any guidance here would be greatly appreciated, as I have already spent waaay too much time tooling with access to get it to work. If only I could just use SQL server : /
Thanks in advance!