Query Problem - SQL connected tables

santosm
3StarLounger
Posts: 253
Joined: 19 Apr 2010, 09:01
Location: Indiana, USA

Query Problem - SQL connected tables

Post by santosm »

Hi All,
I have this query which worked great in Access, but now that my data is in MS SQL, it is giving me an error when I try to run it. As a note, both items in this query are queries. I can open each query without issue however, when I try to open the statement below, I get the error.

Code: Select all

       Forms!fQueueContractWarranty!Child1.Form.RecordSource = _
                "SELECT [qContract_PM_Warranty_Noincidents].* " _
                & "FROM [qContract_PM_Warranty_Noincidents] LEFT JOIN " _
                & "[qContract_EXT_Warranty_Incidents] ON [qContract_PM_Warranty_Noincidents].[ID]=[qContract_EXT_Warranty_Incidents].[ID]" _
                & "WHERE [qContract_EXT_Warranty_Incidents].[ID] Is Null and ([qContract_PM_Warranty_Noincidents]![equip]![warranty_exp_date]) >= "_
                & Me.cbo1.Value & " and ([qContract_PM_Warranty_Noincidents]![equip]![warranty_exp_date]) <= " & Me.cbo2.Value & " order by ([qContract_PM_Warranty_Noincidents]![Name_24])"
Binding error.jpg
I attached a pic of the error. I don't know what the meaning of the "MS2" is in the error dialog. I read something on some other pages about how you can't call table names within a subquery but I am not sure how to fix that here, if in fact that is the issue.

Thanks,
Mark
You do not have the required permissions to view the files attached to this post.
Thanks,
Mark

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query Problem - SQL connected tables

Post by HansV »

This is outside my field - I hope that Wendell will drop by and have a suggestion...
Best wishes,
Hans

santosm
3StarLounger
Posts: 253
Joined: 19 Apr 2010, 09:01
Location: Indiana, USA

Re: Query Problem - SQL connected tables

Post by santosm »

Thanks Hans,
I am sure it is something easy for the MS SQL experienced!

Thanks,
Mark
Thanks,
Mark

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Query Problem - SQL connected tables

Post by Wendell »

Unfortunately I have only limited resources available at the moment, so I can't do any testing, but joining queries can be problematic. I would take each of the queries to SQL Server views, link to those, and then do the above query using the views. Linked views behave like tables in Access.
Wendell
You can't see the view if you don't climb the mountain!

santosm
3StarLounger
Posts: 253
Joined: 19 Apr 2010, 09:01
Location: Indiana, USA

Re: Query Problem - SQL connected tables

Post by santosm »

Hi Wendell and Hans,
The suggestion of moving the queries over to the SQL side as Views works great. Once I did that, I created another query that does the left join and it runs fast without errors. Thanks for the pointers!

Mark
Thanks,
Mark

santosm
3StarLounger
Posts: 253
Joined: 19 Apr 2010, 09:01
Location: Indiana, USA

Re: Query Problem - SQL connected tables

Post by santosm »

Hi All,
I have another query that doesn't want to run with SQL tables. It did work fine in Access with Access tables.

SELECT qContracts_EQ_N1.[Equip ID], qContracts_EQ_N1.PL, qContracts_EQ_N1.Model, qContracts_EQ_N1.SN, qContracts_EQ_N1.active
FROM qContracts_EQ_N1 LEFT JOIN qContracts_EQ_N2 ON qContracts_EQ_N1.[Equip ID] = qContracts_EQ_N2.equip_id
WHERE (((qContracts_EQ_N2.equip_id) Is Null))
ORDER BY qContracts_EQ_N1.[Equip ID];

Both of the tables, qContracts_EQ_N1 and qContract_EQ_N2 are SQL views. Both of the views display the correct data but it doesn't want to run with the last ID as null. Basically, I want everything from the N1 table that is not already present in the N2 table. Now I am thinking that I will have to create tables locally in the Access front end and delete them after I am done with the process.

Thanks,
Mark
Thanks,
Mark

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Query Problem - SQL connected tables

Post by Wendell »

Does the query run if you remove the WHERE clause? I suspect there is another issue with the query - do you get an error message, or does it simply not return any rows?
Wendell
You can't see the view if you don't climb the mountain!

santosm
3StarLounger
Posts: 253
Joined: 19 Apr 2010, 09:01
Location: Indiana, USA

Re: Query Problem - SQL connected tables

Post by santosm »

Hi Wendell,
I was finally able to get this working. The two views would run correctly but for some reason it didn't like the "IS Null" for the ID on the one view. This is what ended up working (and very nicely I might add)

Code: Select all

SELECT qContracts_EQ_N1.[Equip ID], qContracts_EQ_N1.PL, qContracts_EQ_N1.Model, qContracts_EQ_N1.SN, qContracts_EQ_N1.Ship_ID, qContracts_EQ_N1.active, *
FROM qContracts_EQ_N1
WHERE (((qContracts_EQ_N1.[Equip ID]) Not In (select [equip_id] from qContracts_EQ_N2)))
ORDER BY qContracts_EQ_N1.[Equip ID];
2014-04-16_15-01-45.jpg
Thanks,
Mark
You do not have the required permissions to view the files attached to this post.
Thanks,
Mark