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.