Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-27-2015, 01:08 PM   #1
oakey66
Newly Registered User
 
Join Date: Oct 2015
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
oakey66 is on a distinguished road
Query off of a SQL Server

I'm pulling data off of a SQL server through an ODBC connection. My query is pulling in various transactional data for a hospital. All of the data comes out successfully in the query. I was also able to apply a filter by date. The issue I run into is trying to apply a filter based on a specific code (It is a number however, the field is in a short text format). Is there a particular way that I need to apply a filter for a short text field? I'm a complete novice so please forgive me if this is a stupid question.

oakey66 is offline   Reply With Quote
Old 10-27-2015, 01:33 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query off of a SQL Server

What is the issue (error, etc)? What have you tried? I would expect to be able to put a form reference or "123" in the criteria in design view. If the field has a numeric data type, no quotes.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-27-2015, 02:12 PM   #3
oakey66
Newly Registered User
 
Join Date: Oct 2015
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
oakey66 is on a distinguished road
Re: Query off of a SQL Server

Quote:
Originally Posted by pbaldy View Post
What is the issue (error, etc)? What have you tried? I would expect to be able to put a form reference or "123" in the criteria in design view. If the field has a numeric data type, no quotes.
I'm not getting any errors. It pulls in all of the data. When I apply a filter in the design view it pulls in all of the data as if I have not applied a filter. The field is actually listed as a Short Text field but the content is numeric. I spoke with our DBA and he's thinking that this is something related to the way that Access interacts with SQL. We reviewed the SQL code in the SQL view and it looks correct as well. We're not really sure why the data is not filtering correctly.

oakey66 is offline   Reply With Quote
Old 10-27-2015, 02:19 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query off of a SQL Server

I think the DBA is all wet, but I'll reserve judgement until we solve it. What is the SQL of your query?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-28-2015, 07:17 AM   #5
oakey66
Newly Registered User
 
Join Date: Oct 2015
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
oakey66 is on a distinguished road
Re: Query off of a SQL Server

Quote:
Originally Posted by pbaldy View Post
I think the DBA is all wet, but I'll reserve judgement until we solve it. What is the SQL of your query?
He was able to run the query against the SQL database directly using the virtually the same SQL code I had in Access and did so successfully.

When I used the Make Table button using my existing query it worked with no problems. It's only happening when I run the query directly. Here's the code:

SELECT ENCOUNTER.MRNTreatHospitalAbbreviation,
FINANCIALTRANSACTION.ServiceDate,
FINANCIALTRANSACTION.PostingDateFormat,
FINANCIALTRANSACTION.ActivityCode,
FINANCIALTRANSACTION.ActivityCodeDescription,
FINANCIALTRANSACTION.CPTCode,
FINANCIALTRANSACTION.TransBillUnits,
FINANCIALTRANSACTION.Units,
FINANCIALTRANSACTION.DetailTotalAmount,
FINANCIALTRANSACTION.AccountingPeriod,
FINANCIALTRANSACTION.AccountingYear, [Formulary List with NDCs and CDMs].[NDC Code], [Products NDC to GCN].GCN, FINANCIALTRANSACTION.RevenueCenterCode
FROM (ENCOUNTER RIGHT JOIN FINANCIALTRANSACTION ON ENCOUNTER.AccountandFacility = FINANCIALTRANSACTION.AccountandFacility) LEFT JOIN ([Formulary List with NDCs and CDMs] LEFT JOIN [Products NDC to GCN] ON [Formulary List with NDCs and CDMs].[NDC Code Text] = [Products NDC to GCN].NDC) ON FINANCIALTRANSACTION.ActivityCode = [Formulary List with NDCs and CDMs].[Charge Code]
WHERE (((FINANCIALTRANSACTION.PostingDateFormat)>"07/01/2015") AND ((FINANCIALTRANSACTION.RevenueCenterCode)="30200") );
oakey66 is offline   Reply With Quote
Old 10-28-2015, 08:13 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query off of a SQL Server

So you're saying codes other than 30200 are returned? I use Access queries against linked SQL Server tables all the time, and I don't think I've ever had that problem.

Is PostingDateFormat a text field or date/time? If date/time, the date should be surrounded by #, not ".
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-28-2015, 08:32 AM   #7
oakey66
Newly Registered User
 
Join Date: Oct 2015
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
oakey66 is on a distinguished road
Re: Query off of a SQL Server

Quote:
Originally Posted by pbaldy View Post
So you're saying codes other than 30200 are returned? I use Access queries against linked SQL Server tables all the time, and I don't think I've ever had that problem.

Is PostingDateFormat a text field or date/time? If date/time, the date should be surrounded by #, not ".
Correct. I get all of the available codes and their relevant data in the query. Only when I Make Table, do I actually get the filtered data. Posting date is a date field. I adjusted the date field and it is working. It's only the revenue code that isn't being filtered appropriately.

oakey66 is offline   Reply With Quote
Old 10-28-2015, 08:40 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query off of a SQL Server

Access and SQL Server work pretty well together, and like I said I don't think this has ever happened to me. I wonder if it's in the joins. Try this as a test:

SELECT *
FROM FINANCIALTRANSACTION
WHERE FINANCIALTRANSACTION.PostingDateFormat>#07/01/2015# AND FINANCIALTRANSACTION.RevenueCenterCode="30200"
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-28-2015, 09:37 AM   #9
oakey66
Newly Registered User
 
Join Date: Oct 2015
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
oakey66 is on a distinguished road
Re: Query off of a SQL Server

Quote:
Originally Posted by pbaldy View Post
Access and SQL Server work pretty well together, and like I said I don't think this has ever happened to me. I wonder if it's in the joins. Try this as a test:

SELECT *
FROM FINANCIALTRANSACTION
WHERE FINANCIALTRANSACTION.PostingDateFormat>#07/01/2015# AND FINANCIALTRANSACTION.RevenueCenterCode="30200"
Still got all of the rev codes.
oakey66 is offline   Reply With Quote
Old 10-28-2015, 09:41 AM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query off of a SQL Server

Okay, this is a puzzler. What is the T-SQL that works for the DBA? I realize you can't post the db since the back end is SQL Server, but how about a screen shot of the results you're seeing?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-28-2015, 09:52 AM   #11
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,435
Thanks: 165
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Query off of a SQL Server

I wonder if it's how the tables where linked in - sometimes when linking SQL tables it asks you to select the Unique ID / record field - if you don't I've seen some strange results...?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-28-2015, 09:55 AM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query off of a SQL Server

It will do that if there's no primary key defined in SQL Server. The only result I've seen from not selecting one is a read-only table, but who knows?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-28-2015, 01:36 PM   #13
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 99
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Query off of a SQL Server

Quote:
Originally Posted by pbaldy View Post
The only result I've seen from not selecting one is a read-only table, but who knows?
I have seen other problems where a non-unique field is inappropriately selected as a key during the import. The data in all fields of a record with a particular value in the field selected as a key can be displayed for other records sharing that same "key", instead of what is actually in them.

I have also seen this lead to the appearance that the query criteria is not being applied. I think it is very likely that the problem is the inappropriate choice of the primary key during import.
Galaxiom is offline   Reply With Quote
Old 10-29-2015, 06:19 AM   #14
oakey66
Newly Registered User
 
Join Date: Oct 2015
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
oakey66 is on a distinguished road
Re: Query off of a SQL Server

Quote:
Originally Posted by pbaldy View Post
Okay, this is a puzzler. What is the T-SQL that works for the DBA? I realize you can't post the db since the back end is SQL Server, but how about a screen shot of the results you're seeing?
Unfortunately I'm not able to share any of the data. It's PHI so I'm limited in what I can provide. We're fairly puzzled on our end as well. And again, the stranger part is that running a direct SQL query yields the correct results.
oakey66 is offline   Reply With Quote
Old 10-29-2015, 06:32 AM   #15
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,435
Thanks: 165
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Query off of a SQL Server

You already posted the SQL of the non working code - surely you can post the SQL that worked on the Server? We don't need or want to see the data just the query that worked.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Tags
filter , odbc , query , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Link to SQL Server with ODBC without risking original data in SQL Server? David Ball General 1 08-21-2015 07:59 PM
SQL Server Linked Server to Oracle slow --> OpenQuery Solution Rx_ SQL Server 2 05-19-2015 08:25 AM
Query and Sql Server FoFa Queries 9 03-01-2010 10:36 AM
SQL Server Query: Updating a column based on an aggregate query Banana Queries 3 03-16-2009 02:04 AM
Translating SQL server query to Access SQL query SimonSezz Queries 3 02-17-2009 08:36 AM




All times are GMT -8. The time now is 05:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World