SQL nesting

[Swifey]

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:

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

 

[bat17]

What version of Access are you using? I checked the "complie" in A97 and it was OK there.

Peter

 

[SydneyGeek]

Are you running Access 97 or 2000?
If 97, you can drop the DAO bits. If 2000, you will need them. So...

97 Code snippet

  Dim qdf As QueryDef 
  Dim dbs As Database

2000 Code snippet

  Dim qdf As DAO.QueryDef 
  Dim dbs As DAO.Database

 

[SydneyGeek]

Another thought re the SQL problem -- You might find it easier to split this into 2 separate queries. The first could use the ListBox idea that I mentioned, to create the filtered data set. The second could then use this query to give the grouped and totalled result. The code will be easier to work with and the SQL string will not be quite so huge.

The result would be something like this:
First query -- generated new each time you click the Preview button

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, [dbo_tConsolSales PDA].quantity, " 
strSQL = strSQL & "[dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].linesellvalue, [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 & "WHERE [dbo_tConsolSales PDA] In (" & strFilterText & ") ;"
dbs.QueryDefs.Delete "qryCustomerFilter" 
Set qdf = dbs.CreateQueryDef("qryCustomerFilter", strSQL)

You'll need to create a second query that uses qryCustomerFilter as its data source. This is the one where you group and sum the data. For the purposes of this discussion, let's call it qryCustomerTotals

To run the query you can use this code at the end of the stuff above:
DoCmd.OpenQuery("qryCustomerTotals")
Then finish with
End Sub

HTH

Denis

 

[Swifey]

Thank you for your time and patience I’ll try and put your idea into practice.

 

[Swifey]

Sorry its me again I'm using Access 2000

1. I created a new module
2. I then inserted your code
3. I then saved the madule as "Preview"
4 I the pressed the continue button

The system generated the following message "error: User-defined type not defined at Dim qdf As DAO.QueryDef"

I am sorry
Swifey

 

[SydneyGeek]

Try changing DAO.Querydef to just Querydef

I wasn't in front of Access when I modified the code in the post, so DAO may not be required for that particular definition.

Good luck

Denis

 

[Swifey]

Thanks Denis

 

[Swifey]

Sorry..... I changed DAO.Querydef to just Querydef

I then pressed the continue button

The system generated the following message "compile error:
user-defined type not defined"

 

[SydneyGeek]

One more thing you can try. Access 2000 will, by default, use ADO before it uses DAO. They are different object models. You can get Access to use DAO first, by doing the following:

Open any code module
Tools | References
Find the reference to ADO. My bet is that it is higher up the list than the DAO reference. If so, uncheck the ADO reference and close the window.
Now re-open Tools | References, find the ADO reference (it will be some way down the list) and check it again. Close the window.
If the code works now, you're in business. Otherwise we'll have to try another trick or two.

HTH

Denis

 

[Swifey]

I beleive you are winning
I've changed the Refrences Hierachy but.......
I now get a system message "compile error: Invalid use of Me Keyword"

Regards