DCount() Issue

[nfk]

Just to clarify, the code that namliam submitted was

Currentdb.querydefs("qryBetweenDates").sql = strSQL

But you listed

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.

Oh yeah, no thats ok query is named qryMTOBetweenDates

One of the things that don't seem clear to me is whats the point on creating a new query (?), is it an empty query? what am I supposed to put in it? If I use that query for populating my report what am I supposed to set as record source for the report fields ??? lots of questions...

 

[BigHappyDaddy]

It may or may not be an 'empty query'. And by empty query in this context, I mean a query without any SQL statement. The line of code we are discussing assigns a SQL statement to the query named qryMTOBetweenDates.

...what am I supposed to set as record source for the report fields?

You use the field names defined in the query. Per the query

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) "

Your names are txtField, ID, Date, txtSurname, txtFirstName, holiday, Transport.

So use those names in as the values in the Control Source properties of your various report controls.

And just to eliminate confusion, unless you really need the duplicate field "txtField", I would remove the line

" dbo.tblData2.txtField, " & _

from your SQL statement. The two fields would always be the same, but a query can't have the same field name defined twice.

 

[nfk]

This is possibly the most bizarre way to end this post but I manage to make it work in a very surreal fashion...

After over 16 hours trying to sort this stupid thing out and thinking over and over and rereading the comments here, bytes, microsoft, googling until the results started coming in chinese I decided that the proper way was never gonna come around...

...So I workaround 3 dropdowns, 3 textboxes, all invisible. After user enter 2 dates (in the original textboxes) the 3 dropdowns get "row source" from an sql query and the 3 textboxes get populated by the record count on each dropdown, after that I got the 3 integers I need to put in the report and I just pass them to the Report itself with Forms!frmMain!txtCount.

Access...