Another Dcount Question ?

[skiphooper]

Hi Everone,

I have yet another Dcount question ?

My problem is I haven't been able to format the the criteria correctly.
Rather than someone doing this for me, I'd just like to know
where or what to look at to accomplish this.
I,ve tried it at least 50 different times with no success.

I dont know exactly where or when to put the quote's and
double quotes.

below is my dcount statement

=DCount("[UserId]","tblInput"),

as the third argument ( for the criteria ) I need the following
included.

(((Month([inputdate]))=[Forms]![frmCalender].[Month]) AND ((Year([inputdate]))=[Forms]![frmCalender].[year]) AND ((tblInput.UserID)=[forms]![frmCalender]![cboUser]))

Thanks in advance
Skip

 

[RichMorrison]

You wrote
<<
(((Month([inputdate]))=[Forms]![frmCalender].[Month]) AND ((Year([inputdate]))=[Forms]![frmCalender].[year]) AND ((tblInput.UserID)=[forms]![frmCalender]![cboUser]))
>>

Skip,
Here's the basic concept; you have to convert this into one long string that Access can understand. Access can NOT understand a mix of keywords and "Form!Field" stuff.

You want something like this:
strCriteria = "(((Month([inputdate]))="
& [Forms]![frmCalender].[Month]
& ") AND ((Year([inputdate]))="
& [Forms]![frmCalender].[year]
& ") AND ((tblInput.UserID)="
& [forms]![frmCalender]![cboUser]
& "))"

This string might not be completely valid, but you can see how the Forms! stuff is separated from the literal SQL.

RichM

 

[Alexandre]

The first two arguments must be string, so either:
- string variables
- or names surrounded by double quotes as in your case

The third argument is the equivalent of a WHERE statement in a query. It must compare fields of your domain (the table "tblInput") or expressions based on those fields (like your Month([inputdate]) where I suppose InputDate is a field of tblInput), to values.

This third argument must also take the form of a string, but there a few syntax rules:
- if the condition uses variable values, you cannot write a plain string which would be literally interpreted:

"((tblInput.UserID)=[forms]![frmCalender]![cboUser]))
"
For example, means Where tblInput.UserID is strictly equal to the string "[forms]![frmCalender]![cboUser])"
which obviously ill give no result.

Instead of
"((tblInput.UserID)= " & [forms]![frmCalender]![cboUser]
Meaning where tblInput.UserID is strictly equal to the value taken by forms![frmCalender]![cboUser]
This is because what Acces does in the above case is evaluate forms![frmCalender]![cboUser]
then concatenate the result with the rest of the string which gives if forms![frmCalender]![cboUser] is equal to 100:
"((tblInput.UserID)= 100"


- Other rules are that you must surround string variable values with simple quotes (the above example used a numeric ID, and therefore didn't need any) and date values with #.
So as an example of valid syntax using dates:
"[inputdate]= #" & Date() & "#"

- You must respect the same syntax as for a Where statement under SQL, with no unknow words or other syntax mistakes, adequate use of parenthesis etc.


Conclusion: In your example you must take the variable parts of your expression out of the double quotes, so that they can be properly evaluated by access, and concatenate them with the other parts of your string (tha are between quotes) using ampersands (&)

HTH

 

[skiphooper]

Hi Everyone,

I'm sorry it took so long to reply, but a family emergency took me out of state for a while.

First of all, I'd like to thank Rich Morrison and Alexandre
for their reply's. Without your help, I'd still be experimenting.

Below is the code I used.

' Monthly Report Count.
________________________________________________
=DCount("[UserId]","tblInput",
"Month([Inputdate]) = " & [Forms]![frmCalender].[month]
& " And [tblInput]![UserId] = " & [Forms]![frmCalender]![cboUser]
& " And Year([InputDate]) = " & [Forms]![frmCalender].[year])
________________________________________________

' DateRange Report
________________________________________________
=DCount("[UserId]","tblInput"," [tblInput]![UserId] = " & [Forms]![frmCalender]![cboUser] & " And [tblInput]![InputDate] Between " & "#" & [Forms]![frmChoose]![StartingDate] & "#" & " And " & "#" & [Forms]![frmChoose]![EndingDate] & "#")
________________________________________________

Everything works well up until I go to multiple pages, on the DateRange Report , then I get a #Name? error in the total field.

The dcount statement is in the report footer section.
_________________________________________

If I contain the same report to a single page , everything is ok.

What am I Missing ?

Thanks in advance

Skip
________________

 

[RichMorrison]

Skip,

Not sure I follow the whole problem.

It sounds like:
1) you are using DCount in a textbox on a report
2) it works when the report is one page
3) it does not work when the report is 2 or more pages.

I can't think of anything that would cause this problem.

Is the textbox in a foooter or header ?
What causes the report to expand to more that one page ?

RichM

 

[skiphooper]

Hi Rich,

Yes, I'm using the Dcount in a text box of a report.

It is in the report footer section.

As far as the report expanding, some people have accumulated
hundred's of days, either vacation, or sick.

We get 17 or 18 holiday's, 22 vacation days, 10 sick days, and
5 personal days. When some people get close to retirement, they
take from 6 months to a year as either sick or vacation.

In my testing, I just increased the size of the detail section, which
increased the report to 2 pages.

Just to see if I was crazy or not, I ran the test with the expanded
detail area, which caused the report to go to 2 pages.

I got the #Name? error..

I then closed up the detail section, ran the report, no problem.
Total was correct at bottom of page 1.


Skip
___________

 

[skiphooper]

Hi Everyone,

At first I thought it might be because of Access/2002. but
it does the same thing on Assess/2000.

I ran it again this morning, with the same results when it went
to the 2nd page.

When the report is only 1 page there is no problem.

Skip
________

 

[RichMorrison]

Skip,

I have no clue.

Suggest you repost the current problem in the "Reports" section.

Someone will probably figure it out.

RichM

 

[skiphooper]

Hi RichM,

Thanks for all your help.

Skip
_________