Statement Help

[Gootz11]

I need to run a statement that looks at 3 colums and then creates a new column by choosing the greatest number out of three colums.
exmpl

ColumnA ColumnB ColumnC Newcolum
12 15 20 20
10 14 09 14

I been playing with a IIF statement but can't seem to get it right.
Thanks for your help

 

[dcx693]

You can write an The IIF function should work, you just have to be careful to get the syntax right. Post your attempt at writing it and we can help with syntax.

It would be very easy if Access had a Max function, but it doesn't. Alternatively, if you've got Excel, you can write yourself a custom function with a call to Excel to pick out the max for you.

Or you can even write your own Max() function in Access using VBA. You "only" have three columns. Just pass the three values in your query like this: MyMaxFunction([ColumnA],[ColumnB],[ColumnC]) and your custom function will pick out the max for you.

 

[Gootz11]

dcx,
thanks for your reply, i tried your statement and i get an error meassage stating undefined function? any other Ideas?

 

[dcx693]

If you tried that MyMaxFunction that I suggested, you will get an error unless yo actually go and write the function!

Have you ever written a function? Have any idea about it?

 

[Gootz11]

no, guess i havent, i was writing it just like any other statement within the design view of the query

 

[dcx693]

Here, go create a new module in the Access database. Copy and paste the code below into it. Save the module, doesn't matter what you call it (just don't call it MyMaxFunction - it will be confusing).

Function MyMaxFunction(ColumnA, ColumnB, ColumnC) As Double

MyMaxFunction = ColumnA
If ColumnB > MyMaxFunction Then
MyMaxFunction = ColumnB
End If
If ColumnC > MyMaxFunction Then
MyMaxFunction = ColumnC
End If

End Function

Then go and run your query with that call to MyMaxFunction in it. Note that the function as I wrote it only works for three columns. It's easy to generalize it for more inputs. Also, I wrote it in 30 seconds, so there's no error checking. Do not try to send text values through it. It will fail.

 

[Gootz11]

I guess my last question would be how to run a query while including the satatement in the module, i never worked with modules before so i'm really lost when it comes to VB
Thanks Again

 

[Gootz11]

never mind, i figured it out,
thanks for your helped, it worked great