problem with query 1

[bikebanditcom]

working on this statement and am getting
<error>
Microsoft VBScript compilation error '800a03ee'

Expected ')'

/pbc/reporting/xlReports/TMPz49damvdu9.asp, line 8
</error>

the sql:
<code>
set compReport = pbcDB.execute(&quot;SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSales.billed) Is Null) AND ((tblSalesPackages.status)=&quot;Complete&quot))&quot
</code>

what is wrong here? i've counted all my parens and all are right, i think..

 

[meckeard]

I think you have 1 too many closing paren's. Or you are missing an opening one. Count them again.

Mark

 

[bikebanditcom]

mark, i've counted them all and they are all needed as far as i can tell, but i've also removed one set of them and i get the same problem damnit man it doesnt make any sense...

 

[bikebanditcom]

the problem was that i needed single quotes around &quot;completed&quot;, but now i've added a date range to the mix and am getting the same problem

set noBillSQL = pbcDB.execute(&quot;SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE ((tblSales.saleDate) Between #&quot;date1&quot;# And #&quot;date2&quot;#) AND ((tblSales.billed) Is Null) AND ((tblSalesPackages.status)='Complete');&quot

btw i've renamed the recordset since last posting, just to avoid any confusion.

any ideas why guys?thanks alot
dan

 

[meckeard]

bikebanditcom,

Sorry, I still think you are 1 paren too many. Try this:

set compReport = pbcDB.execute(&quot;SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status
FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum)
INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID
WHERE ((tblSales.billed Is Null) AND (tblSalesPackages.status=&quot;Complete&quot)&quot

Mark

 

[bikebanditcom]

mark,

that doesnt work, same error, however this code does

set noBillSQL = pbcDB.execute(&quot;SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE ((tblSales.billed Is Null) AND (tblSalesPackages.status='Complete'))&quot

but my problem is this:

same error msg,

set noBillSQL = pbcDB.execute(&quot;SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE ((tblSales.saleDate) Between #&quot;date1&quot;# And #&quot;date2&quot;#) AND ((tblSales.billed) Is Null) AND ((tblSalesPackages.status)='Complete');&quot


i've added a date range in there that gives the same error msg ???

 

[meckeard]

You need to break around the date var's. Notice the '&':

set noBillSQL = pbcDB.execute(&quot;SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE ((tblSales.saleDate) Between #&quot; & date1 & &quot;# And #&quot; & date2 & &quot;#) AND ((tblSales.billed) Is Null) AND ((tblSalesPackages.status)='Complete');&quot

 

[bikebanditcom]

thanks so much, that worked perfectly.

 

[meckeard]

glad I could help!

 

[bikebanditcom]

hey your great! thanks. if i could trouble you for one more problem that'd be great, or anyone else who may have an idea

error is:
Microsoft VBScript runtime error '800a01a8'

Object required: '[string: &quot;SELECT outerjoinPaid&quot;]'

/pbc/reporting/TMPawt1umvt0t.asp, line 9

sql is :

set noCheck = (&quot;SELECT outerjoinPaidNoBill.saleDate, outerjoinPaidNoBill.projectName, outerjoinPaidNoBill.lotNum, outerjoinPaidNoBill.VOO, tblPhases.phaseName, outerjoinPaidNoBill.billed FROM outerjoinPaidNoBill, tblPhases WHERE (((outerjoinPaidNoBill.checkNum) Is Null));&quot

any ideas? thanks

dan

 

[meckeard]

Did you forget this part?

pbcDB.execute

Mark.

 

[bikebanditcom]

wow, im an idiot sometime...thanks alot bro.

 

[meckeard]

no problem!