Query off of a SQL Server (1 Viewer)

oakey66

Registered User
Joined
Oct 27, 2015
Messages
12
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.
 

spikepl

Eledittingent Beliped
Joined
Nov 3, 2010
Messages
6,144
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
 
Last edited:

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,663
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.
 

spikepl

Eledittingent Beliped
Joined
Nov 3, 2010
Messages
6,144
Perhaps that particular code in the data is not 30200 but 3O2OO or something like that. Check all characters.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,663
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.
 
Last edited:

spikepl

Eledittingent Beliped
Joined
Nov 3, 2010
Messages
6,144
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.
 

oakey66

Registered User
Joined
Oct 27, 2015
Messages
12
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

Registered User
Joined
Oct 27, 2015
Messages
12
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.
 

spikepl

Eledittingent Beliped
Joined
Nov 3, 2010
Messages
6,144
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.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,663
I posted this to an MVP board. First question, have you tried just (no date criteria):

Where FINANCIALTRANSACTION.RevenueCenterCode="30200"
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,833
Still looks to me like the errors that happen when an inappropriate field is chosen as a PK during the import to Access.

Is a PK defined on the SQL table?

Is the same field indicated as the PK on the Access linked table?

If there is no PK defined on the SQL table, Access will ask for the appropriate fields to be selected during the import. If an inappropriate selection is made then exactly this type of error can happen.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,663
Galaxiom has mentioned the PK issue twice and I don't see that it's been addressed. A poster on the MVP board agrees that it is a likely suspect, so please follow up on that.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom