Results 1 to 2 of 2
  1. #1
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    Dlookup as Control Source

    I have a query that sums a PaymentAmount field (2011 only). This is the query SQL:
    SELECT Sum(tblPayments.PaymentAmount) AS SumOfPaymentAmount
    FROM tblPayments
    HAVING (((tblPayments.PaymentDate) Like "*2011"));
    I created a form where I want to summarize certain information. I want to include a field called YTDpmts that gives me the result of the above query. I made the control source of the field: =DLookUp("[SumOfPaymentAmount]","zqrySumPmts") and it gives me #Error as the value of the field. I would love to just add the SQL statement above as the control source, but that doesn't work either.

    Should I be using DSUM instead? I tried using: =DSum("[PaymentAmount]","tblPayments",Year([PaymentDate])=Year(Now())) but I get #Name?.

    Please help.



    Thanks in advance.
    Al
    Last edited by alsoto; 08-28-2011 at 06:49 PM. Reason: add'l question

  2. #2
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67
    I got this to work:

    =DSum("[PaymentAmount]","tblPayments","[PaymentDate] between #01/01/2011# and #12/31/2011#")

    How do I replace between #01/01/2011# and #12/31/2011#" with syntax that will give me only payments where Year of [PaymentDate] = Year(Now()?

    Is DSUM the best solution?

    Thanks again,
    Al
    Last edited by alsoto; 08-28-2011 at 07:07 PM. Reason: grammar

Please reply to this thread with any new information or opinions.

Similar Threads

  1. IIF statement in control source of text box
    By LilMissAttack in forum Reports
    Replies: 11
    Last Post: 08-18-2011, 10:02 PM
  2. Charts within subform in a control source
    By GraemeG in forum Forms
    Replies: 0
    Last Post: 06-03-2011, 04:38 AM
  3. control source
    By nashr1928 in forum Forms
    Replies: 5
    Last Post: 03-12-2011, 09:31 PM
  4. Control Source
    By sarah54 in forum Access
    Replies: 1
    Last Post: 03-07-2011, 09:00 PM
  5. Replies: 2
    Last Post: 07-30-2009, 08:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums