Using VBA to change a single parameter in a long SQL statement

[mikenelena]

We have a complicated series of queries for generating invoices, and it takes a good 20-30 seconds to generate an invoice, depending on how long ago the service assignment was received. I am looking to change a single parameter to speed this query, but I'm a bit intimidated by the length of the statement. I simply don't have experience translating that much SQL into VBA syntax.

I am looking to use code similar to the following to change this one parameter.

HAVING (((ClaimInfo1.Date_Received)>Now()-100))

based on the date an assignment was received. (A control on a form.)

Private Sub Form_Current()

Dim sql As String
Dim db As dao.Database
    
Set db = CurrentDb
Dim qdf As dao.QueryDef

Set qdf = db.QueryDefs("AdjusterFileHistory")
qdf.sql = "Select * From [AdjusterFileHistoryHelp] WHERE [Adjuster Name]=Forms.Clients3.frmBranches.Form.Subformcontainer.Form.[AdjusterFirstLast] And [Canceled] = False AND [Invoice_Number] like '*-01'"


Me.txtListCount = Me.FileList.ListCount
End Sub

Here is the SQL:

SELECT ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], [Service Fee]*[Qty]+[Other Charges$]+[Miles_Charged]*[per mile]+[Discount$]+[Toll_Charges] AS [Total Charges], IIf([Service Fee]<-[Discount$],0,IIf([State_Match]="WV",([Service Fee]*[Qty]+[Other Charges$]+[Discount$])*0.06,0)) AS [WV State Sales Tax], ClaimInfo2.[Service Fee], ClaimInfo2.Miles_Charged, ClaimInfo2.Miles_Traveled, ClaimInfo2.[Other Charges$], ClaimInfo2.[Other Charges Description], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.[Insured Names], ClaimInfo1.[Adjuster Name], ClaimInfo1.Invoice_Number, First(ClaimInfo1.[Invoice Notes]) AS [FirstOfInvoice Notes], ClaimInfo1.[Adjuster Name], Appraisers.email3, ClaimInfo1.Invoice_Date, ClaimInfo1.Canceled, tblItems.Service_Item, ClaimInfo2.Qty, ClaimInfo2.[Discount$], Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo2.[per mile], ClaimInfo2.[Discount Description], ClaimInfo2.Item_Code, ClaimInfo2.Toll_Charges, ClaimInfo2.[Toll Charges Description], ClaimInfo1.[Total Loss], ClaimInfo1.[Total Damages], ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.Non_Billable, ClaimInfo1.chkTransfer, qryAdjusterClientBranch.[Full Name], qryAdjusterClientBranch.AdjusterFirst, qryAdjusterClientBranch.AdjusterLast, qryAdjusterClientBranch.AdjusterSuffix, qryAdjusterClientBranch.Title, qryAdjusterClientBranch.Street, qryAdjusterClientBranch.Suite, qryAdjusterClientBranch.City, qryAdjusterClientBranch.State, qryAdjusterClientBranch.ZipCode, qryAdjusterClientBranch.BillingAddress, qryAdjusterClientBranch.ClientInactive, qryAdjusterClientBranch.AdjusterInactive, qryAdjusterClientBranch.Email, qryAdjusterClientBranch.EmailType, qryAdjusterClientBranch.BulkBilling
FROM qryAdjusterClientBranch INNER JOIN ((((ClaimInfo1 LEFT JOIN ClaimInfo2 ON ClaimInfo1.[Invoice_Number] = ClaimInfo2.[Invoice_Number]) LEFT JOIN tblItems ON ClaimInfo2.Item_Code = tblItems.Item_Code1) LEFT JOIN tblZip_State ON ClaimInfo1.Zip = tblZip_State.Loss_Zip) LEFT JOIN Appraisers ON ClaimInfo1.[Field Work Appraiser] = Appraisers.[Appraiser Name]) ON (qryAdjusterClientBranch.ClientName = ClaimInfo1.[Client Name]) AND (qryAdjusterClientBranch.[Full Name] = ClaimInfo1.[Adjuster Name])
GROUP BY ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], [Service Fee]*[Qty]+[Other Charges$]+[Miles_Charged]*[per mile]+[Discount$]+[Toll_Charges], IIf([Service Fee]<-[Discount$],0,IIf([State_Match]="WV",([Service Fee]*[Qty]+[Other Charges$]+[Discount$])*0.06,0)), ClaimInfo2.[Service Fee], ClaimInfo2.Miles_Charged, ClaimInfo2.Miles_Traveled, ClaimInfo2.[Other Charges$], ClaimInfo2.[Other Charges Description], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.[Insured Names], ClaimInfo1.Invoice_Number, ClaimInfo1.[Adjuster Name], Appraisers.email3, ClaimInfo1.Invoice_Date, ClaimInfo1.Canceled, tblItems.Service_Item, ClaimInfo2.Qty, ClaimInfo2.[Discount$], Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo2.[per mile], ClaimInfo2.[Discount Description], ClaimInfo2.Item_Code, ClaimInfo2.Toll_Charges, ClaimInfo2.[Toll Charges Description], ClaimInfo1.[Total Loss], ClaimInfo1.[Total Damages], ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.Non_Billable, ClaimInfo1.chkTransfer, qryAdjusterClientBranch.[Full Name], qryAdjusterClientBranch.AdjusterFirst, qryAdjusterClientBranch.AdjusterLast, qryAdjusterClientBranch.AdjusterSuffix, qryAdjusterClientBranch.Title, qryAdjusterClientBranch.Street, qryAdjusterClientBranch.Suite, qryAdjusterClientBranch.City, qryAdjusterClientBranch.State, qryAdjusterClientBranch.ZipCode, qryAdjusterClientBranch.BillingAddress, qryAdjusterClientBranch.ClientInactive, qryAdjusterClientBranch.AdjusterInactive, qryAdjusterClientBranch.Email, qryAdjusterClientBranch.EmailType, qryAdjusterClientBranch.BulkBilling, ClaimInfo2.Appraiser
HAVING (((ClaimInfo1.Date_Received)>Now()-100))
ORDER BY ClaimInfo1.File_Number;

Is this the right way to go about this? What is the easiest way to get the syntax correct on a long statement like this?

Thanks!

 

[Micron]

Not enough info to decide on which way to go, but this should not be too difficult. What I can't figure out from your post is
- why show 2 sql statements, one the qdf sql, the other is the long one, and which one do you want to alter based on a control?
- are you going to run the altered sql in code or is that long one from an actual query that you want to run?

I see no reason for that Current event code unless maybe you're altering the form recordsource (the sql of the query). Question is, does Current event requery the form to what you might be altering it to? Not sure but I don't think so. Regardless, it's a bit lacking in terms of code.

Attempting to simplify an answer based on your example, then if the code is on the form where the control is and this is for a date, something like:

HAVING (((ClaimInfo1.Date_Received)> #" & Me.SomeControlName & "#))"

That assumes that there is something that precedes it that is properly quoted.

 

[mikenelena]

Micron,

Thanks for your response. That On Current event code may not even be necessary. I stumbled into it looking for a solution to a different problem, and thought perhaps it would work for this issue as well.

To respond directly to your questions, the long SQL is what we are looking to run. It currently resides in the query only. I thought changing it based on a control could be accomplished through VBA, but perhaps that's not even necessary if I use the right control reference in the query SQL. The control I'm looking to reference is, in fact, a date. I'll try to see if I can get the syntax correct to add a control reference in the query this weekend.

Thanks!

 

[Micron]

The example I gave was based on vba sql. Since you say that the long sql is in a query, you can't use Me to reference anything outside of the form (or a report. You could put a form control reference in the query though. Perhaps

HAVING (((ClaimInfo1.Date_Received)> #" & Forms!FormNameHere.ControlNameHere & "#))"

You could probably achieve this simply by entering the form control reference in the proper field in the query design grid as
Forms!FormNameHere.ControlNameHere

 

[JonXL]

Maybe this will be enough to speed it up?

SELECT ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], [Service Fee]*[Qty]+[Other Charges$]+[Miles_Charged]*[per mile]+[Discount$]+[Toll_Charges] AS [Total Charges], IIf([Service Fee]<-[Discount$],0,IIf([State_Match]="WV",([Service Fee]*[Qty]+[Other Charges$]+[Discount$])*0.06,0)) AS [WV State Sales Tax], ClaimInfo2.[Service Fee], ClaimInfo2.Miles_Charged, ClaimInfo2.Miles_Traveled, ClaimInfo2.[Other Charges$], ClaimInfo2.[Other Charges Description], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.[Insured Names], ClaimInfo1.[Adjuster Name], ClaimInfo1.Invoice_Number, First(ClaimInfo1.[Invoice Notes]) AS [FirstOfInvoice Notes], ClaimInfo1.[Adjuster Name], Appraisers.email3, ClaimInfo1.Invoice_Date, ClaimInfo1.Canceled, tblItems.Service_Item, ClaimInfo2.Qty, ClaimInfo2.[Discount$], Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo2.[per mile], ClaimInfo2.[Discount Description], ClaimInfo2.Item_Code, ClaimInfo2.Toll_Charges, ClaimInfo2.[Toll Charges Description], ClaimInfo1.[Total Loss], ClaimInfo1.[Total Damages], ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.Non_Billable, ClaimInfo1.chkTransfer, qryAdjusterClientBranch.[Full Name], qryAdjusterClientBranch.AdjusterFirst, qryAdjusterClientBranch.AdjusterLast, qryAdjusterClientBranch.AdjusterSuffix, qryAdjusterClientBranch.Title, qryAdjusterClientBranch.Street, qryAdjusterClientBranch.Suite, qryAdjusterClientBranch.City, qryAdjusterClientBranch.State, qryAdjusterClientBranch.ZipCode, qryAdjusterClientBranch.BillingAddress, qryAdjusterClientBranch.ClientInactive, qryAdjusterClientBranch.AdjusterInactive, qryAdjusterClientBranch.Email, qryAdjusterClientBranch.EmailType, qryAdjusterClientBranch.BulkBilling
FROM qryAdjusterClientBranch INNER JOIN ((((ClaimInfo1 LEFT JOIN ClaimInfo2 ON ClaimInfo1.[Invoice_Number] = ClaimInfo2.[Invoice_Number]) LEFT JOIN tblItems ON ClaimInfo2.Item_Code = tblItems.Item_Code1) LEFT JOIN tblZip_State ON ClaimInfo1.Zip = tblZip_State.Loss_Zip) LEFT JOIN Appraisers ON ClaimInfo1.[Field Work Appraiser] = Appraisers.[Appraiser Name]) ON (qryAdjusterClientBranch.ClientName = ClaimInfo1.[Client Name]) AND (qryAdjusterClientBranch.[Full Name] = ClaimInfo1.[Adjuster Name])
WHERE (((ClaimInfo1.Date_Received)>Now()-100))
GROUP BY ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], [Service Fee]*[Qty]+[Other Charges$]+[Miles_Charged]*[per mile]+[Discount$]+[Toll_Charges], IIf([Service Fee]<-[Discount$],0,IIf([State_Match]="WV",([Service Fee]*[Qty]+[Other Charges$]+[Discount$])*0.06,0)), ClaimInfo2.[Service Fee], ClaimInfo2.Miles_Charged, ClaimInfo2.Miles_Traveled, ClaimInfo2.[Other Charges$], ClaimInfo2.[Other Charges Description], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.[Insured Names], ClaimInfo1.Invoice_Number, ClaimInfo1.[Adjuster Name], Appraisers.email3, ClaimInfo1.Invoice_Date, ClaimInfo1.Canceled, tblItems.Service_Item, ClaimInfo2.Qty, ClaimInfo2.[Discount$], Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo2.[per mile], ClaimInfo2.[Discount Description], ClaimInfo2.Item_Code, ClaimInfo2.Toll_Charges, ClaimInfo2.[Toll Charges Description], ClaimInfo1.[Total Loss], ClaimInfo1.[Total Damages], ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.Non_Billable, ClaimInfo1.chkTransfer, qryAdjusterClientBranch.[Full Name], qryAdjusterClientBranch.AdjusterFirst, qryAdjusterClientBranch.AdjusterLast, qryAdjusterClientBranch.AdjusterSuffix, qryAdjusterClientBranch.Title, qryAdjusterClientBranch.Street, qryAdjusterClientBranch.Suite, qryAdjusterClientBranch.City, qryAdjusterClientBranch.State, qryAdjusterClientBranch.ZipCode, qryAdjusterClientBranch.BillingAddress, qryAdjusterClientBranch.ClientInactive, qryAdjusterClientBranch.AdjusterInactive, qryAdjusterClientBranch.Email, qryAdjusterClientBranch.EmailType, qryAdjusterClientBranch.BulkBilling, ClaimInfo2.Appraiser
ORDER BY ClaimInfo1.File_Number;

 

[mikenelena]

Maybe this will be enough to speed it up?

SELECT ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], [Service Fee]*[Qty]+[Other Charges$]+[Miles_Charged]*[per mile]+[Discount$]+[Toll_Charges] AS [Total Charges], IIf([Service Fee]<-[Discount$],0,IIf([State_Match]="WV",([Service Fee]*[Qty]+[Other Charges$]+[Discount$])*0.06,0)) AS [WV State Sales Tax], ClaimInfo2.[Service Fee], ClaimInfo2.Miles_Charged, ClaimInfo2.Miles_Traveled, ClaimInfo2.[Other Charges$], ClaimInfo2.[Other Charges Description], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.[Insured Names], ClaimInfo1.[Adjuster Name], ClaimInfo1.Invoice_Number, First(ClaimInfo1.[Invoice Notes]) AS [FirstOfInvoice Notes], ClaimInfo1.[Adjuster Name], Appraisers.email3, ClaimInfo1.Invoice_Date, ClaimInfo1.Canceled, tblItems.Service_Item, ClaimInfo2.Qty, ClaimInfo2.[Discount$], Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo2.[per mile], ClaimInfo2.[Discount Description], ClaimInfo2.Item_Code, ClaimInfo2.Toll_Charges, ClaimInfo2.[Toll Charges Description], ClaimInfo1.[Total Loss], ClaimInfo1.[Total Damages], ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.Non_Billable, ClaimInfo1.chkTransfer, qryAdjusterClientBranch.[Full Name], qryAdjusterClientBranch.AdjusterFirst, qryAdjusterClientBranch.AdjusterLast, qryAdjusterClientBranch.AdjusterSuffix, qryAdjusterClientBranch.Title, qryAdjusterClientBranch.Street, qryAdjusterClientBranch.Suite, qryAdjusterClientBranch.City, qryAdjusterClientBranch.State, qryAdjusterClientBranch.ZipCode, qryAdjusterClientBranch.BillingAddress, qryAdjusterClientBranch.ClientInactive, qryAdjusterClientBranch.AdjusterInactive, qryAdjusterClientBranch.Email, qryAdjusterClientBranch.EmailType, qryAdjusterClientBranch.BulkBilling
FROM qryAdjusterClientBranch INNER JOIN ((((ClaimInfo1 LEFT JOIN ClaimInfo2 ON ClaimInfo1.[Invoice_Number] = ClaimInfo2.[Invoice_Number]) LEFT JOIN tblItems ON ClaimInfo2.Item_Code = tblItems.Item_Code1) LEFT JOIN tblZip_State ON ClaimInfo1.Zip = tblZip_State.Loss_Zip) LEFT JOIN Appraisers ON ClaimInfo1.[Field Work Appraiser] = Appraisers.[Appraiser Name]) ON (qryAdjusterClientBranch.ClientName = ClaimInfo1.[Client Name]) AND (qryAdjusterClientBranch.[Full Name] = ClaimInfo1.[Adjuster Name])
WHERE (((ClaimInfo1.Date_Received)>Now()-100))
GROUP BY ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], [Service Fee]*[Qty]+[Other Charges$]+[Miles_Charged]*[per mile]+[Discount$]+[Toll_Charges], IIf([Service Fee]<-[Discount$],0,IIf([State_Match]="WV",([Service Fee]*[Qty]+[Other Charges$]+[Discount$])*0.06,0)), ClaimInfo2.[Service Fee], ClaimInfo2.Miles_Charged, ClaimInfo2.Miles_Traveled, ClaimInfo2.[Other Charges$], ClaimInfo2.[Other Charges Description], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.[Insured Names], ClaimInfo1.Invoice_Number, ClaimInfo1.[Adjuster Name], Appraisers.email3, ClaimInfo1.Invoice_Date, ClaimInfo1.Canceled, tblItems.Service_Item, ClaimInfo2.Qty, ClaimInfo2.[Discount$], Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo2.[per mile], ClaimInfo2.[Discount Description], ClaimInfo2.Item_Code, ClaimInfo2.Toll_Charges, ClaimInfo2.[Toll Charges Description], ClaimInfo1.[Total Loss], ClaimInfo1.[Total Damages], ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.Non_Billable, ClaimInfo1.chkTransfer, qryAdjusterClientBranch.[Full Name], qryAdjusterClientBranch.AdjusterFirst, qryAdjusterClientBranch.AdjusterLast, qryAdjusterClientBranch.AdjusterSuffix, qryAdjusterClientBranch.Title, qryAdjusterClientBranch.Street, qryAdjusterClientBranch.Suite, qryAdjusterClientBranch.City, qryAdjusterClientBranch.State, qryAdjusterClientBranch.ZipCode, qryAdjusterClientBranch.BillingAddress, qryAdjusterClientBranch.ClientInactive, qryAdjusterClientBranch.AdjusterInactive, qryAdjusterClientBranch.Email, qryAdjusterClientBranch.EmailType, qryAdjusterClientBranch.BulkBilling, ClaimInfo2.Appraiser
ORDER BY ClaimInfo1.File_Number;

Thank you JonXL. This statement shaved the query run time from 27.7 seconds to 25.21 seconds. An improvement to be sure, but not significant enough that end users would notice.

This query has to go back far enough in time to reach the date when we received the assignment. Often, that's only a few days. Our problem is that if we set the query parameter too tight, the invoice creation will fail. If we set it too loose, it takes forever to generate the invoice. I am going to try Micron's suggestion of referencing the specific date the assignment was received. Hopefully that will solve this. Thanks, and have a nice wekeend!

 

[mikenelena]

The example I gave was based on vba sql. Since you say that the long sql is in a query, you can't use Me to reference anything outside of the form (or a report. You could put a form control reference in the query though. Perhaps

HAVING (((ClaimInfo1.Date_Received)> #" & Forms!FormNameHere.ControlNameHere & "#))"

You could probably achieve this simply by entering the form control reference in the proper field in the query design grid as
Forms!FormNameHere.ControlNameHere

Micron,

This is EXACTLY what we were looking for. Invoice report print time is now down to about 5 seconds, which is a MAJOR improvement. I'd still like to see it faster, but I expect we are approaching the point of diminished returns, and I have so many other challenges ahead with this database...

Thank you again!!

 

[Micron]

We're glad to help!