Change is query to Populate Yes,No and NA options in the listbox

[aman]

Hi All

This is my query which works fine so when the AgentSignOff=true then it displays 'Yes' otherwise 'No' . So this way for all the records AgentSignOff gets populated with "Yes" or "No".
but I just want to amend it so that it should check if the record is present in tbl_RMS_Paperless then checks the following : AgentSignOff=true then it displays 'Yes' otherwise 'No'.

If the record is not present in tbl_RMS_Paperless but it is present in tblAudit then populate AgentSignOff=NA for those records.

SELECT tblAudit.AuditID, tblAudit.[Staff Number], tblAudit.[Date of Observation], tblCallType.[Call Type], tblAuditPurpose.Purpose, tblObjective.Objective, tblOutcome.Outcome, IIf([AgentSignOff]=True,"Yes","No") AS Expr1, IIf([SupSignOff]=True,"Yes","No") AS Expr2
FROM tbl_RMS_Paperless RIGHT JOIN (tblOutcome INNER JOIN (tblObjective INNER JOIN (tblAuditPurpose INNER JOIN (tblCallType INNER JOIN tblAudit ON tblCallType.[Call TypeID] = tblAudit.[Call TypeID]) ON tblAuditPurpose.[Audit PurposeID] = tblAudit.[Audit PurposeID]) ON tblObjective.ObjectiveID = tblAudit.ObjectiveID) ON tblOutcome.OutcomeID = tblAudit.OutcomeID) ON tbl_RMS_Paperless.ActivityRef = tblAudit.AuditID
WHERE (((tblAudit.[Staff Number])=[cbomoveto]))
ORDER BY tblAudit.[Date of Observation] DESC , tblAudit.AuditID DESC;

The listbox rowsourc e is set with the query finalloy as below:

me.lstdata.rowsource=strsql

Any help will be much appreciated.

Many Thanks

 

[CJ_London]

perhaps something like a nested iif

iif([AgentSignOff] is null, "NA",IIf([AgentSignOff],"Yes","No"))

 

[aman]

Thanks CJ_London. When I run the query by itself then it works fine but when I try to populate listbox with the result then it displays 'No' instead of 'NA'

Please see below. The table has a query in it at record no. 24

sAudit = DLookup("[Variable]", "tblVariable", "[VariableID]=24")
Me.lstData.RowSource = sAudit

 

[CJ_London]

implies that [AgentSignOff] isn't null. I don't know which table it is in - I assumed it was in tbl_RMS_Paperless and would produce a null if it wasn't matched by a record in tblAudit

 

[aman]

Yes you are right. When I try to use the same query with the change you mentioned as below in the form code then it works fine:

strsql = "SELECT tblAudit.AuditID, tblAudit.[Staff Number], tblAudit.[Date of Observation], tblCallType.[Call Type], tblAuditPurpose.Purpose, tblObjective.Objective, tblOutcome.Outcome, IIf([AgentSignOff] Is Null,'NA',IIf([AgentSignOff],'Yes','No')) AS Expr1, IIf([SupSignOff] Is Null,'NA',IIf([SupSignOff],'Yes','No')) AS Expr2 FROM tbl_RMS_Paperless RIGHT JOIN (tblOutcome INNER JOIN (tblObjective INNER JOIN (tblAuditPurpose INNER JOIN (tblCallType INNER JOIN tblAudit ON tblCallType.[Call TypeID] = tblAudit.[Call TypeID]) ON tblAuditPurpose.[Audit PurposeID] = tblAudit.[Audit PurposeID]) ON tblObjective.ObjectiveID = tblAudit.ObjectiveID) ON tblOutcome.OutcomeID = tblAudit.OutcomeID) ON tbl_RMS_Paperless.ActivityRef = tblAudit.AuditID WHERE (((tblAudit.[Staff Number]) = " & Me.cboMoveTo & ")) ORDER BY tblAudit.[Date of Observation] DESC , tblAudit.AuditID DESC;"

  Me.lstData.RowSource = strsql

 

[aman]

I have one more problem in the below query. It gives me 'Yes' For all the records.

strsql = "SELECT tblMeeting.MeetingID, tblMeeting.[Staff Number], tblMeeting.[Meeting Date], tblMeetingType.[Meeting Type], IIf([AgentSignOff] Is Null,'NA',IIf([AgentSignOff],'Yes','No')) AS Expr1, IIf([SupSignOff] Is Null,'NA',IIf([SupSignOff],'Yes','No')) AS Expr2 FROM (tblMeeting INNER JOIN tblMeetingType ON tblMeeting.MeetingTypeID = tblMeetingType.MeetingTypeID) LEFT JOIN tbl_RMS_Paperless ON tblMeeting.[Staff Number] = tbl_RMS_Paperless.AgentRef WHERE (((tblMeeting.[Staff Number]) =" & Me.cboMoveTo & ")) ORDER BY tblMeeting.[Meeting Date] DESC;"

And if I remove the condition Staffnumber =cbomoveto then it works fine.

But I need to see all the records of the individual for the meetings he attended.

Any help will be much appreciated.

Many Thanks

 

[CJ_London]

sorry - now out of time. I have work to complete before the weekend and out of the office most of next week.

You have the principles, you'll just need to try it different ways