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!
9 1771
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!!!
assuming id2 is a parameter try this
...WHERE users.name = id2)
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.
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"
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.
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?
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 1,892
Recognized Expert Top Contributor
Just a thought, have you triple checked your data types on both ends?
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Philip Yale |
last post by:
A colleague of mine has a query which fails to run under SQLAgent
batch with the following error:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime...
|
by: Mattyboy |
last post by:
Guys
I have built a database with saved queries that runs fine in Access
but when I call it from the web using ASP, an exception occurs. I
have tried multiple ways of testing the databases with...
|
by: Dave Hopper |
last post by:
Hi
I posted a question recently regarding problems I am having getting a
value from a list box to use in a query. I got a lot of help, for
which I thank you and it's nearly working! But I need...
| |
by: Liz Malcolm |
last post by:
Hello and Thanks in advance for any help. I am using Access 2000.
I have a data entry form that opens a main form (using the On Click
event of the combo box ) with tab controls and 1 subform
on...
|
by: Ron |
last post by:
Hi,
Does a make table query have limitations that a select query doesn't have?
This is my problem. I've got a make table query I'll call qrymktblTemp--it
combines a few different tables and...
|
by: elitecodex |
last post by:
Hey everyone. I have this query
select * from `TableName` where `SomeIDField` 0
I can open a mysql command prompt and execute this command with no
issues. However, Im trying to issue the...
|
by: Cindy |
last post by:
I have an Access 2003 database with a query that works fine on my PC
but does not work on another PC. My PC has older versions of Access
installed as well as 2003 - the other PC only has Access...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| | |