Results 1 to 10 of 10
  1. #1
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215

    How to query if date field is left blank?


    I have a form with unbounded text boxes for searching purposes. A couple of these are date fields (where I check to see if certain records fall between a date range). How do I make it so that my query ignores the fact that they may be blank unbounded text boxes? I know the typical trick to get around this issue with regular text is to use the LIKE function, but for dates the same approach doesn't work.

    Any help is appreciated.

    Thanks!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a simple search set up by Datapig. See if it helps with your situation. If not, then post some code. Do you get an error?

    I see it doesn't have a date field...
    Can you give an example of a few searches where the one(s) with Dates are empty?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Give the query an alternate parameter:

    BETWEEN Nz([starttextbox], #1/1/1900#) AND Nz([endtextbox], #12/31/2900#)

    This will allow either or both textboxes to be left empty.
    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.

  4. #4
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    Thats neat June7. What does the Nz function do? I tried looking it up and didnt find much.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Nz() provides an alternate value if the input is Null.

    More about Null: http://allenbrowne.com/casu-12.html
    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.

  6. #6
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    That's a good tactic to use in this case. I like it. Thanks!

  7. #7
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    Yeh, this is to run a search based on an unbounded search form with text values. The same technique dosent work for dates - unfortunately.

  8. #8
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    Hi June7,

    This technique didn't work unfortunately. It queries all the records in which there IS indeed both an Announced Date AND an Effective Date. However, there may be Announced Dates and/or Effective Dates missing in my records. How do I account for this fact? Here is my query

    Click image for larger version. 

Name:	Query.jpg 
Views:	11 
Size:	72.5 KB 
ID:	17647

  9. #9
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    It turns out that you can do

    (BETWEEN Nz([starttextbox], #1/1/1900#) AND Nz([endtextbox], #12/31/2900#)) OR IS NULL

    in it's place, but you will get values for which there are null values. however, at least everything that you want to show up, shows up. i think this is as good as its going to get.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You mean the field is Null for some records? That does complicate. Try:

    BETWEEN Nz([starttextbox], #1/1/1900#) AND Nz([endtextbox], #12/31/2900#) Or Is Null
    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. warning message if field is left blank
    By dimoc in forum Access
    Replies: 3
    Last Post: 04-10-2014, 03:20 PM
  2. Removing blank spaces created by LEFT statement
    By Paintballlovr in forum Queries
    Replies: 16
    Last Post: 07-09-2013, 12:19 PM
  3. Replies: 4
    Last Post: 05-11-2013, 07:51 AM
  4. Replies: 7
    Last Post: 09-06-2012, 06:04 AM
  5. Replies: 1
    Last Post: 07-25-2011, 09:57 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