Record Count

D

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
 
S

Stuart McCall

DawnP277 said:
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)
 
J

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.
 
D

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

Stuart McCall said:
DawnP277 said:
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)
 
E

egerds

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

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
 
J

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
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top