Using ASPRunner Pro, I am having an issue with using "union" in my custom SQL found in step 5 of 12.
The SQL works as long as I do not check anything found in step 7 of 12 (select key fields screen).
The downside to having nothing selected in step 7 is that it results in a screen that only lets me see the results of my sql statement and not ADD, EDIT records.
Here is my sql statement:
select [Orders.OOrderID], [Orders.OUser], [Orders.OProdID], ((OUserTpIRentPrc)-ODeposit) as OAmtDue
From [Orders], [Items]
Where Orders.OProdID=Items.IProdID
and (ORetDt-OLoanDt) = 0
union
select [Orders.OOrderID], [Orders.OUser], [Orders.OProdID], ((ORetDt-OLoanDt)(OUserTp*IRentPrc)-ODeposit) as OAmtDue
From [Orders], [Items]
Where Orders.OProdID=Items.IProdID
and (ORetDt-OLoanDt) > 0
The statement is supposed to tell me how much someone owes depending on how long they borrow the equipment. The union was needed because the amount Due ended as a zero if if they borrowed and returned the item on the same day.
Also written like this:
IF days borrowed > 0 then
(((ReturnDate-BorrowDate)PricePerDay)-Deposit)
ELSE
(1PricePerDay)-Deposit
Here is is the error if I DO select a primary key in step 7.
Error number -2147467259
Error description [Microsoft][ODBC Microsoft Access Driver] The number of columns in the two selected tables or queries of a union query do not match.
URL /rentals/admin/Orders_list.asp
SQL query select [OOrderID], [Orders.OOrderID], [Orders.OUser], [Orders.OProdID], ((OUserTpIRentPrc)-ODeposit) as OAmtDue From [Orders], [Items] Where Orders.OProdID=Items.IProdID and (ORetDt-OLoanDt) = 0 union select [Orders.OOrderID], [Orders.OUser], [Orders.OProdID], ((ORetDt-OLoanDt)(OUserTpIRentPrc)-ODeposit) as OAmtDue From [Orders], [Items] Where Orders.OProdID=Items.IProdID and (ORetDt-OLoanDt) > 0
I tried working around the error by using the following wrong sql statement:
select [Orders.OOrderID], [Orders.OUser], [Orders.OProdID], ((OUserTpIRentPrc)-ODeposit) as OAmtDue
From [Orders], [Items]
Where Orders.OProdID=Items.IProdID
and (ORetDt-OLoanDt) = 0
union
select [OOrderID], [Orders.OOrderID], [Orders.OUser], [Orders.OProdID], ((ORetDt-OLoanDt)(OUserTpIRentPrc)-ODeposit) as OAmtDue
From [Orders], [Items]
Where Orders.OProdID=Items.IProdID
and (ORetDt-OLoanDt) > 0
but it would not even let me progress to step 6 because there is an extra "[OOrderID]," in the statement on purpose.
_I also tried modifying the Ordersvariables.asp page manually, but I could not make that work either.
Any help appreciated. I'm open to ideas!
Chris Miller
cmiller@tiu.edu
