Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-29-2015, 06:32 AM   #16
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 Minty View Post
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...?
So I removed all links and just tried to search against the transactional data alone. I was still getting the same unfiltered revenue codes.

oakey66 is offline   Reply With Quote
Old 10-29-2015, 06:34 AM   #17
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 Minty View Post
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.
Oh sorry. I misunderstood. I thought you were asking for the results. I'd have to ask the DBA to provide it to me. I don't have SQL. Just access to the data warehouse.
oakey66 is offline   Reply With Quote
Old 11-03-2015, 07:33 AM   #18
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?
Sorry for the delay folks. Here's the SQL Query:

Code:
SELECT        ENCOUNTER.MRNTreatHospitalAbbreviation, FINANCIALTRANSACTION.ServiceDate, FINANCIALTRANSACTION.PostingDateFormat, 
FINANCIALTRANSACTION.ActivityCode, FINANCIALTRANSACTION.ActivityCodeDescription, 
FINANCIALTRANSACTION.CPTCode, 
FINANCIALTRANSACTION.TransBillUnits,
FINANCIALTRANSACTION.Units, 
FINANCIALTRANSACTION.DetailTotalAmount, 
ENCOUNTER.PrimaryCarrierNameFreeText, FINANCIALTRANSACTION.RevenueCenterCode
FROM            ENCOUNTER INNER JOIN
                   FINANCIALTRANSACTION ON ENCOUNTER.AccountandFacility = FINANCIALTRANSACTION.AccountandFacility
WHERE        (FINANCIALTRANSACTION.PostingDateFormat >= '07/01/2015') AND (FINANCIALTRANSACTION.RevenueCenterCode = '30200')

oakey66 is offline   Reply With Quote
Old 11-03-2015, 07:50 AM   #19
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,737 Times in 1,706 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

That would indicate that the Revenue centre number is actually text not a number.
__________________
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 11-03-2015, 08:27 AM   #20
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,268
Thanks: 13
Thanked 4,110 Times in 4,042 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
Re: Query off of a SQL Server

Minty, that was stated at the outset.

Oakey, this is really bugging me. I note the join in the T-SQL is different, but you said even a straight query against the FINANCIALTRANSACTION table alone wasn't working, right? I've got a SQL Server table with a similar field, ie a text field that holds numbers. I query against it all the time using that field in the criteria; never had this problem. Can you confirm the data type on the Access side? Maybe it didn't translate correctly. Or try treating like a numeric field:

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 offline   Reply With Quote
Old 11-03-2015, 10:00 AM   #21
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
Minty, that was stated at the outset.

Oakey, this is really bugging me. I note the join in the T-SQL is different, but you said even a straight query against the FINANCIALTRANSACTION table alone wasn't working, right? I've got a SQL Server table with a similar field, ie a text field that holds numbers. I query against it all the time using that field in the criteria; never had this problem. Can you confirm the data type on the Access side? Maybe it didn't translate correctly. Or try treating like a numeric field:

SELECT *
FROM FINANCIALTRANSACTION
WHERE FINANCIALTRANSACTION.PostingDateFormat>#07/01/2015# AND FINANCIALTRANSACTION.RevenueCenterCode=30200
You're correct. I've tried various joins to no avail. On the Access side, the field is listed as a short text field. In the actual SQL database, it's listed as a varchar(50),not null) format. The data types are the same as I can tell.
oakey66 is offline   Reply With Quote
Old 11-03-2015, 10:08 AM   #22
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Query off of a SQL Server

Extract the revenue center code from SQL and check the contents character by character. Sometimes weird stuff finds its way in, non-printable characters, trailing blanks ...

Also provable by using Like '%30200%' presumably

__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

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


Last edited by spikepl; 11-03-2015 at 10:14 AM.
spikepl is offline   Reply With Quote
Old 11-03-2015, 10:24 AM   #23
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,268
Thanks: 13
Thanked 4,110 Times in 4,042 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
Re: Query off of a SQL Server

I'm stumped. I don't think spike's suggestion will help, as you have the opposite problem, but can't hurt to try. I wish it was a db I could play with, because it's really bugging me.
__________________
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 offline   Reply With Quote
Old 11-03-2015, 10:34 AM   #24
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Query off of a SQL Server

Perhaps that particular code in the data is not 30200 but 3O2OO or something like that. Check all characters.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

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

spikepl is offline   Reply With Quote
Old 11-03-2015, 10:39 AM   #25
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,268
Thanks: 13
Thanked 4,110 Times in 4,042 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
Re: Query off of a SQL Server

The problem is (unless I'm completely misunderstanding) that not only 30200 is returned, but 30201, 30202, etc as well. The criteria is being ignored.
__________________
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.

Last edited by pbaldy; 11-03-2015 at 11:03 AM. Reason: fix goof
pbaldy is offline   Reply With Quote
Old 11-03-2015, 10:41 AM   #26
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Query off of a SQL Server

OH. I thought only 30200 did not return but the others did. Again, check contents of the field in SQL and the contents of the criteria character by character, would be my advice.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

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

spikepl is offline   Reply With Quote
Old 11-03-2015, 10:54 AM   #27
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
The problem is (unless I'm completely misunderstanding) is that not only 30200 is returned, but 30201, 30202, etc as well. The criteria is being ignored.
It's exactly this. The criteria is being ignored altogether.
oakey66 is offline   Reply With Quote
Old 11-03-2015, 12:17 PM   #28
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 spikepl View Post
OH. I thought only 30200 did not return but the others did. Again, check contents of the field in SQL and the contents of the criteria character by character, would be my advice.
But why would the query work in SQL but not in Access? I guess it's something related to how Access reads the database.
oakey66 is offline   Reply With Quote
Old 11-03-2015, 12:23 PM   #29
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Query off of a SQL Server

It is not a question of "why" right now but "what". What gets across from Access to SQL correctly and what doesn't. You have plenty of stuff to try - strings, or perhaps strings after a date, or perhaps ... try simple queries and simple criteria to see what works and what doesn't. Once known, that would help to narrow-down the cause.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

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

spikepl is offline   Reply With Quote
Old 11-03-2015, 02:00 PM   #30
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,268
Thanks: 13
Thanked 4,110 Times in 4,042 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
Re: Query off of a SQL Server

I posted this to an MVP board. First question, have you tried just (no date criteria):

Where 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 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 03:34 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