Sql Statement to filter range.

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

Re: Sql Statement to filter range.

Post by HansV »

Then it is not suitable for your criteria. It should be a number field.
Regards,
Hans

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

Re: Sql Statement to filter range.

Post by adam »

Ok. I'll change it to a number field.
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by adam »

Here's how I've modified the code. But I'm getting an error message saying "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect". What am I doing wrong in here?

Code: Select all

        strSQL = "SELECT COUNT([Gender]) AS total SELECT [Gender] FROM tblmytable WHERE [Gender] = 'Female' AND [PAge] <5 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("D23").CopyFromRecordset rst
        rst.Close
Best Regards,
Adam

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

Re: Sql Statement to filter range.

Post by HansV »

The first SELECT should be TRANSFORM (you had that in the previous version). You now have two SELECTs in the SQL.
Regards,
Hans

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

Re: Sql Statement to filter range.

Post by adam »

Thanks so much for the help Hans.
Best Regards,
Adam