SQL nesting

S

Swifey

Active Member
Joined
Jan 16, 2003
Messages
421
I'm sorry to bother you again - As before I'm trying to create 12 input boxes for the user to type a single or multiple customerno. I tried looking at your SQL nesting code from my previous request, but I dont know what your code is doing so that I can modify my current SQL. My current SQL code is as follows:

Code: 
SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, [dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod
FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN [dbo_tCustomer pda] ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno
GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod
HAVING ((([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto1] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto2] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto3] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto4] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto5] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto6] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto7] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto8] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto9] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto10] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto11] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto12]) AND (([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month]));

Could you please help.
Swifey
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi, I missed this the first time around but my guess is that this statement right at the end...
Code: 
AND (([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month]));
...is the cause of the problem.
If [cstart] and [month] are not both date fields, the query will spit the dummy. You may find that having 2 date fields ([cstart] and [cfinish]) will do the trick. Even if you just want users to pick the finishing month, you'll need to make [cfinish] a hidden field on the form that calculates the last day of the selected month, adn then use that in the query.

HTH

Denis
 
Upvote 0
Good morning thank you for getting back to me. My problem is a user may have just one customerno to key in therefore the query fails they have to key the same customerno into all the 12 input boxes.
 
Upvote 0
OK, if the issue is that sometimes you don't have all of the textboxes selected, you will need something more flexible. A ListBox with multi-select capability is the cleanest way to go -- the user can select as many customers as they want, and you aren't limited to 12.
I created something similar for a client who needed to create a report from as many or few items as they needed at the time.

Here's an adaptation of what I did:
1. Create a list box with multi-select enabled (check in the Properties for the ListBox -- I have called the ListBox lstCustomer)
2. On clicking the Preview button, the record IDs were copied into a SQL string which was used to replace a query -- (a) Create SQL (b) Delete existing QueryDef (c) Create new QueryDef using the same name, with the SQL string as the code for the QueryDef
3. Open the required report, which had the QueryDef as the RecordSource.

This code was written for Access 97 so it's DAO, but it might help:

Code: 
Private Sub cmdPreview_Click()
  Dim strName As String
  Dim strList As String
  Dim strFilterText As String
  Dim varItem As Variant
  Dim strQuote
  Dim strSQL as String
  Dim qdf as DAO.QueryDef
  Dim dbs As DAO.Database
  
  Set dbs = CurrentDb()
  strQuote = Chr$(34)
  strList = ""
  
  With Me!lstCustomer
    For Each varItem In .ItemsSelected
      strName = strQuote & .Column(0, varItem) & strQuote & ","
      strList = strList & strName
    Next
    
    strFilterText = Left(strList, Len(strList) - 1)
  End With
[txtList] = strFilterText

SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, [dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod 
FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN [dbo_tCustomer pda] ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno 
GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod 
HAVING ([dbo_tConsolSales PDA].customerno In " & strFilterText & "));"

dbs.QueryDefs.Delete "qryCustomerFilter"
Set qdf = dbs.CreateQueryDef("qryCustomerFilter", strSQL)
DoCmd.OpenQuery "qryCustomerFilter"

End Sub
As you can see from the name of the Sub, it's triggered by clicking a button called Preview.
HTH

Denis
 
Upvote 0
Hi Denis
1. I created a Listbox with the following properties on my form
Name: 1stCustomer
Control Source: Blank
Row Source Type: Value list
Row source: Blank
Multi select: Unabled

2. I the created my query
3. I then selected SQL view
4. I then pasted in your code
5 when I tried to return back to the query i received the following message
"Invalid SQL statement: expected DELET,INSERT,PROCEDURE,SELECT, OR UPDATE
Here is what I copied into the SQL view.

Code: 
Private Sub cmdPreview_Click() 
  Dim strName As String 
  Dim strList As String 
  Dim strFilterText As String 
  Dim varItem As Variant 
  Dim strQuote 
  Dim strSQL as String 
  Dim qdf as DAO.QueryDef 
  Dim dbs As DAO.Database 
  
  Set dbs = CurrentDb() 
  strQuote = Chr$(34) 
  strList = "" 
  
  With Me!lstCustomer 
    For Each varItem In .ItemsSelected 
      strName = strQuote & .Column(0, varItem) & strQuote & "," 
      strList = strList & strName 
    Next 
    
    strFilterText = Left(strList, Len(strList) - 1) 
  End With 
[txtList] = strFilterText 

SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, [dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod 
FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN [dbo_tCustomer pda] ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno 
GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod 
HAVING ([dbo_tConsolSales PDA].customerno In " & strFilterText & "));" 
AND (([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month])); 

dbs.QueryDefs.Delete "qryCustomerFilter" 
Set qdf = dbs.CreateQueryDef("qryCustomerFilter", strSQL) 
DoCmd.OpenQuery "qryCustomerFilter" 

End Sub

Can you advise me as to where I am going wrong.

Regards
Swifey
 
Upvote 0
Hi Brian its me again
1. Ive just created a module for the macro code
2. Amended SQL code as follows
Code: 
SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, [dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod 
FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN [dbo_tCustomer pda] ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno 
GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod 
HAVING ([dbo_tConsolSales PDA].customerno In " & strFilterText & "));" 
AND ((([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month]));
I get the following message when I save
"In operator without()in query expression "([dbo_tConsolSales PDA].customerno In " & strFilterText & "));"

Or have I totally lost the plot

Regards
 
Upvote 0
I think that you have lost the plot :)
The code that Dennis offered you is run from a module and it creates the Query for you. It uses the info from the list box to create the SQL string then deletes the existing query and creates it afresh using the new SQL string.

I have tweaked Dennis's code slightly for you to try. after you run it you need to check the SQL in the Query that it generates to see if it is what you are after.

HTH

Peter

Code: 
Private Sub cmdPreview_Click()
  Dim strName As String
  Dim strList As String
  Dim strFilterText As String
  Dim varItem As Variant
  Dim strQuote
  Dim strSQL As String
  Dim qdf As DAO.QueryDef
  Dim dbs As DAO.Database
  
  Set dbs = CurrentDb()
  strQuote = Chr$(34)
  strList = ""
  
  With Me!lstCustomer
    For Each varItem In .ItemsSelected
      strName = strQuote & .Column(0, varItem) & strQuote & ","
      strList = strList & strName
    Next
    strFilterText = Left(strList, Len(strList) - 1)
  End With
'[txtList] = strFilterText

strSQL = "SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, "
strSQL = strSQL & "dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, "
strSQL = strSQL & "[dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod "
strSQL = strSQL & "FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN "
strSQL = strSQL & "[dbo_tCustomer pda] "
strSQL = strSQL & "ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno "
strSQL = strSQL & "GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, "
strSQL = strSQL & "dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod "
strSQL = strSQL & "HAVING ([dbo_tConsolSales PDA].customerno In (" & strFilterText & ")) "
strSQL = strSQL & "AND (([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month]));"

dbs.QueryDefs.Delete "qryCustomerFilter"
Set qdf = dbs.CreateQueryDef("qryCustomerFilter", strSQL)

DoCmd.OpenQuery "qryCustomerFilter"
Set qdf = Nothing
Set dbs = Nothing
End Sub
 
Upvote 0
Dare I say it - Its me again when I run your code I get a compile error: User-defined type not defined at Dim qdf As DAO.QueryDef

Code: 
rivate Sub cmdPreview_Click()
  Dim strName As String
  Dim strList As String
  Dim strFilterText As String
  Dim varItem As Variant
  Dim strQuote
  Dim strSQL As String
  Dim qdf As DAO.QueryDef
  Dim dbs As DAO.Database
  
  Set dbs = CurrentDb()
  strQuote = Chr$(34)
  strList = ""
  
  With Me!lstCustomer
    For Each varItem In .ItemsSelected
      strName = strQuote & .Column(0, varItem) & strQuote & ","
      strList = strList & strName
    Next
    strFilterText = Left(strList, Len(strList) - 1)
  End With
'[txtList] = strFilterText

strSQL = "SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, "
strSQL = strSQL & "dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, "
strSQL = strSQL & "[dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod "
strSQL = strSQL & "FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN "
strSQL = strSQL & "[dbo_tCustomer pda] "
strSQL = strSQL & "ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno "
strSQL = strSQL & "GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, "
strSQL = strSQL & "dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod "
strSQL = strSQL & "HAVING ([dbo_tConsolSales PDA].customerno In (" & strFilterText & ")) "
strSQL = strSQL & "AND (([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month]));"

dbs.QueryDefs.Delete "qryCustomerFilter"
Set qdf = dbs.CreateQueryDef("qryCustomerFilter", strSQL)

DoCmd.OpenQuery "qryCustomerFilter"
Set qdf = Nothing
Set dbs = Nothing
End Sub
 
Upvote 0
Dare I say it - Its me again when I run your code I get a compile error: User-defined type not defined at Dim qdf As DAO.QueryDef

Code: 
rivate Sub cmdPreview_Click()
  Dim strName As String
  Dim strList As String
  Dim strFilterText As String
  Dim varItem As Variant
  Dim strQuote
  Dim strSQL As String
  Dim qdf As DAO.QueryDef
  Dim dbs As DAO.Database
  
  Set dbs = CurrentDb()
  strQuote = Chr$(34)
  strList = ""
  
  With Me!lstCustomer
    For Each varItem In .ItemsSelected
      strName = strQuote & .Column(0, varItem) & strQuote & ","
      strList = strList & strName
    Next
    strFilterText = Left(strList, Len(strList) - 1)
  End With
'[txtList] = strFilterText

strSQL = "SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, "
strSQL = strSQL & "dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, "
strSQL = strSQL & "[dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod "
strSQL = strSQL & "FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN "
strSQL = strSQL & "[dbo_tCustomer pda] "
strSQL = strSQL & "ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno "
strSQL = strSQL & "GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, "
strSQL = strSQL & "dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod "
strSQL = strSQL & "HAVING ([dbo_tConsolSales PDA].customerno In (" & strFilterText & ")) "
strSQL = strSQL & "AND (([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month]));"

dbs.QueryDefs.Delete "qryCustomerFilter"
Set qdf = dbs.CreateQueryDef("qryCustomerFilter", strSQL)

DoCmd.OpenQuery "qryCustomerFilter"
Set qdf = Nothing
Set dbs = Nothing
End Sub
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,221,561
Messages
6,160,495
Members
451,653
Latest member
agata

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top