Full Version: Record Count...
UtterAccess Forums > Microsoft® Access > Access Forms
doran_doran
Hi,
ollowing code is behind a form. How can I get count of the records.
Dim strSQL As String
Dim qdfApp As QueryDef
DoCmd.SetWarnings False

Set qdfApp = currentdb.QueryDefs("qryRptNewPlans")
strSQL$ = "SELECT * FROM qryRptNewPlans WHERE ((qryRptNewPlans.DatePlanSold Between # " & Me!txtStartDate & " # AND # " & Me!txtEndDate & "# )"
qdfApp.close
DoCmd.SetWarnings True
quest4
What item are you trying to count? Here is a sample of part of a query I use to count PartNo per month:
SELECT Format([DateIssued],'mmm yyyy') AS MonthIssued, Count(tsubQualityControlAction.PartNo) AS CountOfPartNo, tsubQualityControlAction.Department
It goes on but I think you might now see what is missing. hth.
BrianS
use a recordset
!--c1-->
CODE
Dim rec as DAO.Recordset
Dim strSQL as String
strSQL = "SELECT * FROM qryRptNewPlans WHERE ((qryRptNewPlans.DatePlanSold Between # " & Me!txtStartDate & " # AND # " & Me!txtEndDate & "# )"
Set rec = CurrentDB.OpenRecordset(strSQL,dbOpenDynaset)
If Not rec.Eof Then
     rec.MoveLast
     rec.MoveFirst
End if
MsgBox "There are & " rec.RecordCount & " records"
rec.Close
Set rec = Nothing
doran_doran
Thanks for prompt respond.
I am trying the code provided by Uno1980.
Getting an error on following line
Set Rec = currentdb.OpenRecordset(strSQL, dbOpenDynaset)
SEE ATTACHMENT FOR ACTUAL ERROR....
doran_doran
anyone else ????
Dom DXecutioner
Try pasting the following onto a standard module ...
!--c1-->
CODE
Function fRecordCount(RecSource As String) As Long

[color="green"]'// dimension variable(s)[/color]
Dim db As DAO.Database
Dim rs As DAO.Recordset

[color="green"]'// in the event of an error proceed to Error Handler[/color]
On Error GoTo Err_fRecordCount

    [color="green"]'// create object pointing to current database[/color]
    Set db = CurrentDb
    [color="green"]'// create and set recordset object[/color]
    Set rs = db.OpenRecordset(RecSource, dbOpenSnapshot)

    [color="green"]'// reference recordset object[/color]
    With rs
    
        [color="green"]'// check if we're at the end of recordset[/color]
        If Not .EOF Then
            [color="green"]'// move to last record in recordset
            '// to get accurate recordcount[/color]
            .MoveLast
            
        End If
        
        [color="green"]'// return count[/color]      
        fRecordCount = .RecordCount
        
        [color="green"]'// close recordset object[/color]
        .Close
    
    End With

Err_fRecordCount:
       [color="green"]'// clean up / release memory[/color]
        Set db = Nothing
        Set rs = Nothing
        Exit Function

Exit_fRecordCount:
       MsgBox Err.Number & " : " & Err.Description
       Resume Err_fRecordCount

End Funtion

Example usage ...
CODE
Dim stDocName = "SomeTable"
     MsgBox "Total Records: " & fRecordCount(stDocName)

I hope this helps
BrianS
change strsql to this
!--c1-->
CODE
strSQL = "SELECT * FROM qryRptNewPlans WHERE (qryRptNewPlans.DatePlanSold Between # " & Me!txtStartDate & " # AND # " & Me!txtEndDate & "# )"

Oremoved one of the parens
doran_doran
I tried x-avier method and I always get o "Zero" records.
tried Brian's (uno1980) method and still having same problem.
BrianS
what problems?
BrianS
you need to test your query in a query window to make sure it the syntax is correct
ight after the value of strSQL is set insert
Debug.Print strSQL
it will print the value into a the debug widow. Copy the value out of the debug window into a query window and run the query. Correct any syntax errors then correct the code where the value of strSQL is set.
doran_doran
See my attached pictures
receive snap-1.jpg first then the second error, which is basically debug window.
doran_doran
SELECT * FROM qryRptNewPlans WHERE (qryRptNewPlans.DatePlanSold Between # 01/01/2003 # AND # 01/25/2003# )

this is wat i get when i did debug window (debug.print strsql)


Bottom is my query and it works perfectly....
=============================================================
SELECT qryMain.DatePlanSold, IIf([DatePlanSold] Is Null,"13",Format([DatePlanSold],"mm")) AS MonthNumber, qryMain.GA_Number, qryMain.PlanNum, qryMain.PYE, qryMain.Plan_Name, qryMain.CurrentStatus, qryMain.Plan_Type, qryMain.SvcType, qryMain.Primary_Administrator, qryMain.[1stYearofSvc], qryMain.SystemType, qryMain.Market_sgmt, qryMain.BillingStructure, qryMain.EmployerType, qryMain.Region
FROM Qry_Single_Record_1st INNER JOIN qryMain ON (Qry_Single_Record_1st.PlanNum = qryMain.PlanNum) AND (Qry_Single_Record_1st.GA_Number = qryMain.GA_Number) AND (Qry_Single_Record_1st.MaxOfPYE = qryMain.PYE)
WHERE (((qryMain.DatePlanSold) Between [Forms]![frmRptNewPlans]![txtStartDate] And [Forms]![frmRptNewPlans]![txtEndDate]))
OrderROM qryRptNewPlans WHERE (qryRptNewPlans.DatePlanSold Between # 01/01/2003 # AND # 01/25/2003# )

this is wat i get when i did debug window (debug.print strsql)


Bottom is my query and it works perfectly....
=============================================================
SELECT qryMain.DatePlanSold, IIf([DatePlanSold] Is Null,"13",Format([DatePlanSold],"mm")) AS MonthNumber, qryMain.GA_Number, qryMain.PlanNum, qryMain.PYE, qryMain.Plan_Name, qryMain.CurrentStatus, qryMain.Plan_Type, qryMain.SvcType, qryMain.Primary_Administrator, qryMain.[1stYearofSvc], qryMain.SystemType, qryMain.Market_sgmt, qryMain.BillingStructure, qryMain.EmployerType, qryMain.Region
FROM Qry_Single_Record_1st INNER JOIN qryMain ON (Qry_Single_Record_1st.PlanNum = qryMain.PlanNum) AND (Qry_Single_Record_1st.GA_Number = qryMain.GA_Number) AND (Qry_Single_Record_1st.MaxOfPYE = qryMain.PYE)
WHERE (((qryMain.DatePlanSold) Between [Forms]![frmRptNewPlans]![txtStartDate] And [Forms]![frmRptNewPlans]![txtEndDate]))
ORDER BY qryMain.DatePlanSold DESC , qryMain.GA_Number;
Edited by: doran_doran on Wed Nov 10 14:29:27 EST 2004.
Dom DXecutioner
is it safe to say that you got it working ? if not, is your query a parameter query ? If so, that's what's causing the problem. I've experience that you can not use a parameter query in an SQL statement, unless you provider all of the parameters.
doran_doran
xyonx: No it's not working. I spent about 7 hours. I guess I am force to quit on this. LOL.
et me know if you know a better way... I tried your method and it does not return anything. Always Zero.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.