Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dsum, DLookup, DCount (2003)

    Could someone explain the syntax for using Dsum, Dlookup and Dcount in a query. I just don't know what goes where and why. Thanks for any help you can provide.

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

    Re: Dsum, DLookup, DCount (2003)

    You use these functions if you want to include values from a table or query that is not among the tables/queries that your query is based on. They all have the same syntax, which can take two forms:

    DSum("Field", "TableOrQuery") returns the sum of the values of Field in TableOrQuery. If you use this in a query, you'll get the same value in all records returned by the query.

    DSum("Field", "TableOrQuery", "ID = " & [ID]) returns the sum of the values of Field in TableOrQuery for the records whose ID is the same as that of ID in the query in which you use this expression.

    The above assumes that ID is a number field. For a text field, you'd use

    DSum("Field", "TableOrQuery", "LastName = " & Chr(34) & [LastName] & Chr(34))

    and for a date field:

    DSum("Field", "TableOrQuery", "DateField = #" & [DateField] & "#")

    If you search this forum for DSum or one of the other functions, you'll find examples of their use.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dsum, DLookup, DCount (2003)

    I was looking at this forum for examples and I wasn't sure how to read the examples for instance with this one:

    RunningAmount: Val(DSum("SumOfAmount","qryJobExtByPED","Job=" & Chr(34) & [Job] & Chr(34) & " AND Extra=" & Chr(34) & [Extra] & Chr(34) & " AND Period_End_Date<=#" & Format([Period_End_Date],"mm/dd/yyyy") & "#"))

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

    Re: Dsum, DLookup, DCount (2003)

    In that example, Val is used to convert the result of DSum to a number (the D-functions tend to return text values).
    The syntax for DSum is the second one I mentioned:

    DSum("SumOfAmount","qryJobExtByPED",condition)

    This returns the sum of the field SumOfAmount in the query qryJobExtByPED for records satisfying the condition. In this case, the condition is a composite one:

    Job=" & Chr(34) & [Job] & Chr(34) & " AND Extra=" & Chr(34) & [Extra] & Chr(34) & " AND Period_End_Date<=#" & Format([Period_End_Date],"mm/dd/yyyy") & "#"

    Three conditions are combined with AND:

    Job=" & Chr(34) & [Job] & Chr(34) says that the value of the text field Job should be equal to that of Job in the current record.

    Extra=" & Chr(34) & [Extra] & Chr(34) says that the value of the text field Extra should be equal to that of Extra in the current record.

    Period_End_Date<=#" & Format([Period_End_Date],"mm/dd/yyyy") & "#" says that the value of the date field Period_End_Date is less than or equal to that of Period_End_Date in the current record.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dsum, DLookup, DCount (2003)

    Thank you so much for taking the time to explain.

Posting Permissions

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