Hello All,

I am getting a bit stuck on the following sql string, I have an asp classic web page connected to an access 2007 database. I am trying to get the totals from a query called qrycondetailsextended totals, the fields I want from there are sumofextended price, perhaps sumofquantity too but most importantly for now is the sumofextended price field.

Here is my code:

strSQL = "SELECT tbl_ShipOrders.ship_order_id, tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
"tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
"tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, tbl_truck_Details.truckid, qrycondetailsextendedtotals.sumofquantity" & _
"tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Total.sumofquantity AS Total, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
"Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2, Truck.Truck_Registration AS TruckReg " & _
"FROM (tbl_ShipOrders INNER JOIN tbl_Customers AS Sender " & _
"ON tbl_ShipOrders.Sender_ID = Sender.custid) " & _
"INNER JOIN tbl_Customers AS Receiver " & _
"ON tbl_ShipOrders.Receiver_ID = Receiver.custid " & _
"(tbl_shipOrders INNER JOIN qrycondetailsextendedtotals AS Total) " & _
"ON tbl_shipOrders.ship_order_ID = Total.ship_ID " & _
"WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
"AND tbl_ShipOrders.Del=0 " & _
"AND tbl_ShipOrders.Payment_ID=4 " & _
"AND tbl_ShipOrders.Branch_ID = " & Branchid


I get this error:

"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tbl_ShipOrders.Receiver_ID = Receiver.custid tbl_shipOrders INNER JOIN qrycondetailsextendedtotals AS Total ON tbl_shipOrders.ship_order_ID = Total.ship_I'."

Using just:

strSQL = "SELECT tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
"tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
"tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, " & _
"tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
"Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2 " & _
"FROM (tbl_ShipOrders INNER JOIN tbl_Customers AS Sender " & _
"ON tbl_ShipOrders.Sender_ID = Sender.custid) " & _
"INNER JOIN tbl_Customers AS Receiver " & _
"ON tbl_ShipOrders.Receiver_ID = Receiver.custid " & _
"WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
"AND tbl_ShipOrders.Del=0 " & _
"AND tbl_ShipOrders.Payment_ID=4 " & _
"AND tbl_ShipOrders.Branch_ID = " & Branchid


no problems, pulls the details out nicely, the problem is trying to create a further join from the qrycondetailsextendedtotals.

Any and all help will be fully appreciated

Thanks in Advance

Regards

Ben

deleted deleted deleted deleted deleted deleted deleted deleted deleted deleted

Actually, I got this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in expression.

/delivery.asp, line 91

with this code

strSQL = "SELECT tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
"tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
"tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, Total.sumofquantity AS Total, qrycondetailsextendedtotals.sumofquantity, " & _
"tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
"Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2 " & _
"FROM ((tbl_ShipOrders " & _
"INNER JOIN tbl_Customers AS Sender " & _
"ON Sender.custid = tbl_ShipOrders.Sender_ID) " & _
"INNER JOIN tbl_Customers AS Receiver " & _
"ON Receiver.custid = tbl_ShipOrders.Receiver_ID) " & _
"INNER JOIN qrycondetailsextendedtotals AS Total " & _
"ON Total.ship_ID = tbl_shipOrders.ship_order_ID " & _
"WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
"AND tbl_ShipOrders.Del=0 " & _
"AND tbl_ShipOrders.Payment_ID=4 " & _
"AND tbl_ShipOrders.Branch_ID = " & Branchid

Sorry, I had previously put in a syntax error, I meant to say thanks very much for your reply and I got this error, I researched it and it seems to be asp classic and access related, I tried using IFNULL in front of the query values but got nowhere, any ideas??

the problem may lie in qrycondetailsextendedtotals

which of course i can't see

I researched it and says something to do with null values

Qrycondetailsextendedtotals is a query based on another query called qrycondetailsextended, here is the sql:

SELECT QryConDetailsExtended.Ship_ID AS Ship_ID, Sum(QryConDetailsExtended.[Extended Price]) AS [SumOfExtended Price], Sum(QryConDetailsExtended.Quantity) AS SumOfQuantity, Sum(QryConDetailsExtended.Discount) AS SumOfDiscount
FROM QryConDetailsExtended
GROUP BY QryConDetailsExtended.Ship_ID;


the qrycondetailsextended sql is:

SELECT tbl_consignment_details.*, CCur(Nz([Quantity],0)*Nz([Shipment_price],0)-Nz([Discount],0)) AS [Extended Price], tbl_Products.Shipping_Price
FROM tbl_Products INNER JOIN tbl_consignment_details ON tbl_Products.ProductID = tbl_consignment_details.Product_ID;

All based on the northwind sample dbase

About your book, the pdf version is as complete as the book version, right? probably a silly question but thought I'd clarify it, I am thinking of purchasing it as a reference aid, it would be nice to have the book but I've just purchased a tablet pc so I might start using that put lots of ebooks in there since I am on the road a lot.

You certainly taught me a great deal in a very short amount of time so I think it would be a great investment

Got it, thank you so much for your help, please let me know about this book as I am really eager to start reading it!!!

You are a star!!!!

Here's a tip.

Don't worry about how the Sql statement looks, one single string will do. What I do is put the Query into Access first and all the ASP is reads the query and applies the WHERE part of the SQL statement.

Doing this way you can get the SQL statement working in Access and later back it into Access if it ever goes wrong.

I used to do it your way but now that I using ASP.Net, the SQL statement is one continuous statement. Now its a doddle to test the SQL statement.

Simon

yes it is, you get everything except the paper

I like to see it, I am not new to SQL but I am by far an expert and often make a whole heap of mistakes, I guess I'll migrate to .net too since classic is ageing, thanks for the tip, eventually I'll get it and use the single string format.

figured it was but thought i'd check, kind of like the kindle that amazon have brought out, pretty cool stuff. Thanks again