Dcount() and Dsum() problem

[jonnj]

I have an unbound field in a report.

I need to know why one of these works and the other doesnt.
its the same for both Dcount() and Dsum()

Any help is greatly appreciated.

Heres the two things.

This works:
=sum([tps]<[datecleared]<[forms].[fpenetration].[begin])

This gives the #error msg
=DSum(&quot;[tps]&quot;,&quot;[16j]&quot;,&quot;[datecleared]<[forms].[fpenetration].[begin]&quot

This works:
=DSum(&quot;[tps]&quot;,&quot;[16j]&quot;,&quot;[datecleared]<#10/5/02#&quot

It seems I have followed the field, domain and criteria exactly as I should but cannot get it t work

Thanks in Advance
John

 

[scriverb]

Try this:

=DSum(&quot;[tps]&quot;,&quot;[16j]&quot;,&quot;[datecleared]<#&quot; & [forms].[fpenetration].[begin] & &quot;#&quot

The way you were doing it was comparing a date field to a string representation of a date. The # signs convert the string to a date value. You could have used DateValue([forms].[fpenetration].[begin]) also.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jonnj]

Bob

Thanks for the reply. I understand what you said..let me add I did try this and got the #name error msg.

I believe in my haste to get an answer I left out the following: I dont know if this matters or not.

The report is based on a a parameter query from a begin date and ending date.

In the query [forms].f[penetration].[begin] is the starting date. My ojective to total not only the [tps} for period between the dates(I have that done ok) but also all the values in [tps] prior to the [forms].[fpenetration].[begin] date.

Thx again

 

[scriverb]

You said:
In the query [forms].f[penetration].[begin] is the starting date. My ojective to total not only the [tps} for period between the dates(I have that done ok) but also all the values in [tps] prior to the [forms].[fpenetration].[begin] date.

To select all in one statement all records within the begin and end dates plus all that are less than begin date, would be to select all that are less than or equal to the End Date. If this is what you are meaning then the following should sum up those records:

=DSum(&quot;[tps]&quot;,&quot;[16j]&quot;,&quot;[datecleared]<=#&quot; & [forms].[fpenetration].[end] & &quot;#&quot

In this statement [tps] is the field being summed in the table [16j] with the selection field being [datecleared] which is being compared to your form control [end].

If this does not meet your needs please post back. I may not be understanding your needs correctly. You should not be getting the #Name error.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???