Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Search form with a date range

    I have a subform whose record source is a query. In the main form I have unbound text boxes for all of the fields I care to search on.



    The problem I am having is with the date range search

    The field in my query contains this criteria

    >=[Forms]![Mainform]![TEXT26] And <=[Forms]![Mainform]![TEXT24]

    The problem I have is that if you leave the text26 and text24 boxes blank the search returns no results.

    Why can the other text boxes on my search form be left blank and will then not filter records but the text boxes for the date range (text26 and text24) seem like they have to have a value in them?

    Is there a way to make these look up all records and only filter records if a date is entered?

    Thank you
    Any ideas will be appreciated.

  2. #2
    Join Date
    Feb 2009
    Posts
    16
    I am guessing this is because you are using a numeric expression and you have to have values for it to be interpreted.

    You can use this to check for null values and have it use 0 instead.

    Code:
     
    var strTextBox1 as String 'although you may want to use integer here
    var strTextBox2 as String
     
    If IsNull(Me.TextBox1Name) Then
       strTextBox1="0"
       Else strTextBox1=Me.TextBox1Name
    EndIf
     
    If IsNull(Me.TextBox2Name) Then
       strTextBox1="0"
       Else strTextBox1=Me.TextBox2Name
    EndIf
     
    'Your expression would change to:
    ...>=strTextBox1 And <=strTextBox2...
    FYI - my syntax isn't usually perfect so you will want to double check everything.

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

Similar Threads

  1. Replies: 0
    Last Post: 10-16-2008, 02:39 PM
  2. Refresh form search text box
    By oxicottin in forum Forms
    Replies: 2
    Last Post: 11-19-2007, 02:28 PM
  3. Continuous Range of Records
    By tigers in forum Access
    Replies: 0
    Last Post: 10-10-2007, 08:36 AM
  4. Search for record from a form
    By hcoakley in forum Forms
    Replies: 0
    Last Post: 11-26-2006, 11:23 AM
  5. Retrieving Week of Year in a date range
    By Korvega in forum Queries
    Replies: 2
    Last Post: 05-27-2006, 06:29 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