none
Pass-Through Query - behaves differently to that of the Access Query RRS feed

  • Question

  • Hi MSDN, 

    I have a Access query with SQL Syntax

    [Code]

    SELECT Calls.[Due Date], [Job Order].[Job Order], Employees.[Update Name], Category.Category, Calls.ID, Calls.[Job Number], [Job Time].[Job Time],

    [Calls].[Job Number] & Chr(13) & Chr(10) &

    IIf([Calls].[Call Back]="Yes","Call Back" & Chr(13) & Chr(10),"") &

    [Customers].[Full Name] & Chr(13) & Chr(10) &

    IIf(IsNull([Customers].[Business Phone]),"",[Customers].[Business Phone] & Chr(13) & Chr(10)) &

    IIf(IsNull([Customers].[Home Phone]),"",[Customers].[Home Phone] & Chr(13) & Chr(10)) &

    IIf(IsNull([Customers].[Mobile Phone]),"",[Customers].[Mobile Phone] & Chr(13) & Chr(10)) &

    IIf([Calls].[Call Before]="Yes","Call: " & [Calls].[Call Before] & Chr(13) & Chr(10),"") &

    [Customers].[City] & Chr(13) & Chr(10) &

    [Category].[Category] & Chr(13) & Chr(10) &

    [Calls].[Status] AS Dashboard

    FROM Customers RIGHT JOIN (Category RIGHT JOIN ([Estimated Time of Job] RIGHT JOIN ([Job Time] RIGHT JOIN (Employees AS Employees_1 RIGHT JOIN (Employees RIGHT JOIN ([Job Order] RIGHT JOIN Calls ON [Job Order].ID = Calls.[Job Order]) ON Employees.ID = Calls.[Assigned To]) ON Employees_1.ID = Calls.[Resolved By]) ON [Job Time].ID = Calls.[Job Time]) ON [Estimated Time of Job].ID = Calls.[Estimated Time of Job]) ON Category.ID = Calls.Category) ON Customers.ID = Calls.Caller

    WHERE (((Calls.[Due Date])=[Forms]![Dashboard List]![txtDate]))

    ORDER BY Calls.[Due Date], [Job Order].ID;

    [/code]

    The component of AS Dashboard in the above syntax, will retrieve a concatenated string, regardless if any of the fields are present or missing, for example, if Customer Full Name is missing, it only has the full name missing from the string... Not the ENTIRE String.

    Now i converted this to a pass through query in access , and also tested directly on the SQL Server,, and formatted to the following

    - Changing " to '  and CHR to CHAR

    and & to +

    and having to hard code the date (I am looking at forums that allow the dynamic generation of the pass-through code ... unless you can provide also a recommendation here

    [code]

    SELECT CONVERT(varchar, [Due Date],103) AS [Due Date], [Job Order].[Job Order], Employees.[Update Name], Category.Category, Calls.ID, Calls.[Job Number],

        Format([Job Time].[Job Time] ,'h:mm tt') AS [Job Time],

    ([Calls].[Job Number] + Char(13) + Char(10) +

    [Category].[Category] + Char(13) + Char(10) +

    [Calls].[Status]) AS Dashboard

    FROM Customers RIGHT JOIN (Category RIGHT JOIN ([Job Time] RIGHT JOIN (Employees RIGHT JOIN ([Job Order] RIGHT JOIN Calls

    ON [Job Order].ID = Calls.[Job Order]) ON Employees.ID = Calls.[Assigned To]) ON [Job Time].ID = Calls.[Job Time]) ON Category.ID = Calls.Category) ON Customers.ID = Calls.Caller

    WHERE (((Calls.[Due Date])='2018-08-01'))

    ORDER BY Calls.[Due Date], [Job Order].ID;

    [/code]

    If I add an extra line to the above AS Dashboard

    [Customers].[Full Name] + Char(13) + Char(10) +

    And in the data, the Full name is missing, then it returns no string at all for Dashboard.

    Is there something that can be seen in the T-SQL above that should not allow the string of Dashboard to disappear when a particular field is empty.

    Thank you kindly


    Thank you in Advance

    Thursday, August 2, 2018 1:18 AM

Answers

  • The plus operator (+), in both Access and SQL Server, has a special behavior when used with Null values.  Anything + Null yields a result of Null. Null means unspecified or unknown, so if you add and unknown quantity to anything, the result must also be unknown.

    The Access '&' operator, specifically defined as a string concatenator, behaves differently:  "Anything" & Null yields a result of "Anything".

    To solve your problem, you have to translate the IIf() expressions in your original query to the ST-SQL equivalent;  something like:

       CASE WHEN [Customers].[Full Name] IS NULL THEN '' ELSE [Customers].[Full Name] + Char(13) + Char(10) END

    You would do that for each of your maybe-null fields.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 2, 2018 4:42 AM
  • Great!  I didn't realize that SQL Server 2012 had added the IIf function, to work similar to the CASE WHEN structure I suggested.  If I'd known that -- and if I'd known you were using SQL Server 2012 or later -- I'd have suggested you do it just as you did.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 2, 2018 3:13 PM

All replies

  • The plus operator (+), in both Access and SQL Server, has a special behavior when used with Null values.  Anything + Null yields a result of Null. Null means unspecified or unknown, so if you add and unknown quantity to anything, the result must also be unknown.

    The Access '&' operator, specifically defined as a string concatenator, behaves differently:  "Anything" & Null yields a result of "Anything".

    To solve your problem, you have to translate the IIf() expressions in your original query to the ST-SQL equivalent;  something like:

       CASE WHEN [Customers].[Full Name] IS NULL THEN '' ELSE [Customers].[Full Name] + Char(13) + Char(10) END

    You would do that for each of your maybe-null fields.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 2, 2018 4:42 AM
  • Thank you kindly Dirk, 

    I have employed the logic to dynamically create the pass-through query and it retrieves fine now:

    Private Sub Command0_Click()
    Dim SPTQueryName As String
    Dim SQLString As String
    Dim ConnectString As String
    Dim strDate As Date
    Dim frmName As String

    frmName = "Dashboard List"

    strDate = Forms(frmName).Controls.Item("txtDate")

    SPTQueryName = "Dashboard View Start UPDATE"

    ConnectString = "ODBC;Description=SQL Management Server;DRIVER=SQL Server;SERVER=ABAPLUMBINGPC\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=ABA PLUMBING Call Tracker V7"

    SQLString = "SELECT CONVERT(varchar, [Due Date],103) AS [Due Date], [Job Order].[Job Order], Employees.[Update Name], Category.Category, "
    SQLString = SQLString & "Calls.ID , Calls.[Job Number], Calls.[Call Back], Calls.[Call Before], Calls.[Status], "
    SQLString = SQLString & "Customers.[City], Customers.[Full Name],Customers.[Business Phone],Customers.[Home Phone],"
    SQLString = SQLString & "Customers.[Mobile Phone] , "
    SQLString = SQLString & "([Calls].[Job Number] + Char(13) + Char(10) + "
    SQLString = SQLString & "IIf([Calls].[Call Back] = 'Yes','Call back' + Char(13) + Char(10),'') + "
    SQLString = SQLString & "IIf([Customers].[Full Name] IS NULL, '', [Customers].[Full Name] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Customers].[Business Phone] IS NULL, '', [Customers].[Business Phone] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Customers].[Home Phone] IS NULL, '', [Customers].[Home Phone] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Customers].[Mobile Phone] IS NULL, '', [Customers].[Mobile Phone] + Char(13) + Char(10)) + "
    SQLString = SQLString & "IIf([Calls].[Call Before] = 'Yes','Call: ' + [Calls].[Call Before] + Char(13) + Char(10),'') + "
    SQLString = SQLString & "IIf([Customers].[City] IS NULL, '', [Customers].[City] + Char(13) + Char(10)) + "
    SQLString = SQLString & "[Category].[Category] + Char(13) + Char(10) + "
    SQLString = SQLString & "[Calls].[Status]) AS Dashboard "
    SQLString = SQLString & "FROM Customers RIGHT JOIN (Category  RIGHT JOIN ([Job Time]  RIGHT JOIN (Employees RIGHT JOIN ([Job Order]  JOIN Calls "
    SQLString = SQLString & "ON [Job Order].ID = Calls.[Job Order]) ON Employees.ID = Calls.[Assigned To]) ON [Job Time].ID = Calls.[Job Time]) "
    SQLString = SQLString & "ON Category.ID = Calls.Category) ON Customers.ID = Calls.Caller "
    SQLString = SQLString & "WHERE (((Calls.[Due Date])='" & Format(strDate, "YYYY-MM-DD") & "')) "
    SQLString = SQLString & "ORDER BY Calls.[Due Date], [Job Order].ID;"



        Call CreateSPT(SPTQueryName, SQLString, ConnectString)
        
        
        
    SQLString = "SELECT Employees.* FROM Employees UNION SELECT Management.* FROM Management;"
    SPTQueryName = "Staff Extended"
        
            Call CreateSPT(SPTQueryName, SQLString, ConnectString)
            
            MsgBox "Finished"
            
        
        
        
        
    End Sub



    Public Sub CreateSPT(SPTQueryName As String, SQLString As String, ConnectString As String)
    '-----------------------------------------------
    ' SUB: CreateSPT()

    PURPOSE:
    ' From UtterAccess Forums
    ' Create s an SQL pass-through query using the supplied arguments:
    ' SPTQueryName: the name of the query to create
    ' SQLString: the query's SQL string
    ' ConnectString: the ODBC connect s tring, this must be at
    ' least "ODBC;"
    '---------------------- ------------------------- '
    On Error Resume Next

        Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
        Set mydatabase = DBEngine.Workspaces(0).Databases(0)
        mydatabase.QueryDefs.Delete SPTQueryName
        Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
        myquerydef.Connect = ConnectString
        myquerydef.SQL = SQLString
        myquerydef.Close
        
    End Sub


    Thank you in Advance

    Thursday, August 2, 2018 7:33 AM
  • Great!  I didn't realize that SQL Server 2012 had added the IIf function, to work similar to the CASE WHEN structure I suggested.  If I'd known that -- and if I'd known you were using SQL Server 2012 or later -- I'd have suggested you do it just as you did.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 2, 2018 3:13 PM