Sum() Problem 1

[barrylowe]

I have a bit of a problem with a field on one of my reports.

I have created a report to provide Totals figures for various pieces of information relating to drug prescriptions in my datatabase.

All of the textboxes used in the report appear in the report footer as I am looking at totals information rahter than individual records.

I need to return the maximum and minimum doasge values for the records in the recordsource. The maximum values work fine using the following code in the Control Source of the text box:

=Max(IIf([Area]="NameofArea",[Mgs1],0))

Where Mgs1 is the fields containing the dosage value.

I then used the following code for the minimum value:

=Min(IIf([Area]="NameofArea",[Mgs1],0))

The problem with this is that it returns values of zero and what I actually need is the lowest value that is not zero. I then tried the following code but it made no difference:

=Min(IIf([Area]="NameofArea" And [Mgs1]>0,[Mgs1],0))

Can anyone see what I'm doing wrong?

 

[dhookom]

Try:
=Min(IIf([Area]="NameofArea",[Mgs1],9999999999))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[barrylowe]

No, that didn't make any difference.

Basically when I run the base query there are 32 records. If you order the results ascending by the Mgs1 field, the Mgs1 values are as follows:

0,0,0,4,14,20,30,30 ......... ,80

I want it return the 4 and not the zero.

 

[barrylowe]

My apologies, your code did in fact work.

Can you explain what the 9999999999 did, as I am a bit unsure?

Thanks,

 

[dhookom]

If the number was 0 then any non-nameofarea record would be the min. To make sure that non-nameofarea records aren't the min, treat them as very large numbers.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]