DCOUNT Calculated Fields & Display on a Report

[ciskid]

Hi Folks

I am having some issues with the dcount function on calculated fields within a query.

I have a report where I have some custom expressions which dcount certain values from a query.

But when I preview the report the expressions show "Error"

See below which is the report in design view , but when previewed the red text just shows error



I have attached a simple version of my database on this post and also some screen shots.

Query in datasheet view :-




Query in design view :-




I log faults which have a dateopen filed and a dateclosed field

On my query I have an expression which says SLA: DateDiff("d",[dateopen],[dateclosed])

This will output a value of 0,1,2,3,4

0= closed <24hrs
1=closed in excatly 24 hrs
2=48 hrs
3=72 hrs
4=73 hrs or more

I then have some calculated fields within the same query which say the following :-

ONE: IIf([CASESTATUS]="CLOSED" And [SLA]=1,24,100)
So if SLA =1 then it will output the value 24

TWO: IIf([CASESTATUS]="CLOSED" And [SLA]=2,48,100)
So if SLA =2 then it will output the value 48

THREE: IIf([CASESTATUS]="CLOSED" And [SLA]=3,72,100)

So if SLA =3 then it will output the value 72

FOUR: IIf([CASESTATUS]="CLOSED" And [SLA]>3,73,100)
So if SLA >3 then it will output the value 73

So if I run the query all these expression work see link here :-



Click the picture above if its too small to read ?

Now if I build the dcount function on a report so that I can total how many calls have been closed in 24, 48 72 and greater tan 72 hours , the expression shows an error.

I cant seem to find where Im going wrong

Im thinking that the report is somehow treating my query fields as text instead of number s

Id appreciate any help anyone can give ?

 

[Matt Greatorex]

It looks like the search criteriaare in single quotes, within your DCount expression. e.g. "ONE = '23'"

Try without the quotes, if they are numbers.e.g. "ONE = 23"

 

[ciskid]

Still didnt work

Hi Matt

Just tried

=DCount("[Ref number]","QRYMAIN","[ONE] = 23")

And it made no difference.....arrghhhhhhhhhh

Any other ideas ?

Cheers

Jimmy