How to Select second Recordset from first recordset

[FrustratedAlso]

I am trying to count distinct Month/Year Combinations in a Sales File. I can't seem to figure out how to select the count.


Here are two things I tried, both give me error 3061: too few parameters expected 1. Suggestions???

strSql1 = "(SELECT Distinct (Month(Sales.[SaleDate])),Year(Sales.[SaleDate]) FROM Sales WHERE [Sales.Venue]= [OutLet] )"
    Set SalRec1 = DBEngine(0)(0).OpenRecordset(strSql1, dbOpenDynaset)
    strSql2 = "(SELECT Count([Month]) FROM SalRec1"
    Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

strSql2 = "(SELECT Count([Month]) FROM (SELECT Distinct (Month(Sales.[SaleDate])),Year(Sales.[SaleDate]) FROM Sales WHERE [Sales.Venue]= [OutLet] )"
 Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

[AccessMSSQL]

Copy your SQL Statement into a query designer and you should be able to see what the problem is.

[AccessMSSQL]

Also, check fieldnames spelling. A mispelled fieldname can cause that error.
I see some issues with 2nd code example. You are Counting the field [Month] from your select statement but you don't have a Month field defined. Try Count(*).

[ssanfu]

Be aware that "Month" is a reserved word in Access, as are "Year", "Day" and 'Date". Here is a list of reserved words - http://allenbrowne.com/AppIssueBadWord.html

You could try:

strSql1 = "SELECT Distinct Month(Sales.[SaleDate]) AS SalesMonth, Year(Sales.[SaleDate]) AS SalesYear FROM Sales WHERE [Sales.Venue]= [OutLet] "
    Set SalRec1 = DBEngine(0)(0).OpenRecordset(strSql1, dbOpenDynaset)
    strSql2 = "SELECT Count([SalesMonth]) FROM SalRec1"
    Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

Is "[OutLet]" a control on a form? You might have to concantate it to the string. And if it is text, you will have to use delimiters.

[FrustratedAlso]

This query works

SELECT Count([sMth]) AS Expr1
FROM (SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] =[OutLet]) AS TempTbl;

Can't seem to translate to Vb

These are two different Methods with their accompanying errors

Attachment 7227

Dim SalRec2 As DAO.Recordset     'Related records
Dim strSql2 As String            'SQL statement
Dim strOut As Variant           'Output string For Count.

strSql2 = "SELECT Count([sMth]) FROM (SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "')"
    Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

Attachment 7226

    Dim SalRec1 As DAO.Recordset     'Related records
    Dim SalRec2 As DAO.Recordset     'Related records
    Dim strSql1 As String            'SQL statement
    Dim strSql2 As String            'SQL statement
    Dim strOut As Variant           'Output string For Count.


strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
    Set SalRec1 = DBEngine(0)(0).OpenRecordset(strSql1, dbOpenDynaset)
    strSql2 = "SELECT Count([sMth]) FROM SalRec1"
Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

[AccessMSSQL]

I was just looking at your code again. You can't open a recordset from a recordset. In your code example.

Dim SalRec1 As DAO.Recordset     'Related records
    Dim SalRec2 As DAO.Recordset     'Related records
    Dim strSql1 As String            'SQL statement
    Dim strSql2 As String            'SQL statement
    Dim strOut As Variant           'Output string For Count.


strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
    Set SalRec1 = DBEngine(0)(0).OpenRecordset(strSql1, dbOpenDynaset)
    strSql2 = "SELECT Count([sMth]) FROM SalRec1"  'You cannot perform this operation.  You must select from a valid table name.
Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

You should create a query out of your first statement and then do a "SELECT Count(sMth) FROM qrySalesRec1". I will post code sample in my next post.

[AccessMSSQL]

This example dynamically modifies the query sql statement and then selects from the query. You must first create the query - the query can just select * from sales since this code will modify that based on value of [Outlet]

Dim SalRec1 As DAO.Recordset     'Related records
    Dim SalRec2 As DAO.Recordset     'Related records
    Dim strSql1 As String            'SQL statement
    Dim strSql2 As String            'SQL statement
    Dim strOut As Variant           'Output string For Count.
    Dim qryDef As DAO.QueryDef
strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & Outlet & "'"
Set qryDef = CurrentDb.QueryDefs("qrySalesRec")
qryDef.SQL = strSql1
 strSql2 = "SELECT Count([sMth]) FROM qrySalesRec"
Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

I did, however, take the time to test your first original statement you were trying to run. This statement actually works for me in VBA code. I did add an alias to your Count statement.

strSql2 = "SELECT Count(sMth) AS MonthCount"
strSql2 = strSql2 & " FROM (SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales)  AS temptable;"

[FrustratedAlso]

I tried your solution but I still get error 3265: Item not found in this collection.DAO.QueryDefs ??

Public Function sPeriod(OutLet As String) As Variant
On Error GoTo Err_Handler
    'Purpose:   Count Distinct Month/Year in Sales Records.
    'Return:    Integer, or 0 if no matches.
    'Arguments: None
    '
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim SalRec2 As DAO.Recordset     'Related records
    Dim strSql1 As String            'SQL statement
    Dim strSql2 As String            'SQL statement
    Dim strOut As Variant           'Output string to concatenate to.
    Dim qryDef As DAO.QueryDef
    'Initialize to 0
    sPeriod = 0
    
    
    'Build SQL strings, and get the records.
    'select Records to Count
        strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
        Set qryDef = CurrentDb.QueryDefs("qrySalesRec")
        qryDef.SQL = strSql1
    'Count the Records
        strSql2 = "SELECT Count([sMth]) FROM qrySalesRec"
        Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
    
    ' Check for Count
    If Not IsNull(SalRec2(1)) Then
        strOut = SalRec2(1)
   
    End If
    
    SalRec.Close
       'Return the result
       sPeriod = strOut
     
Exit_Handler:
    'Clean up
     
     Set SalRec2 = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & Err.Source
    Resume Exit_Handler
End Function

Your try of my original does the same thing, you didn't have the where clause on it??

strSql2 = "SELECT Count(sMth) AS MonthCount"
strSql2 = strSql2 & " FROM (SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales  WHERE Sales.[Venue] = '" & OutLet & "')
  AS temptable;"

[AccessMSSQL]

hmmm...not sure what else is going on here. The code looks fine. Have you tried decompiling? Is it possible for you to post the project here?

[FrustratedAlso]

I'm using Access 2007 and I think something os going on with it. If I try and rename a report, query, or form. When I highlight the name, right click and pick rename, it briefly opens the item to rename and before you can do anything immediately focuses elsewhere? Also when in VB if I try to compile from the debug tab, it shuts down Access?? I've tried "repairing" the installation of access and the "compact and repair database" from the access manage tab; but neither seem to help????

[AccessMSSQL]

Sounds like your VBA library is corrupt or you might have a virus. Have you tried decompiling? Just open the database with a shortcut and add the switch \decompile at the end of the path: "C:\databasefiles\Database.ACCDB" /decompile.

Also, do you have multiple versions of Access installed on one machine? If you have 2003 and 2007 installed, or 2007 and 2010 this sometimes will corrupt the database.

If all else fails create a new blank database and just import all of the objects from the current database into it. The import operation may fail on whatever object is corrupt in your database so you will have to re-create that object.

Here's an article that might help you:
http://allenbrowne.com/ser-47.html

[FrustratedAlso]

I get an error message that Access doesn't recognize the option?


Attachment 7300

"K:\Lumenaire LLC\DBA Packrat's Attic\Packrat Inventory.accdb" \decompile

[AccessMSSQL]

YOu need to use a forward slash. /decompile

[FrustratedAlso]

With the forward slash and a space "K:\Lumenaire LLC\DBA Packrat's Attic\Packrat Inventory.accdb" /decompile it. the database opens right up with no indication that is doing anything. still have same problems. Tried creating new blank database and importing. everything imports not errors; but still same problems?

[AccessMSSQL]

Access doesn't do anything visible when you use decompile. Seems like weird behavior. Is it possible for you to post project?