Sql Statement to filter range.

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Sql Statement to filter range.

Post by adam »

I'm getting a type mismatch error when im trying to filter and copy the range in column A from access table with the following code. How could I solve this?

Code: Select all

Sub Filter()
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim strSQL      As String
    Dim ws          As Worksheet
    Dim wss         As Worksheet
    Dim rngMyRange     As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set ws = Worksheets("results")
    Set wss = Worksheets("Criteria")
    
    Set rngMyRange = wss.Range("A1:A85")
    
    ws.Range("A5:G1048576").ClearContents
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\IL\Database.accdb;"
    
    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT [Product Name],[Product Category],[Location],[Price] FROM Ptable WHERE [Product Name]=" & rngMyRange
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    ws.Range("D5").CopyFromRecordset rst
    rst.Close
    
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Assuming that Product Name is a text field, you should enclose the value in (single or double) quotes:

Code: Select all

    strSQL = "SELECT [Product Name],[Product Category],[Location],[Price] FROM Ptable WHERE [Product Name]='" & rngMyRange & "'"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

I'm still getting the typemismatch error.

The code works if the line

Code: Select all

wss.Range("A1:A85")
is changed to one cell.

for example; wss.Range("A1:A1").

How to change the range so the code filters all the text in column A of the sheet wss.
Last edited by adam on 02 Sep 2020, 08:40, edited 1 time in total.
Best Regards,
Adam

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Sql Statement to filter range.

Post by rory »

You can't use = with a multi-cell range. You would need to build an IN clause, or use an additional SELECT clause.
Regards,
Rory

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Code: Select all

strSQL = "SELECT [Product Name],[Product Category],[Location],[Price] FROM Ptable WHERE [Product Name] IN" & rngMyRange & ";"
I'm still getting type mismatch error.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

It won't work like that, you have to build the string in a loop:

Code: Select all

Sub Filter()
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim strIn       As String
    Dim strSQL      As String
    Dim ws          As Worksheet
    Dim wss         As Worksheet
    Dim rngMyRange  As Range
    Dim rngCell     As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set ws = Worksheets("results")
    Set wss = Worksheets("Criteria")
    
    Set rngMyRange = wss.Range("A1:A85")
    
    ws.Range("A5:G1048576").ClearContents
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\IL\Database.accdb;"
    
    Set rst = New ADODB.Recordset
    
    For Each rngCell In rngMyRange
        strIn = strIn & ",'" & rngCell.Value & "'"
    Next rngCell
    
    strSQL = "SELECT [Product Name],[Product Category],[Location],[Price] FROM Ptable WHERE [Product Name] IN (" & _
        Mid(strIn, 2) & ")"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    ws.Range("D5").CopyFromRecordset rst
    rst.Close
    
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Thank you very much for the help Hans. If I may ask one more help;

I have a second SQL Statement that filters products from the same table by the selected year and month in range N1 and O1. The statement is as follows;

Code: Select all

strSQL = "SELECT [Sale Date] FROM Ptable WHERE MONTH([Sale Date])='" & ws.Range("O1").Value & "' AND YEAR([Sale Date]) = " & ws.Range("N1").Value & ""
   rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
   ws.Range("A5").CopyFromRecordset rst
Can it be possible to combine these two statements as one so that the final compilation would?

1. First filter the data by year and month.
2. And then Filter the product name for that year and month.

Following is how I have changed your statement by adding extra columns.

Code: Select all

strSQL = "SELECT [Sale No],[Sale Category],[Product Name],[Product Category],[Location],[Price] FROM Ptable WHERE [Product Name] IN (" & _
    Mid(strIn, 2) & ")"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    ws.Range("B5").CopyFromRecordset rst
    rst.Close
Following are my access tables column headers I want to be copied to excel sheet when both the Sql statements are combined.

[Sale Date], [Sale No], [Sale Category], [Product Name], [Product Category], [Location], [Price]

Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Try this SQL:

Code: Select all

    strSQL = "SELECT [Sale Date],[Sale No],[Sale Category],[Product Name],[Product Category],[Location],[Price] " & _
        "FROM Ptable WHERE [Product Name] IN (" & Mid(strIn, 2) & ") AND MONTH([Sale Date])=" & _
        ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Thank you very much for the help Hans. It works perfect.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

I’m trying to combine the above query with the following query

Code: Select all

strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] FROM Ptable GROUP BY [Product Name] PIVOT [Sale Date];"
so that the final query would?
1. First, filter Product Names in rngMyRange based on the range in O1 and N1.
2. The code would then group copied data by product name
3. And display each day of the month in columns so that a statistical report in displayed for the products.

Here's how I have combined them

Code: Select all

         strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] " & _
        "FROM Ptable GROUP BY [Product Name] PIVOT [Sale Date] WHERE [Product Name] IN (" & Mid(strIn, 2) & ") AND MONTH([Sale Date])=" & _
        ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value
But Im getting the error message "Syntax error (missing operator) in query expression ‘[Sale Date] WHERE [Product Name]’."

any help on this woudl be kindly appreciated.

Thanks in advance.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Try

Code: Select all

         strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] " & _
        "FROM Ptable GROUP BY [Product Name] WHERE [Product Name] IN (" & Mid(strIn, 2) & ") AND MONTH([Sale Date])=" & _
        ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value & " PIVOT [Sale Date]"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Still I'm getting the error in the image?
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Why are all those empty strings in the IN (...) part?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

May be because of the range Set rngMyRange = wss.Range("A1:A85")?

In that range I'm having only four rows of data as orange, apple and so on?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Try restricting rngMyRange to only the filled rows.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

I have attached the workbook for your reference. Im still getting the error.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Code: Select all

         strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] " & _
        "FROM Ptable WHERE [Product Name] IN (" & Mid(strIn, 2) & ") AND MONTH([Sale Date])=" & _
        ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value & " GROUP BY [Product Name] PIVOT [Sale Date]"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Thanks a lot for the help Hans. It worked fine.

In the meantime, I have numbers from 1 to 31 in row 4 (Columns C to AG). Is there a possibility to make the sql query to copy the sum of products to the appropriate date column?

For example;
The sum of the products for 1/3/2020 will get copied to row 5 of column containing 1.
The sum of the products for 7/3/2020 will get copied to row 5 of column containing 7.
At present the code copies data to next empty column.

I hope I’ve made the question clear.
If this is not possible please let me another option of how I could do this.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78242
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Use

Code: Select all

    strSQL = "TRANSFORM Count([Product Name]) AS total SELECT [Product Name] " & _
        "FROM Ptable WHERE [Product Name] IN (" & Mid(strIn, 2) & ") AND MONTH([Sale Date])=" & _
        ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value & _
        " GROUP BY [Product Name] PIVOT Day([Sale Date]) In " & _
        "(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)"
    
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    ws.Range("B5").CopyFromRecordset rst
    rst.Close
(The rest of the code is unchanged)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Thanks for the help Hans. Using the query you have provided, Im trying to filter text from column Gender based on location. However Im unable to do that.

What am I doing wrong here? Here's the query

Code: Select all

        strSQL = "TRANSFORM Count([Location]) AS total SELECT [Location] " & _
            "FROM Ptable WHERE [Gender] CONTAINS 'Female' AND MONTH([Sale Date])=" & _
            ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value & _
            " GROUP BY [Location] PIVOT Day([Sale Date]) In " & _
            "(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)"
        rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
        ws.Range("C30").CopyFromRecordset rst
        rst.Close
Best Regards,
Adam