Results 1 to 4 of 4
  1. #1
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44

    DCOUNT with LIKE and Range

    This one has totally stumped me, I have Locations down a certain Aisle (JY) and im counting the number of pallets as below.



    Code:
    =DCount("Customer","qryTableMerge","Customer='WRIGLEY' AND Location LIKE 'JY*'")
    The locations go from JY025A to JY138F but I need to count the number of pallets from JY025A to JY084F and a separate label will count from JY085A to JY138F.

    So I need the LIKE Value to pickup the JY Aisle and then <> the values above (Hope I explained that well)

    For the life of me I cant think of the best way!!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So you need two calcs. Expand the WHERE clause.

    AND Location <= 'JY084F'

    AND Location >= 'JY085A'

    Or eliminate the LIKE criteria and use BETWEEN AND.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Wow worked a treat, so simple too

    =DCount("Customer","qryTableMerge","Customer='WRIG LEY' AND Location >= 'JY025A' AND Location <= 'JY084F'")

    Im not familiar with BETWEEN but this works :-)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    AND Location BETWEEN 'JY025A' AND 'JY084F'
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 10-06-2017, 01:50 PM
  2. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  3. dCount by date range
    By Ruegen in forum Programming
    Replies: 4
    Last Post: 10-09-2013, 09:48 PM
  4. Replies: 1
    Last Post: 08-08-2012, 02:02 PM
  5. Using a date range with Dcount function
    By mleberso in forum Reports
    Replies: 4
    Last Post: 06-17-2011, 08:56 AM

Tags for this Thread

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