Results 1 to 5 of 5
  1. #1
    Mtyetti is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011

    'Between' function; Show all records if left blank

    I am creating a query which uses 2 unbound text boxes to populate a Between function for 2 date fields. If I fill in the dates, it returns the corresponding data. If I leave them blank, however, it returns no records.

    Is there an easy way to tweak the query to return all records if left blank???


  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Southern California
    Is this the only criteria you're using in the query?

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    Try the Nz() function around each text box reference, using appropriate dates in each (less than and greater than in the data).
    Paul (wino moderator)
    MS Access MVP 2007-2019

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Right, use some extreme dates guaranteed to be outside the range of the dataset:

    BETWEEN Nz([box1],#1/1/1900#) AND Nz([box2],#12/31/2900#)
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Mtyetti is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Thank you, PBaldy and June7. That did the trick!

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

Similar Threads

  1. Replies: 7
    Last Post: 09-06-2012, 06:04 AM
  2. mid,left function
    By revned12 in forum Queries
    Replies: 2
    Last Post: 07-06-2012, 12:09 AM
  3. Update with left function
    By yerromnitsuj in forum Access
    Replies: 3
    Last Post: 01-09-2012, 03:41 PM
  4. Replies: 1
    Last Post: 07-25-2011, 09:57 AM
  5. Replies: 3
    Last Post: 02-02-2011, 01:00 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 - Senior Forums