Calculate lowest number of multiple fields

[Guest]

I am new to access and have hit a stop. I have a form that calculates unit
cost for an item from 14 different vendors and outputs it on the form. I now
want to look at all 14 calculations and put the lowest unit cost into a 15th
field. Please help. I have tried =min([field1]&[field2]) to no avail. Did
I also mention I am not very good with formulas.

 

[Guest]

Dim intI as Integer
Dim intMinUnitCost
intMinUnitCost=Me.Field1.Value
For intI = 2 to 14
If Me("field" & intI).Value<intMinUnitCost then
intMinUnitCost=Me("field" & intI).Value
End if
Next

Daniel

 

[Guest]

Hi Schon,

I'm just guessing, but it appears as if your database is not normalized
properly. Are you dedicating a column (field) for each vendor in your table?
If so, you have what we affectionately term an "Access spreadsheet". This
will be much more difficult to query versus a properly normalized database
structure. If this structure looks familiar, you should stop what you are
doing and head on over here to start reading about proper database design:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________