DCount() Issue

N

nfk

Registered User.
Local time
Yesterday, 22:27
Joined
Sep 11, 2014
Messages
117
Im trying to use =DCount() on a report and its half way working, it does count records acording to the criteria I asign the problem is its using the whole universe of records in the table so I have a report displaying 10 records of which 953 are Red and 752 are Blue... :confused:
 
If the source of the report is a query, try using the query as the "table" in the dcount instead of your source table.
 
Hey, thanks for your reply...


it is like that

Code: 
=DCount("[IDFIELD]","qryQUERY","[BOOLEANFIELD] = 1")

with without brackets any possible combination... it always displays count for all records in the db.
 
Which means some filtering is being done inside the report which you must repeat in your dcount..... or push the filtering back into the query.
 
Code: 
Dates are variables and they are working fine...
Select * from DATA where DateAdded < 2014-10-31 and Date2 > 2014-10-01

Group ----> TYPE1

-----------------------
|  ID  | Name | Disp  |
-----------------------
|  842 | nme8 | 1     |
|  723 | nme4 | 1     |
|  143 | nme3 | 0     |
|  288 | nme1 | 1     |
|  107 | nme7 | 0     |
-----------------------

=DCount("[ID]","QUERY","Disp = 1")

the structure of the report is as follows
Report header ---> nothing
Page header ---> nothing
TYPE's Header ---> just plain text
Detail ----> Dcount for the TYPE's Group
TYPE's Footer ----> Final Dcounts for overall
Page Footer ---> nothing
Report Footer ----> nothing

Ok, so this is the real case scenario.... yeah, maybe something wrong about the position of the DCount?
 
I have tried everything to make this work... As you can see in the picture that just a third of the different options I have tried, on every single part of the report, every single header and footer, every possible combination of quotes, single quotes, Yes/No, True/False, 0/1, 0/-1, IIf, Abs, Count, DCount, ()/[]... Nothing seems to work for the purpose of counting how many records are True and how many are False... Im 100% at the verge of insanity trying to do something as simple as this and not being able to... So frustrating....

mto_report.jpg
 
I been trying this lately:

Code: 
=DCount("*","[tblData]","[Holiday] = 0")

It counts ok, problem being it counts all False values for the column for the whole table not just for the records selected to be showed in the report. (?)
 
BigHappyDaddy said:
What is the recordsource for the report?
Click to expand...

This is the query source for the report:

Code: 
SELECT DISTINCT 
     dbo.tblData2.txtField, 
     dbo.tblData.txtField, 
     dbo.tblData.ID, 
     dbo.tblData.Date, 
     dbo.tblData.txtSurname, 
     dbo.tblData.txtFirstName, 
     dbo.tblData.holiday, -- This is the Boolean field Im trying to get Counts from
     dbo.tblData.Transport
FROM dbo.tblData INNER JOIN
     dbo.tblData2 ON 
     dbo.tblData.txtField = dbo.tblData2.txtField

The report is being called through a form with two text boxes one for "date from" and the other for "date to" a button with the following action:

Code: 
Private Sub cmdGetReport_Click()
On Error GoTo Err_cmdGetReport_Click

    Dim stDocName As String
    Dim strSQL As String
    stDocName = "rptBetweenDates"
    strSQL = ReturnBetweenDates("Date", Me.txtReportDateFrom.Value, Me.txtReportDateTo.Value)
    DoCmd.OpenReport stDocName, acPreview, , strSQL
    Me.txtReportDateFrom.Value = Null
    Me.txtReportDateTo.Value = Null
Exit_cmdGetReport_Click:
    Exit Sub

Err_cmdGetReport_Click:
    MsgBox Err.Description
    Resume Exit_GetReport_Click
    
End Sub

And finally the VBA...

Code: 
Function ReturnBetweenDates(strFieldName As String, StartDate As Date, EndDate As Date) As String
Dim strSQL As String
strSQL = "([" & strFieldName & "] >= CONVERT(DATETIME,'" & Format(StartDate, "yyyy-mm-dd") & " 00:00:00',102))"
strSQL = strSQL & " AND ([" & strFieldName & "] <= CONVERT(DATETIME,'" & Format(EndDate, "yyyy-mm-dd") & " 00:00:00',102))"
ReturnBetweenDates = strSQL
End Function
 
Last edited:
Ok, this query is doing a bit of filtering. Basiclly the query is eliminating all the records from tblData where tblData.txtField <> tblData2.txtField. So your DCount statement must make the same 'eliminations' in order to return the correct count.

Maybe the simplest way is to base you dcount on this same query. Create a query that is the exact same as the report's recordsource (or if it is already a named query, just use the name). The write your DCount as so:
Code: 
=DCount("*","NewQueryName","Holiday = 0")

Give it a whirl...:p
 
Tried that too keeps giving me #Error.

I dont know why but I keep thinking maybe theres something im missing in my VBA just before the report in created... Maybe theres a way of making the query before creating the report and storing the result in a variable that can then be printed inside the report?

Is this a possibility?
 
A possibility is to copy the report's recordsource and use that to create a querydef. Then base the DCount off of the new query def.
Code: 
Dim qry as QueryDef
Dim strSQL as String
Dim lngCount as Long
 
strSQL = Me.RecordSource
 
Set qry = CurrentDB.CreateQueryDef("TEMP",strSQL)
lngCount = DCount("*","TEMP","[Holiday] = 0")

That should get you close(r). ;)
 
I'll give this a shot tomorrow, I'm tired after 8 hours of Access in search for this very simple task... frustrated to the core... thanks for your help.
 
namliam said:
If the source of the report is a query, try using the query as the "table" in the dcount instead of your source table.
Click to expand...

Seems we gone full circle already...

Copying the recordsource wont really help, since the dynamic where being send in the Openreport isnt copied to the recordssource.... (or wasnt, not sure about the newer versions)

Make a query, name it qryBetweenDates
Change:
Code: 
    strSQL = ReturnBetweenDates("Date", Me.txtReportDateFrom.Value, Me.txtReportDateTo.Value)
    DoCmd.OpenReport stDocName, acPreview, , strSQL

to
Code: 
strSQL = " SELECT DISTINCT " & _
                " dbo.tblData2.txtField, " & _
                " dbo.tblData.txtField, " & _
                " dbo.tblData.ID, " & _
                " dbo.tblData.Date, " & _
                " dbo.tblData.txtSurname, " & _
                " dbo.tblData.txtFirstName, " & _
                " dbo.tblData.holiday, " & _
                " dbo.tblData.Transport" & _
         " FROM dbo.tblData INNER JOIN" & _
         "      dbo.tblData2 ON dbo.tblData.txtField = dbo.tblData2.txtField " & _ 
         " Where ReturnBetweenDates("Date", Me.txtReportDateFrom.Value, Me.txtReportDateTo.Value) "
    Currentdb.querydefs("qryBetweenDates").sql = strSQL
    DoCmd.OpenReport stDocName, acPreview

Make the reports recordsource be qryBetweenDates and do your dcount on that query too, should not have any issues.
 
Thanks for your reply...

Code: 
 " Where ReturnBetweenDates("Date", Me.txtReportDateFrom.Value, Me.txtReportDateTo.Value) "

just wont go unless I change the double quote on Date for single quote and even then will pop the following message...

Object variable or With block variable not set

I created a new query as suggested that contains:

Code: 
SELECT dbo.tblData.*
FROM dbo.tblData

...which is the least amount of sql the query wizard allow me to input.
 
Guess I was too fast in "fixing" the query...

that should be
" Where " & ReturnBetweenDates("Date", Me.txtReportDateFrom.Value, Me.txtReportDateTo.Value)
 
Look what I found, Rachel has pretty much the exact same problem I have...

Code: 
https://groups.google.com/forum/#!topic/microsoft.public.access.adp.sqlserver/WsXd6fsUdQ8

Only problem is, if you go to the google group referenced the search wont help at all... and if I throw a google search on ado commands I get all sorts of non related stuff... Sad...
 
Sorry dude, I keep getting "Object variable or With block variable not set"

Seems to work ok until "CurrentDb.QueryDefs("qryMTOBetweenDates").SQL = strSQL"

I put a msgbox before and the query look alright but then it shows that message.
 
Just to clarify, the code that namliam submitted was
Code: 
Currentdb.querydefs("qryBetweenDates").sql = strSQL

But you listed
Code: 
CurrentDb.QueryDefs("qry[B][COLOR=red]MTO[/COLOR][/B]BetweenDates").SQL = strSQL

Which is fine as long as the query you have created is named "qryMTOBetweenDates". The error you are reporting leads me to think otherwise.
 
You must log in or register to reply here.

Similar threads

K
Strange query behavior
Replies
12
Views
908
Ken Sheridan
Ken Sheridan
oxicottin
Solved DCount Date field for duplicates giving Error#
Replies
7
Views
1,009
Ken Sheridan
Ken Sheridan
E
Dcount with a Data range
Replies
3
Views
532
Mark_
M
K
DCount on report
Replies
2
Views
634
Gasman
Gasman
P
Solved current record PDF issues
2
Replies
21
Views
1,803
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom