Really Long SQL. Help!!!!! 3

[jordanking]

Hi there

I have a really long SQL statment. Its complex and I cannot get it into a module properly. I need help. Is there a way to break it up? It is dependent upon three other queries and is invovlved in billing so I can not have duplicates Here it is:

SELECT [InsID] & [PurID] & [ComID] AS ID, [txtName] & " " & [txtBranch] AS Company, [txtLastName] & ", " & [txtFirstName] AS Customer, [InsDate] & [PurDate] & [ComDate] AS [Date], (Nz([InsQty]))+(Nz([ComQty]))+(Nz([PurQty])) AS Qty, [PurCode] & [InsCode] & [ComCode] AS Code, qryInstalInvoice.InstValue AS InstTxt, [InsInvoice Detail] & [PurInvoice Detail] & [ComInvoice Detail] AS Detail, (Nz([InsAmount]))+(Nz([PurAmount]))+(Nz([ComAmount])) AS Amount, (Nz([InsCharge]))+(Nz([ComCharge])) AS Charge, (Nz([InsPrice]))+(Nz([ComPrice]))+(Nz([PurPrice])) AS Price, tblCustomer.idsCustomerID, tblCustomer.numInvoiceType, tblCustomer.lngzAccountStatus FROM tblCompany INNER JOIN ((((tblCustomer LEFT JOIN tblSales ON tblCustomer.idsCustomerID = tblSales.IngzCustomer) LEFT JOIN qryInstalInvoice ON tblCustomer.idsCustomerID = qryInstalInvoice.[InsCustomer ID]) LEFT JOIN qryCommercialInvoice ON tblCustomer.idsCustomerID = qryCommercialInvoice.[ComCustomer ID]) LEFT JOIN qryPurchaseInvoice ON tblCustomer.idsCustomerID = qryPurchaseInvoice.[PurCustomer ID]) ON tblCompany.idsCompanyID = tblCustomer.lngzCompanyID WHERE ((([InsDate] & [PurDate] & [ComDate]) Like "*") AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryCommercialInvoice.ComID) Not Like "*") AND ((qryPurchaseInvoice.PurID) Not Like "*")) OR ((([InsDate] & [PurDate] & [ComDate]) Like "*") AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like "*") AND ((qryPurchaseInvoice.PurID) Not Like "*")) OR ((([InsDate] & [PurDate] & [ComDate]) Like "*") AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like "*") AND ((qryCommercialInvoice.ComID) Not Like "*")) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null) AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryCommercialInvoice.ComID) Not Like "*") AND ((qryPurchaseInvoice.PurID) Not Like "*")) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null) AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like "*") AND ((qryPurchaseInvoice.PurID) Not Like "*")) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null) AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like "*") AND ((qryCommercialInvoice.ComID) Not Like "*")) ORDER BY [txtName] & " " & [txtBranch], [txtLastName] & ", " & [txtFirstName], [InsDate] & [PurDate] & [ComDate], qryInstalInvoice.InsID, qryCommercialInvoice.ComID, qryPurchaseInvoice.PurID;

I will out of the office for the evening but will check back tommorow. Thanks in advance

I appologize if I have double posted.

JK

 

[jordanking]

Sorry, Just to add,

I have built the query in Access 2003. Is there a way to call the sql stament of a query in the database as a variable?

JK

 

[PHV]

Something like this ?
strSQL = "SELECT InsID & PurID & ComID AS ID, txtName & ' ' & txtBranch AS Company" _
& ", txtLastName & ', ' & txtFirstName AS Customer, InsDate & PurDate & ComDate AS [Date]" _
& ", Nz([InsQty],0)+Nz([ComQty],0)+Nz([PurQty],0) AS Qty, PurCode & InsCode & ComCode AS Code" _
& ", I.InstValue AS InstTxt, [InsInvoice Detail] & [PurInvoice Detail] & [ComInvoice Detail] AS Detail" _
& ", Nz([InsAmount],0)+Nz([PurAmount],0)+Nz([ComAmount],0) AS Amount" _
& ", Nz([InsCharge],0)+Nz([ComCharge],0) AS Charge" _
& ",Nz([InsPrice],0)+Nz([ComPrice],0)+Nz([PurPrice],0) AS Price" _
& ", tblCustomer.idsCustomerID, tblCustomer.numInvoiceType, tblCustomer.lngzAccountStatus " _
& "FROM tblCompany INNER JOIN ((((tblCustomer" _
& " LEFT JOIN tblSales ON tblCustomer.idsCustomerID = tblSales.IngzCustomer)" _
& " LEFT JOIN qryInstalInvoice I ON tblCustomer.idsCustomerID = I.[InsCustomer ID])" _
& " LEFT JOIN qryCommercialInvoice C ON tblCustomer.idsCustomerID = C.[ComCustomer ID])" _
& " LEFT JOIN qryPurchaseInvoice P ON tblCustomer.idsCustomerID = P.[PurCustomer ID]) " _
& "ON tblCompany.idsCompanyID = tblCustomer.lngzCompanyID " _
& "WHERE tblCustomer.numInvoiceType=1 AND tblCustomer.lngzAccountStatus<3 " _
& "AND ([InsDate] & [PurDate] & [ComDate] Like '*'" _
& " OR [InsDate] & [PurDate] & [ComDate] Is Null) " _
& "AND ((C.ComID Not Like '*' AND P.PurID Not Like '*')" _
& " OR (I.InsID Not Like '*' AND P.PurID Not Like '*')" _
& " OR (I.InsID Not Like '*' AND C.ComID Not Like '*')) " _
& "ORDER BY txtName & ' ' & txtBranch, txtLastName & ', ' & txtFirstName, InsDate & PurDate & ComDate, I.InsID, C.ComID, P.PurID"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

Close, but cigar? For Ms. A. the limit for continuation lines is 16(?). You can, however build seperate sub-strings (e.g. sqlSelect; sqlFrom; sqlwhere; and sqlOrderby) and concatenate these



MichaelRed

 

[fredericofonseca]

And as for your other question here is a way of executing a Access query (with parameters) as if it was a Stored Procedure.
On the following the query has two variables and the sql is something like "select ... where a > parm1 and a < parm2"

step = 50000
incr = 5000
For i = 0 To max Step incr
    strSql = "exec UPDATE_TBLX" & i & ", " & i + incr & ""
    adoConn.Execute strSql 
    iter = 0
Next i

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[jordanking]

Thanks to all for the posts!

I took the suggestions and created the 2 following attempts:

Attempt 1:

    'Declare Variables
    Dim Rs As New ADODB.Recordset
    Dim i As Integer
    Dim j As Integer
    Dim strSQL As String
    Dim strSQLSelect As String
    Dim strSQLFrom As String
    Dim strSQLWhere As String
    Dim SQLOrder As String

    'Construct sql string
    strSQLSelect = "SELECT [InsID] & [PurID] & [ComID] AS ID, [txtName] & ' ' & [txtBranch] AS Company," _
        & " [txtLastName] & ', ' & [txtFirstName] AS Customer," _
        & " [InsDate] & [PurDate] & [ComDate] AS [Date]," _
        & " (Nz([InsQty]))+(Nz([ComQty]))+(Nz([PurQty])) AS Qty," _
        & " [PurCode] & [InsCode] & [ComCode] AS Code," _
        & " qryInstalInvoice.InstValue AS InstTxt," _
        & " [InsInvoice Detail] & [PurInvoice Detail] & [ComInvoice Detail] AS Detail," _
        & " (Nz([InsAmount]))+(Nz([PurAmount]))+(Nz([ComAmount])) AS Amount," _
        & " (Nz([InsCharge]))+(Nz([ComCharge])) AS Charge," _
        & " (Nz([InsPrice]))+(Nz([ComPrice]))+(Nz([PurPrice])) AS Price," _
        & " tblCustomer.idsCustomerID, tblCustomer.numInvoiceType, tblCustomer.lngzAccountStatus" _

    strSQLFrom = " FROM tblCompany INNER JOIN ((((tblCustomer LEFT JOIN tblSales ON tblCustomer.idsCustomerID = tblSales.IngzCustomer)" _
        & " LEFT JOIN qryInstalInvoice ON tblCustomer.idsCustomerID = qryInstalInvoice.[InsCustomer ID])" _
        & " LEFT JOIN qryCommercialInvoice ON tblCustomer.idsCustomerID = qryCommercialInvoice.[ComCustomer ID])" _
        & " LEFT JOIN qryPurchaseInvoice ON tblCustomer.idsCustomerID = qryPurchaseInvoice.[PurCustomer ID]) ON tblCompany.idsCompanyID = tblCustomer.lngzCompanyID" _

    strSQLWhere = " WHERE ((([InsDate] & [PurDate] & [ComDate]) Like '*')" _
        & " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryCommercialInvoice.ComID) Not Like '*')" _
        & " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Like '*')" _
        & " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')" _
        & " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Like '*')" _
        & " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')" _
        & " AND ((qryCommercialInvoice.ComID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null)" _
        & " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryCommercialInvoice.ComID) Not Like '*')" _
        & " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null)" _
        & " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')" _
        & " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null)" _
        & " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')" _
        & " AND ((qryCommercialInvoice.ComID) Not Like '*'))" _

    strSQLOrder = " ORDER BY [txtName] & ' ' & [txtBranch], [txtLastName] & ', ' & [txtFirstName], [InsDate] & [PurDate] & [ComDate], qryInstalInvoice.InsID, qryCommercialInvoice.ComID, qryPurchaseInvoice.PurID;" _

    strSQL = strSQLSelect & strSQLFrom & strSQLWhere & strSQLOrder

    'Open the recordset
    Rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    j = Rs.RecordCount
    Debug.Print Rs.RecordCount

    For i = 1 To j
        'Display Query Fields
        Me!lblRecNum.Caption = i & " / " & Rs.RecordCount
        Me!lblBranch.Caption = Rs!Company
        Me!lblName.Caption = Rs!Customer
        'Display Percent Done
        Me!lblPercent.Caption = Math.Round(Nz((i / j) * (100), 0)) & "%"
        'Move to Next Record
        'Rs.MoveNext
    Next
    
    'Close RecordSet
    Rs.Close
    'Empty Recordset Variable
    Set Rs = Nothing

Attempt 2:

    'Declare Variables
    Dim Rs As New ADODB.Recordset
    Dim i As Integer
    Dim j As Integer
    Dim strSQL As String
    Dim str1 As String
    Dim str2 As String
    Dim str3 As String
    Dim str4 As String
    Dim str5 As String
    Dim str6 As String
    Dim str7 As String
    Dim str8 As String
    Dim str9 As String
    Dim str10 As String
    Dim str11 As String
    Dim str12 As String
    Dim str13 As String
    Dim str14 As String
    Dim str15 As String
    Dim str16 As String
    Dim str17 As String
    Dim str18 As String
    Dim str19 As String
    Dim str20 As String
    Dim str21 As String
    Dim str22 As String
    Dim str23 As String
    Dim str24 As String
    Dim str25 As String
    Dim str26 As String
    Dim str27 As String
    Dim str28 As String
    Dim str29 As String

    'Construct sql string
    str1 = "SELECT [InsID] & [PurID] & [ComID] AS ID, [txtName] & ' ' & [txtBranch] AS Company,"
    str2 = " [txtLastName] & ', ' & [txtFirstName] AS Customer,"
    str3 = " [InsDate] & [PurDate] & [ComDate] AS [Date],"
    str4 = " (Nz([InsQty]))+(Nz([ComQty]))+(Nz([PurQty])) AS Qty,"
    str5 = " [PurCode] & [InsCode] & [ComCode] AS Code,"
    str6 = " qryInstalInvoice.InstValue AS InstTxt,"
    str7 = " [InsInvoice Detail] & [PurInvoice Detail] & [ComInvoice Detail] AS Detail,"
    str8 = " (Nz([InsAmount]))+(Nz([PurAmount]))+(Nz([ComAmount])) AS Amount,"
    str9 = " (Nz([InsCharge]))+(Nz([ComCharge])) AS Charge,"
    str10 = " (Nz([InsPrice]))+(Nz([ComPrice]))+(Nz([PurPrice])) AS Price,"
    str11 = " tblCustomer.idsCustomerID, tblCustomer.numInvoiceType, tblCustomer.lngzAccountStatus"
    str12 = " FROM tblCompany INNER JOIN ((((tblCustomer LEFT JOIN tblSales ON tblCustomer.idsCustomerID = tblSales.IngzCustomer)"
    str13 = " LEFT JOIN qryInstalInvoice ON tblCustomer.idsCustomerID = qryInstalInvoice.[InsCustomer ID])"
    str14 = " LEFT JOIN qryCommercialInvoice ON tblCustomer.idsCustomerID = qryCommercialInvoice.[ComCustomer ID])"
    str15 = " LEFT JOIN qryPurchaseInvoice ON tblCustomer.idsCustomerID = qryPurchaseInvoice.[PurCustomer ID]) ON tblCompany.idsCompanyID = tblCustomer.lngzCompanyID"
    str16 = " WHERE ((([InsDate] & [PurDate] & [ComDate]) Like '*')"
    str17 = " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryCommercialInvoice.ComID) Not Like '*')"
    str18 = " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Like '*')"
    str19 = " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')"
    str20 = " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Like '*')"
    str21 = " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')"
    str22 = " AND ((qryCommercialInvoice.ComID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null)"
    str23 = " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryCommercialInvoice.ComID) Not Like '*')"
    str24 = " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null)"
    str25 = " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')"
    str26 = " AND ((qryPurchaseInvoice.PurID) Not Like '*')) OR ((([InsDate] & [PurDate] & [ComDate]) Is Null)"
    str27 = " AND ((tblCustomer.numInvoiceType)=1) AND ((tblCustomer.lngzAccountStatus)<3) AND ((qryInstalInvoice.InsID) Not Like '*')"
    str28 = " AND ((qryCommercialInvoice.ComID) Not Like '*'))"
    str29 = " ORDER BY [txtName] & ' ' & [txtBranch], [txtLastName] & ', ' & [txtFirstName], [InsDate] & [PurDate] & [ComDate], qryInstalInvoice.InsID, qryCommercialInvoice.ComID, qryPurchaseInvoice.PurID;"

    strSQL = str1 & str2 & str3 & str4 & str5 & str6 & str7 & str8 & str9 & str10 & str11 & str12 & str13 & str14 & str15 & str16 & str17 & str18 & str19 & str20 & str21 & str22 & str23 & str24 & str25 & str26 & str27 & str28 & str29

    'Open the recordset
    Rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    j = Rs.RecordCount
    Debug.Print Rs.RecordCount

    For i = 1 To j
        'Display Query Fields
        Me!lblRecNum.Caption = i & " / " & Rs.RecordCount
        Me!lblBranch.Caption = Rs!Company
        Me!lblName.Caption = Rs!Customer
        'Display Percent Done
        Me!lblPercent.Caption = Math.Round(Nz((i / j) * (100), 0)) & "%"
        'Move to Next Record
        'Rs.MoveNext
    Next
    
    'Close RecordSet
    Rs.Close
    'Empty Recordset Variable
    Set Rs = Nothing

Both of these return no errors. But the debug.print rs.recordcount returns "0" for both. So the recordset is empty but i know the sql works because it is working within access 2003. The lack of errors is frustrating because I have no idea where to begin. I did get one error while creating that mentioned an improper join but I cannot seem to reproduce it. Any more Ideas?

 

[jordanking]

Hello again

I have some more info through playing with simple queries. I appologize if this is obvious but I have only been building queries in the access 2003 design view.

When I try to run the following as the SQL string:

    str1 = "SELECT tblDriver.DriverId, tblDriver.DriverName AS Name1 FROM tblDriver WHERE (((tblDriver.DriverName) Like ' * '));"

it does not work

but if i remove the where clause

    str1 = "SELECT tblDriver.DriverId, tblDriver.DriverName AS Name1 FROM tblDriver;"

it does work

It appears as if I can not replace the double quotes with single quotes.
Is there a way to work with double quotes within strings. Is this my problem?

JK

 

[PHV]

I think that in ADO you have to use % instead of *

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

Thanks PHV, you sent me in the right direction,

I found a msdn page the told me to reformat my query:

str1 = "SELECT tblDriver.DriverId, tblDriver.DriverName AS Name1 FROM tblDriver WHERE tblDriver.DriverName Like "" % "";"

however, it did not work. I tried using single quotes, double quotes, and then escapeing both single and double quotes by doubling them up (as seen above). Can you see anything I am doing wrong?

 

[PHV]

Are you sure you have values in DriverName with leading and trailing space ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

Thanks PHV

you were right, I have no values with leading and trailing spaces. I did not mean to insert those but was also unable to recognize them. It works now. I did not know about the wildcard changes from DAO to ADO though. That explains a lot. Thanks again, stars aplenty

JK