dcount function

[Purnima Sharma]

I have a main report called agents report. I have attached a subreport
called sragents. An agent can register different type of loans such as
rimary", "Helock", "PM","RCES". The type of loans are listed in LoanCode
column in the subreport. I have to make four text boxes in the main report
which would capture the number of loans for the respective categories. I
tried using dcount function, but it does not give the count. I think I am
not using the formula correctly. Can someone help me with the correct
syntax. Also, can a subreport be grouped based on the type of loan? Thanks.
I appreciate your help and time.

Purnima

 

[Larry Linson]

Without knowing your table, query, and field names, we can't give you any
more information on DCount than you can get by placing the cursor on DCount
in the code and pressing F1.

I suspect the trick is in getting the Criteria set properly for each.

Larry Linson
Microsoft Access MVP

 

[Purnima Sharma]

My main report is coming from the query "CommissionAgents" in which I have
fields: Empno, PeriodEnddate, loanno etc. The data for the subreport is
coming from the query "CommissionAgentsSubreport" which has column empno,
periodEnddate, LoanNo, App_type_code". I have to put a text box in the main
report which should capture count of loans based on the data in App_type_code
in the subreport e.g count the number of Helock loans

 

[Larry Linson]

You appear to have LoanNo as a Field in both the main Report and in the
Subreport. Does that mean each detail record of the main Report deals with a
single loan identified by LoanNo, and if so, what is in the Subreport --
just one loan?

I had, until this last post, had the idea that your main Report was by
Employee and the Subreport showed the loans initiated by that Employee
during the period specified.

I would think, however, that the Dcount for a given EmpNo for a Given Type
of Loan, for a given PeriodEndDate might look like:

strCriteria = "[EmpNo] = """ & txtEmpNo & _
" AND [AppTypeCode] = ""Helock"" & _
" AND [PeriodEndDate] = #" & txtPeriodEndDate & "#"

txtCount = Dcount("*", "CommissionAgents", strCriteria)

This assumes that EmpNo and AppTypeCode are Text fields and that
PeriodEndDate is a DateTime field and that EmpNo is shown in the main Report
in Text Box txtEmpNo and PeriodEndDate is shown in the main Report in Text
Box txtPeriodEndDate. Note: it is "air code", untested.

Larry Linson
Microsoft Access MVP

 

[Purnima Sharma]

You appear to have LoanNo as a Field in both the main Report and in the
Subreport. Does that mean each detail record of the main Report deals with a
single loan identified by LoanNo, and if so, what is in the Subreport --
just one loan?

I had, until this last post, had the idea that your main Report was by
Employee and the Subreport showed the loans initiated by that Employee
during the period specified.

I would think, however, that the Dcount for a given EmpNo for a Given Type
of Loan, for a given PeriodEndDate might look like:

strCriteria = "[EmpNo] = """ & txtEmpNo & _
" AND [AppTypeCode] = ""Helock"" & _
" AND [PeriodEndDate] = #" & txtPeriodEndDate & "#"

txtCount = Dcount("*", "CommissionAgents", strCriteria)

This assumes that EmpNo and AppTypeCode are Text fields and that
PeriodEndDate is a DateTime field and that EmpNo is shown in the main Report
in Text Box txtEmpNo and PeriodEndDate is shown in the main Report in Text
Box txtPeriodEndDate. Note: it is "air code", untested.

Larry Linson
Microsoft Access MVP

My main report is coming from the query "CommissionAgents" in which I have
fields: Empno, PeriodEnddate, loanno etc. The data for the subreport is
coming from the query "CommissionAgentsSubreport" which has column empno,
periodEnddate, LoanNo, App_type_code". I have to put a text box in the
main
report which should capture count of loans based on the data in
App_type_code
in the subreport e.g count the number of Helock loans

Actually I was able to use DCount function in a text box in the subreport successfully, but when I try to put it in the main report, it gives me an error stating that it is an aggregate function. Do I have to make a function to do this and then call it in the text box in the main report. I don't know programming that well but I do have the knowledge of concepts. This is my first big project. Could you please write me the function which would take the argument of empno and date.

I will try to use your formula also on Monday and see if it works. To
clarify the scenario agian, the main report lists the total commisssion (one
figure) of all the loans registerd by an agent whether it is Helock , RCES,
or, PM ). It looks like this in the main report:

Total Loans Total commission
12 2000

I have to breakdown these figures to list commission individually which
would look like this in the table:

Type of loan Count of loans Multiplier Total Commission

HE 3 100
300
RCES 5 200
1000
PM 4 175
700

The subreport has details of all the loans and it is coming from a separate
table. The linked fields are Empno and PeriodEndDate.

The agents are paid commission on a biweekly basis. The periodenddate counts
all the loans listed up to the date entered in the report. e.g. If an agent
types 1/20, it should list the commission for all the loans up to 1/20.

Thank you very much. I sincerely appreciate your help. Thanks
Purnima