D
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;
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;