Record Count

[DawnP277]

I am trying to count the number of records in my table that have the the same
base and date.

I wrote some code but it is not executing. I know I am missing something.

If someone could look at my code I would appreciate it.
Dim IntCount As Variant
strSQL = "Select(*)" _
& "From [Tabledata]" _
& " WHERE Tabledata.[Base] = BaseLetter" And "Tabledata.[TDate] =
DDate;"
Set rs2 = db.OpenRecordset(strSQL)
rs2.MoveLast
IntCount = rs2.RecordCount

Thank you
Dawn

 

[Stuart McCall]

I am trying to count the number of records in my table that have the the
same
base and date.

I wrote some code but it is not executing. I know I am missing something.

If someone could look at my code I would appreciate it.
Dim IntCount As Variant
strSQL = "Select(*)" _
& "From [Tabledata]" _
& " WHERE Tabledata.[Base] = BaseLetter" And "Tabledata.[TDate] =
DDate;"
Set rs2 = db.OpenRecordset(strSQL)
rs2.MoveLast
IntCount = rs2.RecordCount

Thank you
Dawn

Try using the Dcount function instead:

strCriteria = "Tabledata.[Base] =" & BaseLetter & " AND Tabledata.[TDate] ="
& DDate
intCount = Dcount("*", "Tabledata", strCriteria)

 

[John W. Vinson]

If someone could look at my code I would appreciate it.
Dim IntCount As Variant
strSQL = "Select(*)" _
& "From [Tabledata]" _
& " WHERE Tabledata.[Base] = BaseLetter" And "Tabledata.[TDate] =
DDate;"
Set rs2 = db.OpenRecordset(strSQL)
rs2.MoveLast
IntCount = rs2.RecordCount

Replace this all with

IntCount = DCount("*", "[Tabledata]", "[Base] = 'BaseLetter' AND [Tdate] =
'DDate')

assuming that you want records where the field Base contains the literal text
string 'BaseLetter' and the field TDate contains the literal text string
'DDate'. If that's not what you want please explain.

 

[DawnP277]

Thanks Stuart,
I tried your code as written but I am getting a runtime error 13 for TYpe
Mismatch would happen to know why?

Thanks Again

I am trying to count the number of records in my table that have the the
same
base and date.

I wrote some code but it is not executing. I know I am missing something.

If someone could look at my code I would appreciate it.
Dim IntCount As Variant
strSQL = "Select(*)" _
& "From [Tabledata]" _
& " WHERE Tabledata.[Base] = BaseLetter" And "Tabledata.[TDate] =
DDate;"
Set rs2 = db.OpenRecordset(strSQL)
rs2.MoveLast
IntCount = rs2.RecordCount

Thank you
Dawn

Try using the Dcount function instead:

strCriteria = "Tabledata.[Base] =" & BaseLetter & " AND Tabledata.[TDate] ="
& DDate
intCount = Dcount("*", "Tabledata", strCriteria)

 

[egerds]

TYpe Mismatch
sounds like your linking field is not a string, use cstr()
or your date field is not a field use cdate()

 

[kc-mass]

Hi Dawn

If your want to do in query try:

SELECT BaseLetter, TDate, Count(BaseLetter) AS CountOfBaseLetter
FROM tabledata
GROUP BY BaseLetter, TDate;

That will hand you the count of every possible, unique pair of BaseLetter
and TDate.

Regards

Kevin

 

[John Spencer]

Assumptions:
TDate is a Date field;
Base is a string field.
"BaseLetter" is a string value you want to match
DDate is a variable or a control on a form

IntCount="*","TableData","Base='BaseLetter' AND TDate=" &
Format(DDate,"\#yyyy-mm-dd\#")

If Baseletter is a variable or control that contains a string
IntCount="*","TableData","Base='" & BaseLetter & "' AND TDate=" &
Format(DDate,"\#yyyy-mm-dd\#")
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================