How do I 'add' results produced by the 'greatest' macro?

[Eric]

Hi!

I got this 'Greatest' module from the newsgroup a month or
two ago <thanks!> and it works fine ... until now that is.
Unfortunately, the numbers it produces don't seem to
be 'addable'; rather then seem to simply concatenate
together..

For example (See Table below), say I have a column in a
query that uses the greatest module to calculate the
greatest value across a number of other columns (call it
expression A) and it produces '12' for a particular
record, and another use of the greatest module for a
different set of columns (call it expression B) produces a
result of '31' for that record, then when I try to 'add' A
and B, I don't get 43 in that record - instead I
get '1231' i.e. the concatenation of 12 and 31.

ID A:Greatest([C],[D]) B:Greatest([Y],[Z]) SumA&B:[A]+
1 12 31 1231
2 45 7 457
3 1234 5678 1345678

How do I get around this?

The 'Greatest' module I am currently using is below:

========
Option Compare Database

Public Function Greatest(ParamArray var() As Variant) As
Variant

Dim max As Variant
Dim i As Integer

' Initialize
max = var(LBound(var))
For i = LBound(var) To UBound(var)
If var(i) > max Then max = var(i)
Next i

Greatest = max

End Function
==========

What changes do I need to make either to:
a) the greatest module or
b) to my 'adding' [a]+ expression,

to allow me to trully ADD data produced by various uses of
this module, as opposed to simply concatenating?

Thanks in advance,

Eric

 

[Duane Hookom]

Your function is returning a variant. If you can be sure there will always
be a number returned, you could change the function's data type to " As
Double".
Or you can wrap your expressions in Val(A) + Val(B)

 

[Eric]

Thanks Duane!

Eric

-----Original Message-----
Your function is returning a variant. If you can be sure there will always
be a number returned, you could change the function's data type to " As
Double".
Or you can wrap your expressions in Val(A) + Val(B)

--
Duane Hookom
MS Access MVP

Hi!

I got this 'Greatest' module from the newsgroup a month or
two ago <thanks!> and it works fine ... until now that is.
Unfortunately, the numbers it produces don't seem to
be 'addable'; rather then seem to simply concatenate
together..

For example (See Table below), say I have a column in a
query that uses the greatest module to calculate the
greatest value across a number of other columns (call it
expression A) and it produces '12' for a particular
record, and another use of the greatest module for a
different set of columns (call it expression B) produces a
result of '31' for that record, then when I try to 'add' A
and B, I don't get 43 in that record - instead I
get '1231' i.e. the concatenation of 12 and 31.

ID A:Greatest([C],[D]) B:Greatest([Y],[Z]) SumA&B:[A]+
1 12 31 1231
2 45 7 457
3 1234 5678 1345678

How do I get around this?

The 'Greatest' module I am currently using is below:

========
Option Compare Database

Public Function Greatest(ParamArray var() As Variant) As
Variant

Dim max As Variant
Dim i As Integer

' Initialize
max = var(LBound(var))
For i = LBound(var) To UBound(var)
If var(i) > max Then max = var(i)
Next i

Greatest = max

End Function
==========

What changes do I need to make either to:
a) the greatest module or
b) to my 'adding' [a]+ expression,

to allow me to trully ADD data produced by various uses of
this module, as opposed to simply concatenating?

Thanks in advance,

Eric

.