Results 1 to 9 of 9
  1. #1
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187

    Query works perfect but not displaying the same in a listbox

    I have a Yes\No data-type field included in my query. My objective is to make it show nothing (Null) if the field is null, and if it's not null, display "Yes" or "No" depending on the value. When I execute this query in the SQL window it works perfect, however when it populates my listbox it displays "No" instead of nothing if the field is Null.

    Code:
    SELECT tblWT.EmployeeID, tblWT.LastName, tblWT.FirstName, tblAttendance.ScheduledDate, Format([tblAttendance.ScheduledTime],"hh:nn AM/PM") AS ScheduledTime, IIf([tblAttendance.Attended] Is Not Null,IIf([tblAttendance.Attended],"Yes","No"),Null) AS HasAttended
    FROM tblWT LEFT JOIN tblAttendance ON tblWT.EmployeeID = tblAttendance.EmployeeID
    WHERE (((tblWT.Department)=[Forms].[frmAttendance]![cmbDepartment])) OR (((IsNull([Forms].[frmAttendance]![cmbDepartment]))<>False))
    ORDER BY tblWT.LastName;


  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try:
    IIf([tblAttendance.Attended] Is Not Null,[tblAttendance.Attended],Null) AS HasAttended
    It seems to me that you don't need the IIF, just use the field.

  3. #3
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    That will just display -1/0, depending on the value. In this case, 0 is shown whether its false or null. Again, this works fine in query\execute mode, but not when populated into a listbox.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    In that case try:
    IIf([tblAttendance.Attended] Is Not Null,IIf([tblAttendance.Attended],"Yes","No"),"") AS HasAttended

  5. #5
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I tried that, same thing.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    All right then how about:
    IIf(Not IsNull([tblAttendance.Attended]),IIf([tblAttendance.Attended],"Yes","No"),"") AS HasAttended

  7. #7
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    Same thing

  8. #8
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    GOT IT! Changed the data type in the field from Yes\No to Number

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Great! Glad to hear you got it working.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2012, 11:45 AM
  2. Query works but VBA fails
    By dumbledown in forum Queries
    Replies: 11
    Last Post: 03-16-2012, 10:37 AM
  3. Replies: 1
    Last Post: 01-06-2012, 03:55 PM
  4. Update Query how it works
    By waqas in forum Queries
    Replies: 10
    Last Post: 09-10-2011, 11:04 AM
  5. Update Query Works Once
    By Lorlai in forum Access
    Replies: 2
    Last Post: 07-22-2011, 08:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums