>0

  • Thread starter Thread starter Eb1mom
  • Start date Start date
E

Eb1mom

I am trying to get query to return records where PiecesLeft
field is greater than 0. When I put >0 in as criteria for
PiecesLeft I get a popup box asking for TotalofScheduledPieces?

The two query fields are

PiecesLeft:
Nz([NumberofPiecesReceived],0)-Nz([TotalofScheduledPieces],0)

TotalofScheduledPieces:
DSum("[NumberofPiecesScheduled]","Piecesscheduledtbl","[SLotTrackID]="
& [LotTrackID])

Both fields return correct values until I specify criteria
for PiecesLeft. Any suggestions would be appreciated. Thank-you
 
I am trying to get query to return records where PiecesLeft
field is greater than 0. When I put >0 in as criteria for
PiecesLeft I get a popup box asking for TotalofScheduledPieces?

The two query fields are

PiecesLeft:
Nz([NumberofPiecesReceived],0)-Nz([TotalofScheduledPieces],0)

TotalofScheduledPieces:
DSum("[NumberofPiecesScheduled]","Piecesscheduledtbl","[SLotTrackID]="
& [LotTrackID])

Both fields return correct values until I specify criteria
for PiecesLeft. Any suggestions would be appreciated. Thank-you
Click to expand...

The problem is that you can't (reliably) reuse one calculated field in
the definition of a second calculated field. Try replacing the
definition of PiecesLeft with

PiecesLeft: Nz([NumberofPiecesReceived],0) -
Nz(DSum("[NumberofPiecesScheduled]","Piecesscheduledtbl","[SLotTrackID]="
& [LotTrackID])

i.e. redoing the calculation of the total scheduled pieces.
 
Thank-you my query works now. Not reusing a calculated
field is now in my list of things to check when I have
problems. I appreciate your help.
-----Original Message-----
I am trying to get query to return records where PiecesLeft
field is greater than 0. When I put >0 in as criteria for
PiecesLeft I get a popup box asking for TotalofScheduledPieces?

The two query fields are

PiecesLeft:
Nz([NumberofPiecesReceived],0)-Nz([TotalofScheduledPieces],0)

TotalofScheduledPieces:
DSum("[NumberofPiecesScheduled]","Piecesscheduledtbl","[SLotTrackID]="
& [LotTrackID])

Both fields return correct values until I specify criteria
for PiecesLeft. Any suggestions would be appreciated. Thank-you
Click to expand...

The problem is that you can't (reliably) reuse one calculated field in
the definition of a second calculated field. Try replacing the
definition of PiecesLeft with

PiecesLeft: Nz([NumberofPiecesReceived],0) -
Nz(DSum("[NumberofPiecesScheduled]","Piecesscheduledtbl","[SLotTrackID]="
& [LotTrackID])

i.e. redoing the calculation of the total scheduled pieces.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
.
Click to expand...
 
Back
Top