Simple query gone wrong

[RazorEddie]

I'm trying to run a query against a DB I can't see, back end for a program. I have access to a access sample db that should be identical in structure.
SELECT [TDS Loans].Account AS LoanRecID
FROM [TDS Loans] INNER JOIN [TDS Charges] ON [TDS Loans].RecID = [TDS Charges].ParentRecID
WHERE [TDS Charges].Description="NSF";
Works in Access, but against the SQL Version gives me the following:

"The Query Failed to Execute Properly,
Unable to populate Table,
Unable to open Data Table,
Invalid column Name 'NSF'

I have had version that execute properly but return 0 rows which is not accurate.
This for example:
SELECT [TDS Loans].Account AS LoanRecID
FROM [TDS Loans] INNER JOIN [TDS Charges] ON [TDS Loans].RecID = [TDS Charges].ParentRecID
WHERE [TDS Charges].OwedToBal=$35.00

I know there is at least one record with that value in both DB's, in Access it returns the one row but not SQL.

Can any one point me in the right direction?

 

[dhookom]

Text values are delimited with single quotes in SQL Server:

WHERE [TDS Charges].Description='NSF'

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Also, try:

WHERE [TDS Charges].OwedToBal = 35.00

No $ sign

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[RazorEddie]

Got rid of the errors but still returning 0 rows.

SELECT [TDS Loans].Account AS LoanRecID
FROM [TDS Loans] INNER JOIN [TDS Charges] ON [TDS Loans].RecID = [TDS Charges].ParentRecID
WHERE [TDS Charges].Description='ASSIGNMENT FEE'

It's supposed to return a list of Accounts AS LoanRecID to filter the main list. Very Puzzled as I know that field in that table has that exact string on one record, we entered it on purpose for testing.

 

[Andrzejek]

What do you get when you do:[tt]
Select Description, ParentRecID
From [TDS Charges]
Where Description = 'ASSIGNMENT FEE'[/tt]

And then, take the value(s) from ParentRecID column above and try:[tt]
Select *
From [TDS Loans]
Where RecID = [blue]One of the value from first SQL [/blue][/tt]

See what you get.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[RazorEddie]

Unfortunately, the field I have to use is only to filter the record set. All I get when testing is whether the query executed and how many rows were returned. It doesn't show me any actual results. I have no direct access to the server for testing. I guess I'll go to the Server Admin and ask if I can get access on a par with the user setup in the program. Then I can use a management app to test with. Thanks for the suggestions. I let you know If I can get access and get some results.

 

[dhookom]

Remove the WHERE clause and test.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[RazorEddie]

Still getting 0 records. I put in a request for direct access. Considering he once gave me the Administrator Password, I think he'll be happy to set me up a user account. I promptly forgot that password.

 

[dhookom]

Did you try SELECT * on a single table with no criteria?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Somebody wants you to work blindfolded, with your hands tied behind your back, and saying: “Just make it happen!”

You may ask to have access to either development or test database, not necessarily production database. Most of the time all 3 environments should be the same.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[RazorEddie]

If I was still in IT running that department we would have 3 environments. I'll be getting access the production. I only need access to pull data, no insert or update or delete for this boy. I know better. Thanks for the help. He already told me he would get me access but it's not going to happen today, Halloween party today.