Issue with a Expr2 field.

[gusc999]

I'm having an issue with the query below, I need the "Expr2" field to only display result with >= 8 after the subtractin but the onyl thing that comes up is a -1 for when the number is >8. I would greatly appreciate any advice.


SELECT PIRF_ITEMMSC.IMBNBR, PIRF_ITEMMSC.IMBNAM, PIRF_PIRITEM.ITWHDP, PIRF_PIRITEM.ITITEM, PIRF_PIRITEM.ITDESC, PIRF_PIRITEM.ITPDSC, [ITRCVD]/100 AS Expr1, PIRF_PIRITEM.ITSWGT, [Expr1]-[ITSWGT] AS Expr2

FROM (PIRF_PIRITEM INNER JOIN PIRF_ITEMMSC ON PIRF_PIRITEM.ITITEM = PIRF_ITEMMSC.IMITEM) INNER JOIN PIRF_SLOT3 ON PIRF_PIRITEM.ITITEM = PIRF_SLOT3.SLITEM

WHERE (((PIRF_SLOT3.SLPICK)="Y"));

 

[PHV]

You wanted this ?
SELECT PIRF_ITEMMSC.IMBNBR, PIRF_ITEMMSC.IMBNAM, PIRF_PIRITEM.ITWHDP, PIRF_PIRITEM.ITITEM, PIRF_PIRITEM.ITDESC, PIRF_PIRITEM.ITPDSC, [ITRCVD]/100 AS Expr1, PIRF_PIRITEM.ITSWGT, [Expr1]-[ITSWGT] AS Expr2
FROM (PIRF_PIRITEM
INNER JOIN PIRF_ITEMMSC ON PIRF_PIRITEM.ITITEM = PIRF_ITEMMSC.IMITEM)
INNER JOIN PIRF_SLOT3 ON PIRF_PIRITEM.ITITEM = PIRF_SLOT3.SLITEM
WHERE PIRF_SLOT3.SLPICK="Y" AND ([ITRCVD]/100)-[ITSWGT]>=8

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

If I understand your question, try:

SELECT PIRF_ITEMMSC.IMBNBR, PIRF_ITEMMSC.IMBNAM, PIRF_PIRITEM.ITWHDP, 
PIRF_PIRITEM.ITITEM, PIRF_PIRITEM.ITDESC, PIRF_PIRITEM.ITPDSC, 
[ITRCVD]/100 AS DescriptiveName, PIRF_PIRITEM.ITSWGT, 
([ITRCVD]/100)-[ITSWGT] AS AnotherDescriptiveName

FROM (PIRF_PIRITEM INNER JOIN PIRF_ITEMMSC 
   ON PIRF_PIRITEM.ITITEM = PIRF_ITEMMSC.IMITEM) 
 INNER JOIN PIRF_SLOT3 
   ON PIRF_PIRITEM.ITITEM = PIRF_SLOT3.SLITEM

WHERE PIRF_SLOT3.SLPICK="Y"
AND ([ITRCVD]/100)-[ITSWGT]>=8;

I never used a derived alias in another expression. I also take the time to change "Expr.." to more descriptive names.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[r937]

[blue]SELECT *
  FROM ([/blue]
       SELECT PIRF_ITEMMSC.IMBNBR
            , PIRF_ITEMMSC.IMBNAM
            , PIRF_PIRITEM.ITWHDP
            , PIRF_PIRITEM.ITITEM
            , PIRF_PIRITEM.ITDESC
            , PIRF_PIRITEM.ITPDSC
            , [ITRCVD]/100 AS Expr1
            , PIRF_PIRITEM.ITSWGT
            , [Expr1]-[ITSWGT] AS Expr2
         FROM (
              PIRF_SLOT3 
       INNER 
         JOIN PIRF_PIRITEM 
           ON PIRF_PIRITEM.ITITEM = PIRF_SLOT3.SLITEM
              )
       INNER 
         JOIN PIRF_ITEMMSC
           ON PIRF_ITEMMSC.IMITEM = PIRF_PIRITEM.ITITEM
        WHERE PIRF_SLOT3.SLPICK = 'Y'
       [blue]) AS d
 WHERE Expr2 >= 8[/blue]

r937.com | rudy.ca