Show only rows where fielda < fieldb

[Schon731]

Thats about it. I have 5 columns that I want to display but only if fielda is less than fieldb. The query is coming from a linked table where those table fields are numberic (double). Sounds easy but for a layman its not. Please help.

Have tried
Where [Fielda]<[fieldb] and things along those lines but still nothing.. Thanks

[jzwp11]

Can you provide the full SQL text of the query (switch to SQL view to get the text)?

[Schon731]

SELECT DG572B.[DG-Drug Code], DG572B.[DG-Drug Name], DG572B.[DL-Acq Unit Cost], DG572B.[DL-Welfare Unit Cost], [DG572B]![DL-Welfare Unit Cost]-[DG572B]![DL-Acq Unit Cost] AS Expr1
FROM DG572B
ORDER BY [DG572B]![DL-Welfare Unit Cost]-[DG572B]![DL-Acq Unit Cost];

This is what I have now just so I can work with it. What I want is to only show a row for where DG572B.[DL-Acq Unit Cost] < DG572B.[DL-Welfare Unit Cost]

I added the expr1 and sorted it so I can focus on the negative numbers.. Hope this helps..

[jzwp11]

Assuming that fielda and fieldb correspond to DG572B.[DL-Acq Unit Cost]and DG572B.[DL-Welfare Unit Cost] respectively, the WHERE clause you showed earlier should work, so perhaps I am not understanding what you want to achieve

SELECT DG572B.[DG-Drug Code], DG572B.[DG-Drug Name], DG572B.[DL-Acq  Unit Cost], DG572B.[DL-Welfare Unit Cost], [DG572B].[DL-Welfare Unit  Cost]-[DG572B].[DL-Acq Unit Cost] AS Expr1
FROM DG572B
  WHERE DG572B.[DL-Acq Unit Cost] < DG572B.[DL-Welfare Unit Cost]
ORDER BY [DG572B]![DL-Welfare Unit Cost]-[DG572B]![DL-Acq Unit Cost];

[weekend00]

I guess that Schon tried to put where clause after order clause, that's not the right syntax.

[Schon731]

Told u I was a layman at this. My problem was I was putting the word WHERE into the criteria so it was coming up as WHERE "where....etc.. I just removed that word and it worked. Looking at your SQL view helped, thanks alot..

[jzwp11]

You're welcome. I'm glad you got it worked out.