Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Value-fetching function in an update query (2000)

    Query7x in the attached mdb launches the EnerOm function. I thought the code line
    <font color=red> b = DFirst("K_CONT", tab2, "startdate <= #" & Format(gio, "mm/dd/yyyy") & "#")</font color=red>
    in it should fetch the value of the K_CONT field in the MazG2K table corresponding to StartDate = 15/03/2001 but it doesn't.
    How do I do it?

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

    Re: Value-fetching function in an update query (2000)

    The DFirst function returns the value from the first record it encounters that satisfies the WhereCondition "startdate <big><=</big> #" & Format(gio, "mm/dd/yyyy") & "#". Since all records satisfy this condition, you get b = 3000.

    (You don't see this in the result since a = 0 and c = 0)

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value-fetching function in an update query (2000)

    Hans,
    I've tried changing the EnerOm function as you see in the attached mdb but I still can't make the function return the value for K_CONT corresponding to the start date closest to the date in the query criteria.
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

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

    Re: Value-fetching function in an update query (2000)

    Does this do what you want? It first retrieves the date nearest gio, then uses that to retrieve the corresponding K_CONT.

    Function EnerOm(tab1, tab2, gio, orem)
    Dim a, b, c, d, lett, lett1
    lett = DLookup("LETTUR", tab1, "Giorno=#" & Format(gio, "mm-dd-yyyy") & "#")
    lett1 = DLookup("LETTUR1", tab1, "Giorno=#" & Format(gio, "mm-dd-yyyy") & "#")
    If IsNull(lett) Then
    EnerOm = 0
    Else
    a = lett - Nz(DLookup("inLetT", tab2, "startdate = #" & Format(gio, "mm/dd/yyyy") & "#"), _
    DLookup("LETTUR", tab1, "Giorno=#" & Format(gio - 1, "mm/dd/yyyy") & "#"))
    c = lett1 - Nz(DLookup("inLetA", tab2, "startdate = #" & Format(gio, "mm/dd/yyyy") & "#"), _
    DLookup("LETTUR1", tab1, "Giorno=#" & Format(gio - 1, "mm/dd/yyyy") & "#"))
    d = DMax("StartDate", tab2, "startdate <= #" & Format(gio, "mm/dd/yyyy") & "#")
    If IsNull(d) Then
    MsgBox "There is no date satisfying criteria."
    EnerOm = Null ' or 0?
    Else
    b = DLookup("K_CONT", tab2, "startdate = #" & Format(d, "mm/dd/yyyy") & "#")
    EnerOm = (a + Nz(c, 0)) * b
    End If
    If EnerOm = 0 And orem > 0 Then
    MsgBox "Warning, there are working hours with no energy.", vbOKOnly, tab1
    End If
    End If
    End Function

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value-fetching function in an update query (2000)

    Perfect! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Thank you Hans.

Posting Permissions

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