Passing Variable To DCount & Dsum on Rpt

[rjf3123]

I have created a report that looks kind of like a spreadsheet where there are rows and columns of information. For some fields, I am using DCount and others I am using DSum to pull the correct information ouf of a table.

An example of the DCount expression:
=DCount("[InvoiceDate]","ClientTracking","[AccountRep]='John' AND [InvoiceDate]>=#1-1-2003# AND [InvoiceDate]<=#1-31-2003#")

An Example of the DSum expression:
=Nz(DSum("[TER]","ClientTracking","[AccountRep]='John' AND [InvoiceDate]>=#1-1-2003# AND [InvoiceDate]<=#1-28-2003#"),0)

With these hard coded on the report, everything works fine.

What I would like to do is either use a listbox off a switchboard to select the AccountRep or at a minimum, be prompted to enter that into a prompt box.

Secondly, I would like to know if it is possible to change the date parameters to automatically pick up the current year rather than having the "-2003#" hardcoded in the expression.

Anyone?

TIA -
R. Fisher

 

[mark curtis]

What I would like to do is either use a listbox off a switchboard to select the AccountRep or at a minimum, be prompted to enter that into a prompt box.

A) Create a combo or list box and then just refer to the selection from the query. But if u wish to return records for multiple AccountReps then u need a little code.

Secondly, I would like to know if it is possible to change the date parameters to automatically pick up the current year rather than having the "-2003#" hardcoded in the expression.

A) USE Year((Now)) or if u want 2004 then use =Year(Now())+1

 

[rjf3123]

Mark -
Thanks for the direction here. As in the past, when I pose a question, I provide the working solution here for other folks seeking answers.

As far as selecting the Rep off the switchboard, I simply used an empty text box on the report itself called AccountRep. When I click on the switchboard button, I get a MsgBox asking for the name of the Rep. This works fine for us because we have less than 10 reps.

As far as picking up the current year in the DCount & DSum functions, not 100% sure if this is the "cleanest" solution but it certainly works.

=DCount("[SubmitDate]","tblClientTracking","[SubmitDate] Between # " & '1-1-' & DatePart('yyyy',Date()) & " # AND # " & '1-31-' & DatePart('yyyy',Date()) & " #")

Thanks again for the reply, can't tell you how much this board has saved me in terms of time and aspirin!

R. Fisher