Debugging a Query (1 Viewer)

[TheSearcher]

I am trying to use a msgbox to debug a query I have written in VBA code. However, the msgbox is not large enough to display the whole query. Is there any way to expand the msgbox so that I can see the whole query?

sql1 = "SELECT tbl_Authorizations.ID, [tbl_Authorizations].[client_LName] & ', ' & [tbl_Authorizations].[client_FName] AS Client_Name, tbl_Authorizations.Auth_No, "
sql1 = sql1 & "tbl_Authorizations.Therapy, tbl_Authorizations.Auth_StartDate AS Auth_SDate, tbl_Authorizations.Auth_EndDate AS Auth_EDate, tbl_Authorizations.Payer, "
sql1 = sql1 & "tbl_Authorizations.Number_UnitsApproved AS Total_Units, Sum(tbl_Billing.Number_UnitsBilled) AS Billed, tbl_Authorizations.Units_PerWeek AS Units_PWeek, "
sql1 = sql1 & "tbl_Authorizations.Client_Id "
sql1 = sql1 & "FROM tbl_Authorizations INNER JOIN tbl_Billing ON (tbl_Authorizations.Auth_No = tbl_Billing.Auth_No) AND (tbl_Authorizations.Therapy = tbl_Billing.Therapy) "
sql1 = sql1 & "AND (tbl_Authorizations.Client_Id = tbl_Billing.Client_Id) "
sql1 = sql1 & "GROUP BY tbl_Authorizations.ID, [tbl_Authorizations].[client_LName] & ', ' & [tbl_Authorizations].[client_FName], tbl_Authorizations.Auth_No, "
sql1 = sql1 & "tbl_Authorizations.Therapy, tbl_Authorizations.Auth_StartDate, tbl_Authorizations.Auth_EndDate, tbl_Authorizations.Payer, tbl_Authorizations.Number_UnitsApproved, "
sql1 = sql1 & "tbl_Authorizations.Units_PerWeek, tbl_Authorizations.Client_Id "
sql1 = sql1 & "HAVING (((tbl_Authorizations.Auth_EndDate) > Date()) "
sql1 = sql1 & "AND " & Globals.glb_Whereclause_Hold & ""
sql1 = sql1 & " ORDER BY [tbl_Authorizations].[client_LName] & ', ' & [tbl_Authorizations].[client_FName], tbl_Authorizations.Auth_StartDate DESC;"

MsgBox sql1

 

[MajP]

You can build your own custom form, but I think this one does it already.

MS Access: Enhanced Message Box Replacement – Cypris' lookout

 

[CJ_London]

Or use debug.print sql1

 

[sonic8]

I am trying to use a msgbox to debug a query I have written in VBA code.

A MsgBox is not particularly helpful for debugging a query.
You could output your SQL to the Immediate Window as an alternative using Debug.Print ....
Even more convenient would be to create (by code) a real query with your SQL and open that instead. Then you could run the query and evaluate the results right away. Creating the query might fail, if there are syntax errors in your SQL. So, still add the output to the Immediate Window as a fallback.

 

[jdraw]

You could break up your query sql into smaller pieces. Make sure the smaller parts work, then incrementally add the additional tables and fields. Using debug.print will show in the immediate window how Access interpreted your SQL. Helpful if a syntax issue exists. Running a smaller query error free -even with restricted scope- will give you a result that you can verify. As you add "complexity to your evolving query", you may get a syntax error that can be reviewed (googled) for appropriate corrective action(s).

Good Sql REFERENCE w3schools sql
SQL reformatter poorsql.com

 

[TheSearcher]

Thank you all. Very useful information! I solved my problem after I noticed a syntax error involving parentheses.

 

[ebs17]

There is a nice add-in for viewing and testing queries assembled in VBA:

http://access-codelib.net/download/addins/SqlDebugPrint.zip

 

[Pat Hartman]

You could also condense the string by using Alias' for the table names

"FROM tbl_Authorizations as Auth INNER JOIN tbl_Billing as Bill ON (Auth.Auth_No = Bill.Auth_No) AND (Auth.Therapy = Bill.Therapy) "
sql1 = sql1 & "AND (Auth.Client_Id = Bill.Client_Id) "

 

[ClaraBarton]

Personally, I debug into the immediate window, copy that to Create a New Query SQL view and then Shift F2 to the zoom box. The new zoom box is terrific for editing... Big font, Roomy... what's not to like? And you know it's right when the query runs!

 

[CJ_London]

You could also condense the string by using Alias' for the table names

To add to that, if you made your field names unique to the application you wouldn’t need to use table names at all- except for the join

 

[ebs17]

To add to that, if you made your field names unique to the application you wouldn’t need to use table names at all- except for the join

This is problematic, with SELF JOINs the clarity is lost.

When I query, I have to see which table a field comes from. Therefore, I generally use table aliases when more than one table is used.
Prefixes from shortened table names are often used to clarify field names. Something like this worsens the visibility to the outside world. But especially when I switch between draft and datasheet view, the field names all look the same, especially with longer prefixes, and I need a lot more time to orient myself. Something like that bothers me.
If you create calculated fields using the expression generator, table names are omitted entirely. This is a nasty problem if you don't know the application and its fields by heart. Developing should primarily be a creative process, not a search and lookup of what you are currently dealing with.

 

[CJ_London]

This is problematic, with SELF JOINs the clarity is lost.

Please clarify. I'm suggesting rather than calling a field 'Therapy' for both primary and foreign keys, use 'TherapyPK' and 'TherapyFK' respectively So I don't see how clarity is lost.

For a self referencing join I might give the FK field a different name such as 'parentFK'. All depends on context

If the Therapy primary table is referenced by many other tables rather than have 'TherapyFK' in each of those tables, add a short prefix - usually associated with the table name - or live with including the table name when required (and I use table aliases as you do). For example a db with a customer table and order, invoice and contact child tables I would have CustPK, ordCustFK, invCustFK and conCustFK.

Although I use the QBE most of my sql these days is freetyped whether it be in the sql window or VBA, my philosophy is to keep it as short as possible while maintaining readability. So I use short but meaningful names with some abbreviations e.g. I wouldn't use CustomerName, I would use CustName or perhaps CustNm.

But that's me

 

[ebs17]

Please clarify

SELF JOIN means multiple instances of the same table are used. Field names then appear several times in the same way.

Below is an example query where combinations of three are determined from customers.
Table aliases have the great advantage that they can be extremely shortened. They have no external effect, unlike field names and column aliases. They just have to be understandable within the query and be sufficiently different from others.

SELECT
   C1.CustomerName AS Person1,
   C2.CustomerName AS Person2,
   C3.CustomerName AS Person3
FROM
   tblCostumers AS C1,
   tblCostumers AS C2,
   tblCostumers AS C3
WHERE
   C1.CustomerName < C2.CustomerName
      AND
   C2.CustomerName < C3.CustomerName

When designing, I mostly work in the SQL view because that's where I have an overview of everything. However, an easily accessible formatting of the SQL statement is necessary.

 

[amorosik]

I am trying to use a msgbox to debug a query I have written in VBA code. However, the msgbox is not large enough to display the whole query. Is there any way to expand the msgbox so that I can see the whole query?

Use a classic TextBox on a form