XLODBC #N/A Sum Queries do not connect
 

I have set up a number of queries, with start and end date references t
cells on the spreadsheet. Only half of the queries work. The other hal
(which all include SUM) return #N/A to the cell. Below are one of each
working and not working. Any advise greatly appreciated...

Works fine

=SQL.REQUEST("DSN=dtabase;SERVER=PRO002;UID=test1; PWD=test2",,,"SELEC
Count(Distinct b.ClientID) FROM tblbets b left join tblEvents e o
e.eventid = b.eventid WHERE (e.settleddate='"&B1&"' An
e.settleddate<'"&B2&"') AND (b.Internet=0)")

Doesn't work

=SQL.REQUEST("DSN=dtabase;SERVER=PRO002;UID=test1; PWD=test2",,,"selec
sum(amounttowin)+ sum(amounttoplace)-sum(payoutwin)- sum(payoutplace
from v_allbets b inner join v_allevents e on b.eventid = e.eventi
where internet=0 and (settleddate='"&B1&"' and settleddate <'"&B2&"'
and (bettype 0 and bettype <100) and valid = 1"

--
fiona
-----------------------------------------------------------------------
fionac's Profile: http://www.excelforum.com/member.php...fo&userid=1585
View this thread: http://www.excelforum.com/showthread.php?threadid=27348

XLODBC #N/A Sum Queries do not connect
 

fionac wrote ...

I have set up a number of queries, with start and end date references to
cells on the spreadsheet. Only half of the queries work. The other half
(which all include SUM) return #N/A to the cell. Below are one of each,
working and not working. Any advise greatly appreciated...

Works fine

=SQL.REQUEST("DSN=dtabase;SERVER=PRO002;UID=test1; PWD=test2",,,"SELECT
Count(Distinct b.ClientID) FROM tblbets b left join tblEvents e on
e.eventid = b.eventid WHERE (e.settleddate='"&B1&"' And
e.settleddate<'"&B2&"') AND (b.Internet=0)")

Doesn't work

=SQL.REQUEST("DSN=dtabase;SERVER=PRO002;UID=test1; PWD=test2",,,"select
sum(amounttowin)+ sum(amounttoplace)-sum(payoutwin)- sum(payoutplace)
from v_allbets b inner join v_allevents e on b.eventid = e.eventid
where internet=0 and (settleddate='"&B1&"' and settleddate <'"&B2&"')
and (bettype 0 and bettype <100) and valid = 1")

In your second query, the column names are not always prefixed with
the table alias e.g. internet=0 rather than b.internet=0, which may be
causing errors.

Jamie.

--