Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Access functions

    I have a record with six numeric fields (data type - integer) in a query.

    I have to find out the smallest number and then substract it from 35. How to write a funtion to that effect? Can you suggest some idea?

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    just sample for 3 fields, rewrite it for 6
    suppose we have fields f1, f2,f3
    there is the expression to get smallest field value
    IIF(f1<IIF(f2<f3,f2,f3),f1,IIF(f2<f3,f2,f3))
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Jan 2004
    Posts
    184

    Re: Access functions

    Create a recordset with the results from your query i.e.

    Recordset.Open "SELECT field1, field2, field3 FROM table1"

    Then loop through the results

    DO WHILE NOT Recordset.Eof

    if field1<field2 then
    intValue = Recordset.fields("field1").value
    else
    intValue = Recordset.fields("field2").value
    endif

    if field3<intValue then
    intValue = Recordset.fields("field3").value
    endif

    'At this point intValue has the smallest value of the 3 fields

    Loop

    Check this out:
    In abundance of water only the fool is thirsty. Bob Marley.

  4. #4
    Join Date
    Feb 2004
    Posts
    8

    Smile

    Originally posted by Kakha
    just sample for 3 fields, rewrite it for 6
    suppose we have fields f1, f2,f3
    there is the expression to get smallest field value
    IIF(f1<IIF(f2<f3,f2,f3),f1,IIF(f2<f3,f2,f3))
    Thanks. My problem solved. Construction of the statement very difficult to understand. Interesting.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •