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,923
    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.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  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,923
    In that case try:
    IIf([tblAttendance.Attended] Is Not Null,IIf([tblAttendance.Attended],"Yes","No"),"") AS HasAttended
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  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,923
    All right then how about:
    IIf(Not IsNull([tblAttendance.Attended]),IIf([tblAttendance.Attended],"Yes","No"),"") AS HasAttended
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  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,923
    Great! Glad to hear you got it working.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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