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

    Aggregate function in a text box (2000)

    The testo56 text box contained in the tab control on the startup form of the attached mdb has
    =DSum("OREtU","gruppo4","dgior between #01/01/2004# and [dgior]")
    as its control source.
    #01/01/2004# in the expression above represents the earliest date in the form record source and I'm trying to calculate that with the expression
    =DSum("OREtU","gruppo4","dgior between min([dgior]) and [dgior]")
    in the testo58 text box but I get #Error.
    Is it possible to achieve that?

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

    Re: Aggregate function in a text box (2000)

    You can't refer to the DGior control that way, and you can't use Min([DGior]) this way. You must put DGior outside the quotes, and use DMin instead of Min. Try this:

    =DSum("OREtU","gruppo4","dgior between #" & Format(DMin("dgior","Gruppo4"),"mm/dd/yyyy") & "# and #" & Format([dgior],"mm/dd/yyyy") & "#")

    Note: you must select a date in 2004 for it to work.

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

    Re: Aggregate function in a text box (2000)

    Thank you Hans,
    How can I avoid displaying #Error in the text box if I select a date on the calendar for which there's no underlying data?
    I've tried
    <font color=448800>=IIf(IsNull(DSum("OREtU","gruppo4","d gior between #" & Format(DMin("dgior","Gruppo4"),"mm/dd/yyyy") & "# and #" & Format([dgior],"mm/dd/yyyy") & "#")),"",DSum("OREtU","gruppo4","dgior between #" & Format(DMin("dgior","Gruppo4"),"mm/dd/yyyy") & "# and #" & Format([dgior],"mm/dd/yyyy") & "#"))</font color=448800>
    to no avail.

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

    Re: Aggregate function in a text box (2000)

    You should test whether DGior (the field linking the subform to the main form) is null:

    =IIf(IsNull([DGior]),Null,DSum("OREtU","gruppo4","dgior between #" & Format(DMin("dgior","Gruppo4"),"mm/dd/yyyy") & "# and #" & Format([dgior],"mm/dd/yyyy") & "#"))

Posting Permissions

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