Sql Statement to filter range.

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

Re: Sql Statement to filter range.

Post by HansV »

CONTAINS is not valid in Access SQL. Use

Code: Select all

[Gender] Like '*Female*'
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've tried your suggetsuon. But it does not seem to worl. I have attached the file for reference.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by HansV »

Sorry about that. Access uses * as wildcard character (just like Excel and VBA), but ADODB expects %. So use

Code: Select all

[Gender] Like '%Female%'
Best wishes,
Hans

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

Re: Sql Statement to filter range.

Post by adam »

when I use the above line for males as

Code: Select all

[Gender] Like '%Male%'
it does give me female values too. Why is that?
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by HansV »

Because SQL is case-insensitive. The word "Female" contains "male".
Why don't you use

[Gender]='Male'

and

[Gender]='Female'
Best wishes,
Hans

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

Re: Sql Statement to filter range.

Post by adam »

Thankyou Hans. It worked very well.
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by adam »

Instead of using the range Set rngMyRange = wss.Range("A1:A85") I'm trying to upgrade the code so that it would filter values from the Access table "Ptable" based on values from the Access table "Criteria" column "Product Result".

Here's the new query that works to filter the values

Code: Select all

  strSQL = "select [Product Name] from Ptable where [Product Name] in (select [Product Result] from Criteria)"
    rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
    ws.Range("B5").CopyFromRecordset rst
I want to embbed the above code to the following code so that it would filter the data that is in "Ptable" based on values in table "criteria".

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
Here is how I have embedded. But I'm getting error message "no value given for required parameter"

Code: Select all

    strSQL = "TRANSFORM Count([Product Name]) AS total SELECT [Product Name] " & _
        "FROM Ptable WHERE [Product Name] IN (select [Product Result] from Criteria) 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)"
How could I overcome this? Any help would be kidnly appreciated. Thanks in advance.
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by HansV »

Are all field and table names correct? In a quick test, it appeared to work correctly.
Best wishes,
Hans

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

Re: Sql Statement to filter range.

Post by adam »

Sorry, I missed the field Year. After writing the year. The code worked fine. Thanks for the help.
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by adam »

To fetch data by month name I did replaced "month" by "monthname" in the hope that the query would collect data when the user writes month name instead of month number. But this change didn't work.

What could be a better way to do this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
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

 ... AND Format([Sale Date],'mmmm')='" & _
        ws.Range("O1").Value & "' AND ...
Best wishes,
Hans

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Sql Statement to filter range.

Post by p45cal »

I've been looking at this thread for my own edification and also perhaps to suggest using Power Query which would probably need no vba code at all.
When trying to execute the macro (after extracting the most recent .rar file) I got the error that the Access database engine couldn't find the query/table 'Criteria'. When I look at the Access database I can only see PTable with only 3 entries. Should there be a Criteria table in there too?
I'm not very familiar with Access (I was only doing this to find out what result you wanted and to design the same output from Power Query).
Can one of you fill me in a bit?

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

Re: Sql Statement to filter range.

Post by adam »

Yes. There needs to be a criteria table too.
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by adam »

Instead of copying Product Name from Ptable I'm trying to copy an alternative field called Product Category with the other columns and ranges of sql query statement unchanged.

I tried replacing the following part

AS total SELECT [Product Name] " & _
"FROM Ptable WHERE [Product Name]

With;

AS total SELECT [Product Category] " & _
"FROM Ptable WHERE [Product Name]

But I keep getting error message. What could be the reason for this Hans?
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by HansV »

You should of course also change

GROUP BY [Product Name]

to

GROUP BY [Product Category]
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 Hans. Worked very well. Appreciate a lot.
Best Regards,
Adam

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Sql Statement to filter range.

Post by p45cal »

adam wrote:
02 Nov 2020, 14:43
Yes. There needs to be a criteria table too.
without which I can do nothing at all.

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 sum up and filter the records for the month that are less than 12 Years and where the gender is 'Male'. Here is how I'm trying to achieve this.

Code: Select all

        strSQL = "TRANSFORM Count([Gender]) AS total SELECT [Gender] " & _
            "FROM tblmytable WHERE [Gender]='Male' AND[Age]='<12 Years' AND Format([CDate],'mmmm')='" & _
            ws.Range("AE9").Value & "'AND YEAR([CDate])=" & ws.Range("AI9").Value & _
            " GROUP BY [Gender] PIVOT Day([CDate]) 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("C16").CopyFromRecordset rst
        rst.Close
However I'm not getting any error and yet the code is not working. What am I doing wrong here?
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by HansV »

What kind of field is Age? A text field?
Best wishes,
Hans

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

Re: Sql Statement to filter range.

Post by adam »

its a text field.
Best Regards,
Adam