Forum Home
Press F1
Thread ID: 12701	2001-11-07 02:59:00	Need VBA / ACC2000 Help	Guest (0)	Press F1

Post ID	Timestamp	Content	User
23827	2001-11-07 02:59:00	MS Access2000 / Win98se / 62 yr old, bit better than novice struggling with VBA etc. I'm involved in a charity org. running a fishing contest at Easter. Last 4 yrs I've managed using Works DB, however gets very messy at times with the limits on Queries, Reports, & work arounds to print Labels (Draw Tickets) when DB has Formulae in it (stalls the merge)etc. I've now got a long way down the track with Access only to hit a wall with the Data output to Reports etc. Access has no Built in Cross Field Aggregate Functions eg Max(Field1,Field2,---) as Works has say MS. Their Knowledge Base answer to this is a Custom Function in an Alias Field of a Query, eg. MaxField: Maximum(Field1,Field2,---) Using code Function Maximum(ParamArray FieldArray() As Variant) 'Declare the two local variables. Dim N As Integer Dim currentVal As Variant 'Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) 'Cycle through each value from the row to find the largest For N = 0 To UBound(FieldArray) If FieldArray(N) > currentVal Then currentVal = FieldArray(N) End If Next N 'Return the maximum value found. Maximum = currentVal End Function This works fine (Lists the Max. value across the fields in each Record) except, the Data in the new field has lost the 'single,fixed,3' (Kg/grams) formatting of the original Data, and won't Sort (Descending) properly to show the o/all Max val. and who weighed it in, nor will Max('the Alias' MaxField) find it in a Report control of course. So, seems to me the Data needs converting from 'Variant' to at least 'Single' (prefer 3 place fixed?) before it is returned from the code? but I'm not up that yet, assuming I'm on the right track anyway. Please can someone set me straight. This is not a one off, can work around thing, Over two days, four fish species each day and o/all summary Report to do, I'll want to use one of the aggregate functions on maybe sixty occasions, and I'm only starting the Min/Max area! Avg, Sum & Count to come. Thanks for getting this far, hope you can help Don.	Guest (0)
1