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
Click to expand...

Try using the Dcount function instead:

strCriteria = "Tabledata.[Base] =" & BaseLetter & " AND Tabledata.[TDate] ="
& DDate
intCount = Dcount("*", "Tabledata", strCriteria)
 
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
Click to expand...

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
Click to expand...

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.
 
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
Click to expand...

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
Click to expand...

Try using the Dcount function instead:

strCriteria = "Tabledata.[Base] =" & BaseLetter & " AND Tabledata.[TDate] ="
& DDate
intCount = Dcount("*", "Tabledata", strCriteria)
Click to expand...
 
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
Click to expand...

Try using the Dcount function instead:

strCriteria = "Tabledata.[Base] =" & BaseLetter & " AND Tabledata.[TDate] ="
& DDate
intCount = Dcount("*", "Tabledata", strCriteria)
Click to expand...
 
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()
 
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
 
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
'====================================================
 
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

Similar Threads

DCOUNT 5
Data Type Conversion error 5
Run time error in VBA Code 1
Division Question 1
Too few Parameter. Expected 1 8
Please help with this code 1
"Object doesn't support this property or method" Error on Tree Con 0
Event procedure not working 2

Top