Results 1 to 15 of 15
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    Help with using an IIf function in a SQL query in Access VBE.

    Greetings Again ~

    I have a ListBox using an SQL query as its RowSource

    The source table is a SQL server linked table.

    One of the fields showing in this ListBox is a Yes/No (bit datatype) field which is interpreted in the ListBox as either 0 or -1

    What I'm trying to do is use an IIF function inside the SQL query to change the 0 (or False) to No and the -1 (or True) to Yes

    The query tests perfectly in the SQL server, but when I put the query in the Access VBE - it doesn't fail but it pulls an empty recordset.

    I've tried several variations - and, well here I am...


    Code:
    Me.ListSearchResults.RowSource = _
          "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 'False', 'No', 'Yes') AS Printed, ID " _
        & "FROM tblUINPort " _
        & "WHERE (Printed = False AND DataEntered = True)" _
        & "ORDER BY [RequestDate] DESC;"
    

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Try
    Code:
    Me.ListSearchResults.RowSource = _
          "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 'False', 'No', 'Yes') AS PrintedTxt, ID " _
        & "FROM tblUINPort " _
        & "WHERE (Printed = False AND DataEntered = True)" _
        & "ORDER BY [RequestDate] DESC;"

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to have spaces before each section.

    Code:
    & "FROM tblUINPort " _
    & "WHERE (Printed = False AND DataEntered = True)" _
    & "ORDER BY [RequestDate] DESC;"
    so surprised the code works at all

    tip 1 - assign your code to a string which you can then debug.print. You can then copy/paste this to a query to make sure it runs and get more detailed error messages

    tip 2 - when building sql code in this way, put the space at the beginning of the line rather than the end. It is then very easy to see if you have missed a space

    the other error would appear to be here

    IIf(Printed = 'False', 'No', 'Yes')

    you say Printed is a bit either 0 or -1 - so how can it ever = 'False', a string? You appear to have got it right in your criteria

    Either way, bits cannot be negative, only positive.

    In SQL Server yes/no is stored as 0 or 1, in access 0 or -1. In both cases where false/no =0. So you are right to interrogate as false which will work in either access or sql server.

  4. #4
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Arvil - Ajax - Thanks for pitchin in on this one

    Arvil - I did add Txt to Printed - unfortunately, same blank results
    Ajax - Good to see you're still out there...
    Ok, so I did assign the query to a String Variable so I can view it in the immediate window -
    However, I may not be clear on the what you mean about copy/pasting into a query to get more detailed error messages - perhaps you can eloborate.
    Also, I thought by putting the ampersand then a space then the quotation mark I was putting a space at the front of each line - so I may be unclear on that as well.
    Finally, for the IIf statement I have tried the following: 0; '0'; "0"; #0#; False; 'False'; "False" And #False#
    Some efforts produced predictible errors while the others produced more of the same - nothing.
    Below are the queries that work in SQL Server 2014 & in the Access VBE - Plus a few iterations I have tried in VBE that fail (No errors - but no results)

    This produces the correct results in VBE - (But with a zero in the Printed field of the ListBox)
    Code:
    Me.ListSearchResults.RowSource = _
          "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, Printed, ID " _
        & "FROM tblUINPort " _
        & "WHERE (Printed = False AND DataEntered = True)" _
        & "ORDER BY [RequestDate] DESC;"
    This produces the correct results in SQL 2014 - (With 'No' in the Printed field)
    Code:
    SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0, 'No', 'Yes') AS Printed, ID
    FROM tri.UINPort
    WHERE (Printed = 'False' AND DataEntered = 'True')
    ORDER BY [RequestDate] DESC
    The following all produce no results
    Code:
    Me.ListSearchResults.RowSource = _
                "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0 'No', 'Yes') AS Printed, ID " & _
                "FROM tblUINPort " & _
                "WHERE (Printed = False AND DataEntered = True) " & _
                "ORDER BY [RequestDate] DESC;"
    Code:
    StrSQL = "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0 'No', 'Yes') AS Printed, ID " _
         & "FROM tblUINPort " _
         & "WHERE (Printed = False AND DataEntered = True) " _
         & "ORDER BY [RequestDate] DESC;"
        
        Me.ListSearchResults.RowSource = StrSQL
    Code:
     StrSQL = "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0 'No', 'Yes') AS Printed, ID " & _
                 "FROM tblUINPort " & _
                 "WHERE (Printed = False AND DataEntered = True) " & _
                 "ORDER BY [RequestDate] DESC;"
    
        Me.ListSearchResults.RowSource = StrSQL

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I may not be clear on the what you mean about copy/pasting into a query to get more detailed error messages - perhaps you can eloborate.
    copy the code from the immediate window, open a new query, close down the table selection and go to sql view - paste the code then try to run it or return to the design grid view to see what your query looks like there

    with regards the ones that don't work - again you are missing characters - do the above and you will get a better idea of the issue

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried:
    Code:
    StrSQL = "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0, "No", "Yes") AS Printed, ID " _
         & "FROM tblUINPort " _
         & "WHERE (Printed = False AND DataEntered = True) " _
         & "ORDER BY [RequestDate] DESC;"

  7. #7
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Real quick update - there is a comma between the 0 and 'No' - I accidently ommited it when I typed out the code

  8. #8
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Gicu ~ Thank You for taking a peek at this one

    Sorry 'bout the missing ',' I tried the quotations instead of the ticks - but it produces an 'End of Statement' Error

    Ajax - I'm working with your idea now

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I accidently ommited it when I typed out the code
    strongly recommend you copy/paste the code to the thread, otherwise time is wasted pointing out irrelevant typo's

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Also, maybe you could post the entire code so we get an idea why you need to set the rowsource in code and not just in the actual Row Source property of the list box (the one I posted should work just fine in there).

    Cheers,
    Vlad

  11. #11
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Ajax ~ Yeah, I actually did copy/paste -
    Where I made the error was when I changed the VBA code back to 0 from False
    I accidently deleted the comma and simply didn't catch it before/after copy/pasting
    Sorry 'bout that

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think the problem occurs because of the aliasing, can you try:
    Code:
    SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0, 'No', 'Yes') AS WasPrinted, ID
    FROM tri.UINPort
    WHERE (Printed = 'False' AND DataEntered = 'True')
    ORDER BY [RequestDate] DESC
    Cheers,
    Vlad

  13. #13
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Boom!!
    And the winner is...

    Code:
    StrSQL = "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0, 'No', 'Yes') AS [Printed?], ID " _
        & "FROM tblUINPort " _
        & "WHERE(Printed = False AND DataEntered = True) " _
        & "ORDER BY [RequestDate] DESC;"
    The above code works perfectly!!
    Thank You, Vlad!
    Ajax - I'm not fogetting you - I always appreciate your help as well.
    Thank you both for sticking with me on this - I would have likely not figured this one out
    Note to self - Can't use field name as alias name... Got it!

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to help!
    Cheers,
    Vlad

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    My $0.02.....


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names. ........( [Printed?] is an extremely poor name because of the punctuation in the name. Better would be [IsPrinted] or[ WasPrinted].)
    Do not begin an object name with a number.


    In Post #3, Ajax suggested:
    tip 1 - assign your code to a string which you can then debug.print. You can then copy/paste this to a query to make sure it runs and get more detailed error messages
    tip 2 - when building sql code in this way, put the space at the beginning of the line rather than the end. It is then very easy to see if you have missed a space
    I create the string variables like this (as Alex suggests)
    Code:
    StrSQL = "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0, 'No', 'Yes') AS [Printed?], ID"
    StrSQL = StrSQL & " FROM tblUINPort"
    StrSQL = StrSQL & " WHERE(Printed = False AND DataEntered = True)"
    StrSQL = StrSQL & " ORDER BY [RequestDate] DESC;"
    Debug.Print StrSQL 
    
    Me.ListSearchResults.RowSource = StrSQL
    Because the spaces are at the beginning of the string, it is easier to see if a space is missing.
    The Debug.Print sends the SQL string to the Immediate window. You can then copy the SQL and paste it into a query (in SQL view) and execute the query to see if there are errors in the string.

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

Similar Threads

  1. Access 2010 Query Using IIF Function
    By pinkpanther6666 in forum Queries
    Replies: 10
    Last Post: 05-24-2016, 08:46 AM
  2. Help with Access Query function or code
    By molinamike in forum Queries
    Replies: 1
    Last Post: 12-10-2015, 12:21 AM
  3. [access 2007] Update Query And Function
    By Zoroxeus in forum Queries
    Replies: 4
    Last Post: 04-16-2012, 04:19 PM
  4. NZ Function in Access Query
    By Rosier75 in forum Queries
    Replies: 2
    Last Post: 11-02-2011, 06:14 AM
  5. Access SQL Query - Aggregate function
    By jack in forum Access
    Replies: 0
    Last Post: 11-10-2009, 08:06 PM

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