Syntax error (missing operator) in query expression

D

Deki

Hello,

I can't figure out why am I getting above message on this query:

SELECT
dbo_mainCustomer_Florida.customerID,qDRI2.tDRI2,qDRISF.tDRISF,qRRI.tRRI,dbo_mainCustomer_Florida.agencyName,dbo_mainCustomer_Florida.state
,dbo_mainCustomer_Florida.availCredits
FROM dbo_mainCustomer_Florida INNER Join
(SELECT customerID,COUNT(*) tDRI2 FROM dbo_mainCustomer_Florida MC JOIN
dbo_evaluators2_Florida EV ON MC.customerID = EV.Customer_ID JOIN
dri2_Offenders DRI2 ON
EV.password = DRI2.userpassword WHERE Cdate(DRI2.testDate) BETWEEN
#5/5/2005# AND #5/5/2006# GROUP BY customerID) qDRI2 ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF FROM dbo_mainCustomer_Florida MC JOIN
dbo_evaluators2_Florida EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.Password = DRISF.userpassword WHERE cdate(DRISF.testDate) BETWEEN
#5/5/2005# AND #5/5/2006# GROUP BY customerID) qDRISF ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI FROM dbo_mainCustomer_Florida MC JOIN
dbo_evaluators2_Florida EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.Password = RRI.userpassword WHERE Cdate(RRI.testDate) BETWEEN #5/5/2005#
AND #5/5/2006# GROUP BY customerID) qRRI ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID

Query works fine in Query Analyzer if I change linked table names to actuall
table names and CDate to CAST:

SELECT
mainCustomer.customerID,qDRI2.tDRI2,qDRISF.tDRISF,qRRI.tRRI,mainCustomer.agencyName,mainCustomer.state ,mainCustomer.availCredits
FROM mainCustomer INNER Join
(SELECT customerID,COUNT(*) tDRI2 FROM mainCustomer MC JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID JOIN dri2_Offenders DRI2 ON
EV.password = DRI2.userpassword WHERE Cast(DRI2.testDate AS datetime)
BETWEEN '5/5/2005' AND '5/5/2006' GROUP BY customerID) qDRI2 ON
mainCustomer.customerID = qDRI2.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF FROM mainCustomer MC JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID JOIN drisf_Offenders DRISF ON
EV.Password = DRISF.userpassword WHERE Cast(DRISF.testDate AS datetime)
BETWEEN '5/5/2005' AND '5/5/2006' GROUP BY customerID) qDRISF ON
mainCustomer.customerID = qDRISF.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI FROM mainCustomer MC JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID JOIN rri_Offenders RRI ON
EV.Password = RRI.userpassword WHERE Cast(RRI.testDate AS datetime) BETWEEN
'5/5/2005' AND '5/5/2006' GROUP BY customerID) qRRI ON
mainCustomer.customerID = qRRI.customerID

What am I missing?

Deki PA
 
D

Deki

Thanks Gary,
Both queries show me syntax error so I created passthrough query and it
works fine. Now my question is, is it possible to run stored procedure as
passthrough query with parametars? Basically, dates you see in my query are
supposed to be parametars and I have created stored procedure, but don't know
how to connect it to Access.
Here is stored procedure:

Create Procedure dbo.usp_qFlorida
@fromDate datetime
,@toDate datetime
As

SELECT
mainCustomer.customerID
,qDRI2.tDRI2
,qDRISF.tDRISF
,qRRI.tRRI
,mainCustomer.agencyName
,mainCustomer.state
,mainCustomer.availCredits
FROM
mainCustomer
INNER JOIN
(SELECT customerID,
COUNT(*) tDRI2
FROM mainCustomer MC
JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID
JOIN dri2_Offenders DRI2
ON EV.password = DRI2.userpassword
WHERE Cast(DRI2.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRI2
ON mainCustomer.customerID = qDRI2.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.password = DRISF.userpassword
WHERE Cast(DRISF.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRISF
ON mainCustomer.customerID = qDRISF.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.password = RRI.userpassword
WHERE Cast(RRI.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qRRI
ON mainCustomer.customerID = qRRI.customerID



--
Deki PA



Gary Walter said:
or maybe this:

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
((dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
Click to expand...
 
D

Deki

It was a little bit, but I got it. Thanks! I think I'm gonna redo all queries
into stored procedures. Thanks again!
--
Deki PA



Gary Walter said:
hope that wasn't confusing......
was going to paste in a reply,
but decided to type out instead
and forgot to delete first paragraph.
sorry..


Create a passthrough query that executes
the stored procedure with any parameter dates
(say "qryExecSP")

Execute usp_qFlorida '2006-01-01', '2006-02-01'

then, change SQL of this query in code
to insert new parameters (say from form textboxes)

(example using DAO)

Dim strSQL As String

strSQL = "Execute usp_qFlorida '" & Format(Me.txtFromDate,"yyyy-mm-dd") _
& "', '" & Format(Me.txtToDate, "yyyy-mm-dd") & "'"
CurrentDb.QueryDefs("qryExecSP").SQL = strSQL
Deki said:
Both queries show me syntax error so I created passthrough query and it
works fine. Now my question is, is it possible to run stored procedure as
passthrough query with parametars? Basically, dates you see in my query
are
supposed to be parametars and I have created stored procedure, but don't
know
how to connect it to Access.
Here is stored procedure:

Create Procedure dbo.usp_qFlorida
@fromDate datetime
,@toDate datetime
As

SELECT
mainCustomer.customerID
,qDRI2.tDRI2
,qDRISF.tDRISF
,qRRI.tRRI
,mainCustomer.agencyName
,mainCustomer.state
,mainCustomer.availCredits
FROM
mainCustomer
INNER JOIN
(SELECT customerID,
COUNT(*) tDRI2
FROM mainCustomer MC
JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID
JOIN dri2_Offenders DRI2
ON EV.password = DRI2.userpassword
WHERE Cast(DRI2.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRI2
ON mainCustomer.customerID = qDRI2.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.password = DRISF.userpassword
WHERE Cast(DRISF.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRISF
ON mainCustomer.customerID = qDRISF.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.password = RRI.userpassword
WHERE Cast(RRI.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qRRI
ON mainCustomer.customerID = qRRI.customerID



--
Deki PA



:

or maybe this:

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
((dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
Click to expand...
Click to expand...
Click to expand...
 
Top