Change query to populate listbox

[AmanKaur123]

I have one problem in the below query. It gives me only 'Yes' in agentsignoff field and supsignoff field 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

[jwhite]

I formatted your statement for readability, substituting "999" for your parameter field.

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] ) = 999 )) 
ORDER  BY tblmeeting.[meeting date] DESC;

The False case for the first Iif in both cases is just Iif([field],'Yes','No'). What Data Type is [agentsignoff] and [subsignoff] and what values are in the table for each?

[AmanKaur123]

The AgentSignOff and SupSignoff have Yes/No datatype. But these fields are only present in tbl_RMS_Paperless table. I want to link it to tblmeeting which has far more records than tbl_RMS_paperless.

please see attached the spreadsheet. The Sheet1 which shows all the records with no condition( all staff numbers) which is incorrect (as it gives answer in yes or no but there are loads and loads of records in tbmeeting so for then it should be NA)

The Sheet2 which shows all the records for Aoife which is also incorrect.

And also in actual tblmeeting has only one record for Aoife with reference 26672 but it appears multiple times in the result.

The query is wrong and needs to be amended . Any hep will be much appreciated. Many Thanks