Query Works, but ASP gives me an error

[timber910]

Hello, I've been working on this for days and have been able to find anything to help me solve the problem. Here is my SQL query.

Select FISCALMONTH.id, FISCALMONTH.qtr _year, FISCALMONTH.fis _month,
Query1.[users.Name], Query1.username , Query1.scrub, Query1.qa, Query1.comp, Query1.acw, Query1.aht, Query1.aht_disp , Query1.events, Query1.do, Query1.abg, Query1.tas, Query1.other, Query1.css
From FISCALMONTH Left Join
(Select STATS06.*, users.username, users.id, users.Name
From STATS06 Inner Join users On STATS06.userid = users.id
Where users.username = 'KLewis') As Query1 On FISCALMONTH.id = Query1.fmid Where FISCALMONTH.qtr _year = '4th Qtr FY-06'

Only difference on my asp page the 'KLewis' and '4th Qtr FY-06' are variables, but i changed them to the defined to figure out why this is working. Here is the error I'm getting.

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'stats06 INNER JOIN users ON stats06.userid = users.id WHERE users.id = 3419'.

/kc cmc/ams/ams/docs/qtr_doc.asp, line 117


I tried placing [] around the subquery, when I did that I got a slightly different error.

Microsoft JET Database Engine error '80040e37'

The Microsoft Jet database engine cannot find the input table or query 'SELECT stats06 INNER JOIN users ON stats06.userid = users.id WHERE users.id = 3419'. Make sure it exists and that its name is spelled correctly.

/kc cmc/ams/ams/docs/qtr_doc.asp, line 117

The tables are there and are spelled correctly. The database is Access 2003, I can create the query in access and it works. I can also get the query to run in a SQL query builder and send it to access and it works. Any one have any ideas?? I'm so in need of some help!

[timber910]

Still needing some help. I got my query to work, however I am getting a type mismatch. Here is my SQL Query.

Select fiscalmonth.id, fiscalmonth.qtr _year, fiscalmonth.fis _month, S.[users.Name], S.username, S.scrub, S.qa, S.comp, S.acw, S.aht, S.aht_disp, S.events, S.do, S.abg, S.tas, S.other, S.css FROM fiscalmonth LEFT JOIN (SELECT stats06.*, users.username, users.id, users.Name FROM stats06 INNER JOIN users On stats06.userid = users.id WHERE users.name = '&id2&') AS S On fiscalmonth.id = S.fmid WHERE fiscalmonth.id = 10

But i can use this query:

fiscalmonth LEFT JOIN stats06 ON fiscalmonth.id = stats06.fmid WHERE userid = "&id2&" AND fmid = 10

(and not get the results I wanted) but no type mismatch on any of the items pulled from the query with I use the formatnumber. Is there a reason for this? Any help would be wonderful!!!

[scripto]

assuming id2 is a parameter try this

...WHERE users.name = id2)

[timber910]

assuming id2 is a parameter try this

...WHERE users.name = id2)

Its a variable that set as the parameter. Tried without the '& &' got more errors than before.

[willakawill]

Its a variable that set as the parameter. Tried without the '& &' got more errors than before.

Hi. It might work to ensure that there is white space around id2 so that it is not concatenated with the text before or after. That would cause the error you are getting.

"userid = " & id2 & " AND fmid = 10"

[timber910]

Hi. It might work to ensure that there is white space around id2 so that it is not concatenated with the text before or after. That would cause the error you are getting.

"userid = " & id2 & " AND fmid = 10"

with the " " I get a datatype error as the id2 is a number. I did try something very close to this suggestion and it it working. Still trying to figure out why both querys will run one with the formatnumber and works and the other one gets a datatype mismatch. Its sill very odd.

[willakawill]

with the " " I get a datatype error as the id2 is a number. I did try something very close to this suggestion and it it working. Still trying to figure out why both querys will run one with the formatnumber and works and the other one gets a datatype mismatch. Its sill very odd.

So what is it that worked?

[timber910]

Still doesn't work. I still get a type mismatch. Which is still odd, something to do with access I'm guessing but. I had to do two different querys instead of using the joined statement. Hopefully I can get my database moved to SQL Server2005 in the near future and I think some of the problem could be solved. Thanks for the help!

[AricC]

Just a thought, have you triple checked your data types on both ends?

[timber910]

Just a thought, have you triple checked your data types on both ends?

Data types in the database are text. Tried triming and not. One query pulls and can do the formatnumber without any issues and the other one doesn't.