Show only records where Straws is greater than Shipped? 1

[Acipenser]

I have a query which has 2 fields (among others). The fields are NumStraws and Shipped. The Numstraws field simply displays the number of straws entered for that SampleID. The Shipped field counts the number of shipped straws for that SampleID. Shipped is a check box. It works right now, Numstraws shows the correct number and Shipped shows the correct number. What I want the query to do is only display those SampleID records where Numstraws is greater than the count of Shipped.

In the Total category for Shipped I have count selected. I thought using a Citeria in the Numstraws field, such as >[CountofShipped], would work. CountofShipped is listed as a variable when you try to build a criteria for NumStraws. When I did this, the query now asks me for the value of CountofShipped instead of calculating it from the data.

What am I doing wrong?

 

[BNPMike]

Most likely you have mistyped it because it should work just fine. mike.stephens@bnpparibas.com

 

[CosmoKramer]

I don't think you can refer to an alias. Try using the same count statement that you used to create the alias as the criteria instead of >[CountofShipped]......

 

[Acipenser]

CosmoKramer - It appears you were right. I could not use the alias it self, typing in the same expression worked just fine though. Thanks.

BNPMike - I did not type it, but selected the expression within the build window. I think it is as Cosmo pointed out that I cant use the alias.

Thanks to both of you for your help. Much appreciated.