Displaying a Record Count

J

Juan Schwartz

I am simply trying to count records and use returned values in my
report as I do often, but for some reason I can't catch my error here.

======================== Snippet =============================

Set anirs = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM
dbo_CTI_CallHistory WHERE Format(CallDateTime,""m/d/yyyy"") BETWEEN
#2006-04-26# AND #2006-05-02#")

Text0.Value = anirs.RecordCount

ANICount = anirs.RecordCount

======================= End Snippet============================


I've checked the query and it runs returning 179,419. When I try to
assign it to Text0's value, it gives me 1 every time.

I tried this as I've done before with great success on reports...

======================== Snippet =============================

Text0.Value = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM
dbo_CTI_CallHistory WHERE Format(CallDateTime,""m/d/yyyy"") BETWEEN
#2006-04-26# AND #2006-05-02#")

======================= End Snippet============================

I get a type mismatch error there, so something is wrong with how I'm
going about this. Any help would be appreciated.

-Jimmy Kirk
 
D

Duane Hookom

Why use the format() when I think this should work:
Set anirs = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM
dbo_CTI_CallHistory WHERE CallDateTime BETWEEN #2006-04-26# AND
#2006-05-02#")
 
J

Juan Schwartz

I thought that when you didn't do the format that the info pulled was
2006-04-26(12AM) - 2006-05-02(12AM). I will try again without the
formats.
 
R

Roger Carlson

BTW, here is an alternative that should work WITH the format:

Set anirs = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM
dbo_CTI_CallHistory WHERE Format(CallDateTime,""yyyy-dd-mm"") BETWEEN
""2006-04-26"" AND ""2006-05-02""")

This should work, because the Format() function converts dates to text.
Text dates formatted as yyyy-dd-mm will sort correctly and can be used in
ranges.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
J

Juan Schwartz

See, I'm still getting "1" no matter what I do... even just "SELECT *
FROM dbo_CTI_CallHistory" returns "1". I'm baffled.
 
J

John Spencer

You are getting a recordcount, but the problem is you must move to the end
of the recordset to get an accurate record count.


Text0.Value = anirs.RecordCount
Anirs.MoveLast

ANICount = anirs.RecordCount

Of course in this case, it is still going to return one, because that is all
that is in the recordset - one record with a count value in one field.

What you probably want is

Set anirs = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM
dbo_CTI_CallHistory WHERE CallDateTime BETWEEN #04/26/2006# AND
#05/02/2006#")

ANICount= Anirs!Fields(0)

Of course, you should be able to do this all using the DCount function.
ANICount=DCount("*", "dbo_CTI_CallHistory", "#04/26/2006# AND #05/02/2006#")
 
D

Duane Hookom

Good catch John. I just realized Juan was reading the recordcount property
of the recordset rather than the value of the returned record.
 
J

Juan Schwartz

Hey Duane,

Aside from this, I've moved along quite well in Access in terms of
reports. I finally got the reports to work the way I wanted by doing
almost all of my data gathering in VBA behind the scenes. I always
wondered how people dealt with the "one query" then subreporting deal.
I've been simply making my reports by putting controls everywhere and
manually assigning their values in the VBA and to use grouping, I use a
query as the record source with what I want to group by and use that
value in my queries.

All in all, Access is far more flexible for me now and I am able to do
some of the things I could only do in other languages(like php). Thank
you Duane, John and Google Groups!
 

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