Results 1 to 3 of 3
  1. #1
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12

    Problem with AND in DSum() Criteria

    Well, I have made a lot of progress lately in figuring out all the quotes in SQL, VBA and Functions like DSum(). However this one has me stumped.

    If I write the DSum with one set of Criteria it works fine, for each set. When I try and add the AND to get them both in one statement I get "Expected Expression".

    Here are some results from the immediate Window. The first two are each of the criteria individually, along with results (Which are correct). The second two are using an AND statement to include them both. I added brackets around each of the expressions in the last of these two (Desperation!).

    ? DSum("CallQuantity","tblCCTransaction","EquityID = " & Me.cboTicker)


    -100


    ?DSum("CallQuantity","tblCCTransaction","CallExpir e < #" & Date() & "#")
    -100


    ?DSum("CallQuantity","tblCCTransaction","(EquityID = " & Me.cboTicker) & AND ("CallExpire < #" & Date() & "#")) -Gives "Expected Expression"


    ?DSum("CallQuantity","tblCCTransaction","EquityID = " & Me.cboTicker & AND "CallExpire < #" & Date() & "#") -Gives "Expected Expression"


    I have looked over several threads here but have not found the answer. Any help here would be greatly appreciated as I am swimming at this point.

    Thanks

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The AND needs to be inside the quote
    try:
    Code:
    ?DSum("CallQuantity","tblCCTransaction","EquityID = " & Me.cboTicker & " AND CallExpire < #" & Date() & "#")
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Bingo! Works. I can't believe I didn't think of that but thanks for the assistance.

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

Similar Threads

  1. Dsum criteria
    By Manuel Ferreira in forum Access
    Replies: 4
    Last Post: 09-12-2014, 09:54 AM
  2. dsum with criteria Not like 38,39,40,41
    By sdel_nevo in forum Programming
    Replies: 3
    Last Post: 06-13-2013, 03:23 PM
  3. Dsum criteria
    By bdaniel in forum Queries
    Replies: 4
    Last Post: 11-27-2011, 03:41 PM
  4. Dsum criteria problem
    By leonhuynh2006 in forum Queries
    Replies: 3
    Last Post: 10-04-2011, 03:18 AM
  5. Replies: 1
    Last Post: 05-05-2010, 01:54 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