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
[TABLE="width: 377"]
<tbody>[TR]
[TD="class: xl66, width: 227"]D_NAME[/TD]
[TD="class: xl66, width: 92"]COST[/TD]
[TD="class: xl66, width: 92"]PRICE[/TD]
[TD="class: xl66, width: 92"]Ratio[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"]*DIFFLAM-C SOLUTION(500ML)[/TD]
[TD="class: xl68, width: 92"]101061[/TD]
[TD="class: xl68, width: 92"]130000[/TD]
[TD="class: xl69, width: 92"]1.29[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"],CO-PHENYLCAINE SPRAY[/TD]
[TD="class: xl68, width: 92"]10800[/TD]
[TD="class: xl68, width: 92"]12600[/TD]
[TD="class: xl69, width: 92"]1.17[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"],NISITA NASAL OINT (20G)[/TD]
[TD="class: xl68, width: 92"]36429[/TD]
[TD="class: xl68, width: 92"]45000[/TD]
[TD="class: xl69, width: 92"]1.24[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"],NISITA NASAL OINT (20G)[/TD]
[TD="class: xl68, width: 92"]36429[/TD]
[TD="class: xl68, width: 92"]45000[/TD]
[TD="class: xl69, width: 92"]1.24[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"],RHINOCORT TH 100MCG/200DOSES[/TD]
[TD="class: xl68, width: 92"]272319[/TD]
[TD="class: xl68, width: 92"]350000[/TD]
[TD="class: xl69, width: 92"]1.29[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"].BACTROBAN 2% NASAL OINT[/TD]
[TD="class: xl68, width: 92"]167272[/TD]
[TD="class: xl68, width: 92"]210000[/TD]
[TD="class: xl69, width: 92"]1.26[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"].BACTROBAN 2% NASAL OINT[/TD]
[TD="class: xl68, width: 92"]170909[/TD]
[TD="class: xl68, width: 92"]210000[/TD]
[TD="class: xl69, width: 92"]1.23[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"].CETAPHIL CLEANSER[/TD]
[TD="class: xl68, width: 92"]54000[/TD]
[TD="class: xl68, width: 92"]70000[/TD]
[TD="class: xl69, width: 92"]1.30[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 227"].DEMOVATE 0.05% CR. (25G)[/TD]
[TD="class: xl68, width: 92"]79451[/TD]
[TD="class: xl68, width: 92"]100000[/TD]
[TD="class: xl69, width: 92"]1.26
[/TD]
[/TR]
</tbody>[/TABLE]

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.