Field >0

[DS]

I have a Query. One field is an expression called ID. I want that field to
return the value that is not zero unless all three fields are 0, otherwise
it should return the field that is not zero. There are only a few
scenerios.

PayAppliedDocID = 0 PayAppliedDocID = 5
PayAppliedDocID=0 PayAppliedDocID=0
PayAppliedCreditID = 0 PayApliedCreditID = 0
PayAppliedCreditID=6 PayAppliedCreditID=0
PayAppliedCCNumber = 0 PayAppliedCCNumber = 0 PayAppliedCCNumber=0
PayAppliedCCNumber=8758

Then ID would be 0 Then ID would be 5 Then
ID would be 6 Then ID would be 8758

How would I code this?
Thanks
DS


SELECT tblPayApplied.PayAppliedBizDay,
IIf([PayTypeName]="Cash","Cash",[PayTypeName] & "S") AS PT,
tblPayName.PayName,
Choose([PayAppliedDocID],[PayAppliedCreditID],[PayAppliedCCNumber]) AS ID,
tblPayApplied.PayAppliedTip, tblPayApplied.PayAppliedDate,
tblPayApplied.PayAppliedTime, tblPayApplied.PayAppliedCheckID,
tblPayApplied.PayAppliedAmount
FROM (tblPayApplied LEFT JOIN tblPayName ON tblPayApplied.PayAppliedNameID =
tblPayName.PayNameID) LEFT JOIN tblPayTypes ON tblPayName.PayNameTypeID =
tblPayTypes.PayTypeID
WHERE
(((tblPayApplied.PayAppliedBizDay)>=[Forms]![frmReportDates]![TxtStart] And
(tblPayApplied.PayAppliedBizDay)<=[Forms]![frmReportDates]![TxtEnd]) AND
((Choose([PayAppliedDocID],[PayAppliedCreditID],[PayAppliedCCNumber]))>0))
ORDER BY IIf([PayTypeName]="Cash","Cash",[PayTypeName] & "S"),
tblPayName.PayName, tblPayApplied.PayAppliedDate,
tblPayApplied.PayAppliedTime;

 

[Bob Barrows [MVP]]

I have a Query. One field is an expression called ID. I want that
field to return the value that is not zero unless all three fields
are 0, otherwise it should return the field that is not zero. There
are only a few scenerios.

PayAppliedDocID = 0 PayAppliedDocID = 5
PayAppliedDocID=0 PayAppliedDocID=0
PayAppliedCreditID = 0 PayApliedCreditID = 0
PayAppliedCreditID=6 PayAppliedCreditID=0
PayAppliedCCNumber = 0 PayAppliedCCNumber = 0
PayAppliedCCNumber=0 PayAppliedCCNumber=8758

Then ID would be 0 Then ID would be 5
Then ID would be 6 Then ID would be 8758

I don't understand this example. Did something go wrong with your
formatting? Could you present these as records that a query would see?

 

[DS]

Yeah I agree, It got squished somehow! In any case I just came up with this
Function and it seems to work.
Thanks
DS

Public Function DOC(PayAppliedDocID, PayAppliedCreditID, PayAppliedCCNumber,
PayAppliedDepositID) As Integer
Dim ID As Integer
If ID = Nz(PayAppliedDocID + PayAppliedCreditID + PayAppliedCCNumber +
PayAppliedDepositID, 0) Then
DOC = 0
Else
If PayAppliedDocID > 0 And _
PayAppliedCreditID = 0 And _
PayAppliedCCNumber = 0 And _
PayAppliedDepositID = 0 Then
DOC = PayAppliedDocID
ElseIf PayAppliedDocID = 0 And _
PayAppliedCreditID > 0 And _
PayAppliedCCNumber = 0 And _
PayAppliedDepositID = 0 Then
DOC = PayAppliedCreditID
ElseIf PayAppliedDocID = 0 And _
PayAppliedCreditID = 0 And _
PayAppliedCCNumber > 0 And _
PayAppliedDepositID = 0 Then
DOC = PayAppliedCCNumber
ElseIf PayAppliedDocID = 0 And _
PayAppliedCreditID = 0 And _
PayAppliedCCNumber = 0 And _
PayAppliedDepositID > 0 Then
DOC = PayAppliedDepositID
End If
End If
End Function