debug SQL

[artemisia]

New user looking for help. Is there a way to debug or check the SQL statement in Access?
Thank You ^.^

 

[dcx693]

Can you be more specific? What type of SQL statement do you have?

 

[artemisia]

I am using Access 2000. I am trying to debug an SQL code for a Query that is not working. Access help says you can debug this but you need the debug toolbar. Problem is...I can't find the debug toolbar. I looked in View, Toolbar, Customize and it's not there.
Whe I installed the program I just did a typical install. Someone told me to do a custom and install everything. Is this true? Maybe some of the application was not installed. That's the only think I can think of.
Your feedback is greatly appreciated.

 

[dcx693]

Can you post the SQL code here so we can help you debug it? The debugging toolbar is part of the Visual Basic Editor environment, and should be a standard part of the Access install. It's best for debugging VBA programming, not SQL code. Access doesn't really have a SQL debugging environment.

 

[artemisia]

Here is the SQL statement: SELECT [Commissions payable2 by salesperson query].[Order Master].[Order Number], [Commissions payable2 by salesperson query].[Order Date], [Commissions payable2 by salesperson query].[Company Name], [Commissions payable2 by salesperson query].[SalesPerson], [Commissions payable2 by salesperson query].[Delivery Date], [Commissions payable2 by salesperson query].[Commission Date], [Commissions payable2 by salesperson query].[Market], [Commissions payable2 by salesperson query].[Order Master].[Manager], [Commissions payable2 by salesperson query].[Paid], [Commissions payable2 by salesperson query].[Invoice], [Commissions payable2 by salesperson query].[IMEI], [Commissions payable2 by salesperson query].[Data], [Commissions payable2 by salesperson query].[Feature 2], [Commissions payable2 by salesperson query].[Feature 3], [Commissions payable2 by salesperson query].[Feature 4], [Commissions payable2 by salesperson query].[Comp Plan], [Commissions payable2 by salesperson query].[Rate Plan Description], [Commissions payable2 by salesperson query].[Value], [Commissions payable2 by salesperson query].[Carrier], [Commissions payable2 by salesperson query].[Level], [Comp Plan Table].[Compensation], [Comp Plan Table].[Count Value], [Comp Plan Table].[Qualify for Quota], [Comp Plan Table].[Quota Value], [Comp Plan Table].[F1 Comp], [Comp Plan Table].[F2 Comp], [Comp Plan Table].[F3 Comp], [Comp Plan Table].[F4 Comp], [Commissions payable2 by salesperson query].[Rate Plan Cost], [Commissions payable2 by salesperson query].[Rate Plan].[Rate Plan], IIf([comp plan table.level]<0,[comp plan table.Compensation]) AS cb, IIf([comp plan table.level]>0,[comp plan table.Compensation]) AS [sales comp], [Commissions payable2 by salesperson query].[Reserve]
FROM [Commissions payable2 by salesperson query] INNER JOIN [Comp Plan Table] ON ([Commissions payable2 by salesperson query].[Comp Plan]=[Comp Plan Table].[Comp Plan]) AND ([Commissions payable2 by salesperson query].[Level]=[Comp Plan Table].[Level])
WITH OWNERACCESS OPTION;

This is what happened. We changed our commission amounts, and I added a column to the Comp Plan Table so I could indicate the type of commission, example: Emp, Mgr, Agent-TIC, Agent-NIP etx. After I added this column, the report would not print. So I deleted the column. Now the query runs but does not show any data and the report has error where data should be.

I noticed when I run the query now an extra column appeared where the deleted column was but there is no field name.

Any thoughts on this?

 

[Pat Hartman]

When installing a development tool such as Access, it is best to install ALL the wizards, help files, and sample files.

Paste the SQL into a new query and test it again. Make sure that the joins are correct that could be why you are not getting any data. Also, check the tables themselves to make sure that the data is there. What do you want to be the value of cb and [sales comp] if the conditions in the IIf()'s are not true? Also, what happens if level is 0?

FYI, it is really poor practice to use embedded spaces and special characters in object names. Although Access allows you to create names like these, they become a problem in VBA since NO programming language supports these names. Also, make sure you don't use reserved words (the names of functions, properties, methods, etc) as names for the objects that you define.

 

[artemisia]

I'll keep working at it.
The person who created the database is no longer with the company. I am trying to do updates as we need them.
Thank you!