Results 1 to 4 of 4
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    dsum between dates

    Hi Guys



    I am having real trouble with a Dsum on a form that runs on a forms onload event.

    what I am trying to do is to sum all the payments from the table tblAccountTransactions" where the "TransType" is "money in" and the "EntryDate" is between the dates in the form "frmOpenReport" start and End dates

    the code i have is below

    Code:
        Dim StartDate As Date
        Dim EndDate As Date
               
        StartDate = [Forms]![frmOpenReport]![StartDateTxtBox]  
        EndDate = [Forms]![frmOpenReport]![EndDateTxtBox]   
       
         
        Me.IncomeTXTBox = Nz(DSum("[Payment]", "[tblAccountTransactions]", "[Transtype]='Money In'" And [EntryDate] >="#" & StartDate & "#" And [EntryDate] <= "#" & EndDate & "#"), 0)
    im getting the Run-time error 2465 cant find the feild '|1' referred to in your expression

    any help would be wonderfull,

    many thanks

    Steve

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    too many quotes, try:
    Me.IncomeTXTBox = Nz(DSum("[Payment]", "[tblAccountTransactions]", "[Transtype]='Money In' And [EntryDate] between #" & StartDate & "# And #" & EndDate & "#"))

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Id be tempted to write

    Code:
    debug.print Nz(DSum("[Payment]", "[tblAccountTransactions]", "[Transtype]='Money In'" And [EntryDate] >="#" & StartDate & "#" And [EntryDate] <= "#" & EndDate & "#"), 0)
    to see what's returned in the immediate window

  4. #4
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ranman256

    you are a star, that worked

    forgot about the between

    many many thanks

    steve

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

Similar Threads

  1. DSum and dates...I need help!
    By CBaigent in forum Queries
    Replies: 4
    Last Post: 05-10-2016, 05:15 PM
  2. Dsum function using mulitple criteria (specific: dates)
    By QA_Compliance_Advisor in forum Programming
    Replies: 10
    Last Post: 07-08-2015, 10:29 AM
  3. Replies: 1
    Last Post: 06-26-2015, 05:05 AM
  4. Replies: 2
    Last Post: 06-17-2013, 11:24 AM
  5. filter dsum between dates on a form
    By sdel_nevo in forum Forms
    Replies: 5
    Last Post: 06-03-2013, 03:07 PM

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