Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    iif...where (2003)

    I have the following in a query:
    MinPENE: IIf(Min(qryHasHubMeterReading!PENE1)<Min(qryHasHub MeterReading!PENE2),Min(qryHasHubMeterReading!PENE 1),Min(qryHasHubMeterReading!PENE2))

    I have had to adjust the query qryHasHubMeterReading so that it will now allow a zero (0) in the field of PENE1. I want to know how to include a WHERE in the above expression so that the MIN of PENE1 will be the lowest number bigger than 0, but do not want to exclude records that include 0.

    thanks,
    jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: iif...where (2003)

    Sorry, I don't understand. If you want to return "the lowest number bigger than 0", doesn't that mean that you exclude the 0 values?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: iif...where (2003)

    I need qryHasHubMeterReading to pull records that have a PENE1 of 0, but I need to to know what the lowest PENE1 (MinPENE1) other than 0.

    thanks,
    jackal

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: iif...where (2003)

    You can use the expression

    DMin("PENE1","qryHasHubMeterReading","PENE1>0")

    to retrieve the minimum of all values greater than 0.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: iif...where (2003)

    Thanks. Is there a way that once a specific crieteria, say equipment # 43-040 & Job 4211, that the DMin will work on only this criteria?

    thanks,
    jackal

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: iif...where (2003)

    The 3rd argument of DMin is the WhereCondition. You can expand this:
    <code>
    DMin("PENE1","qryHasHubMeterReading","PENE1>0 And [Equipment#]='43-040' And [Job]=4211")
    </code>
    Substitute the correct field names. Numeric values can be used directly, and text values must be enclosed in single quotes.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: iif...where (2003)

    Can the numeric & text values that are needed for each time the query runs, be attached to the criteria on the field JobNumber? In the criteria field on JobNumber is [what job?], then user enters a job number.

    thanks,
    jackal

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: iif...where (2003)

    Yes, but you have to put the parameters outside the quotes. For example:
    <code>
    DMin("PENE1","qryHasHubMeterReading","PENE1>0 And [Equipment#]='43-040' And [JobNumber]=" & [What Job])
    </code>
    (DMin itself doesn't know how to handle parameters)

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: iif...where (2003)

    Thanks for your help Hans!! Here is my final solution:

    MinPENE: DMin("PENE1","qryHasHubMeterReading","PENE1>0 And [Equip]='" & [WhatEquip?] & "' And [JobNumber]='" & [WhatJob?] & "' ")

    With this I can use 2 parameters.

    Thanks again.
    jackal

Posting Permissions

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