Queries based on Calculated Field criteria worked in Imported SQL DB but not Linked SQL DB

[davidckchiu]

Dear friends,

I am new to Access.

I am working to select records based on criteria using a calculated field. The criteria is the ratio of 2 fields is smaller than certain number.

When I tried it using the imported SQL DB, it works.
But when I use linked SQL DB, same DB and same calculated field and same criteria, the queries return records not based on criteria in the calculated field.

Can't figure out why ? Any hintes ?

Thanks in advance.

DC

 

[Joe4]

Welcome to the Board!

Please post the SQL code of your query, and maybe some sample data so we can see what it all looks like.

 

[davidckchiu]

Thanks.

Here is the SQL in the Aceess using imported DB file

SELECT dbo_DRUGMASTERTABLE.D_NAME, dbo_DRUGMASTERTABLE.COST, dbo_DRUGMASTERTABLE.PRICE, [PRICE]/[COST] AS Ratio
FROM dbo_DRUGMASTERTABLE
WHERE (((dbo_DRUGMASTERTABLE.COST)<>0) AND (([PRICE]/[COST])<1.3));



Here is the sample date

D_NAME	COST	PRICE	Ratio
*DIFFLAM-C SOLUTION(500ML)	101061	130000	1.29
,CO-PHENYLCAINE SPRAY	10800	12600	1.17
,NISITA NASAL OINT (20G)	36429	45000	1.24
,NISITA NASAL OINT (20G)	36429	45000	1.24
,RHINOCORT TH 100MCG/200DOSES	272319	350000	1.29
.BACTROBAN 2% NASAL OINT	167272	210000	1.26
.BACTROBAN 2% NASAL OINT	170909	210000	1.23
.CETAPHIL CLEANSER	54000	70000	1.30
.DEMOVATE 0.05% CR. (25G)	79451	100000	1.26

<tbody>
</tbody>

Cannot have the SQL in the Linked SQL Access file, as I am not at office now.

Thanksagain

 

[Joe4]

Please confirm the Data Type of the Cost and Price fields in linked database. If one is string/text, that could be problematic.