Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Query (2000)

    I am stumped on this one.

    I have a 'search' form where users can select/enter criteria to search on. Five of them are date to
    and from boxes for 5 different date fields.

    When the user types in a from/to date the query is simple, I use a between forms!frmMenuInquiry!txtFrom
    and to syntax. This works. However, how do I get it to work if the user doesn't enter a date? ie a null
    entry means find everything. One way would be to create a query for every eventuality for the form and
    use a Select Case statement to see if a date has been entered in the date field. That would be ok for
    one maybe two dates, but 5 results in over 20 different possible combinations.

    Does this make much sense?

  2. #2
    New Lounger
    Join Date
    Sep 2001
    Location
    Portland, Oregon, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2000)

    This may not be the best way, but you could use If Then Else statements like this:

    If IsNull(txtFrom) Then
    txtFrom.Value = #01/01/1901#
    End If

    If IsNull(txtTo) Then
    txtTo.Value = Date
    End If

    You can set the From date to whatever would be the earliest possible date or just use the 1901 date and then Date would give all up until today.

    Bob Larson

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2000)

    Thanks for the reply Bob. I have something like that already in place, however it assumes that there will always be an entry for the date field in every record. I have date fields which may not be completed so if the user does not place a to/from date in the search form they are assuming that all records will be found for this field as no criteria is present. Using the method you suggested would only find records with actual entries. I think....

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Complex Query (2000)

    If it's the TO date, just use Nz(forms!frmMenuInquiry!txtTo,Date()), which will use the current date if they don't enter a TO date. If they don't enter a FROM date, you have to decide how you want to handle it. I generally test for a FROM date and a TO date. If they entered only a FROM date, I use >= the FROM date. If they entered only a TO date or neither date, I use <= the TO date, using the first trick I described to generate a TO date if I need to.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Dec 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2000)

    Charlotte,

    This will achieve the same thing, but will not find a record that is blank if the user does not type in a date which they might not want to do. e.g.
    Record# Date
    1 01/01/2001
    2 02/01/2001
    3 03/01/2001
    4 Empty
    5 04/01/2001
    6 05/01/2001
    7 06/01/2001

    If txtfrom is blank my function will make txtfrom = 1/1/1900 and txtto = 07/09/2001this will
    find records 1,2,3,5,6,7 but *not* record 4. I want the query to say if the txtfrom and txtto are blank
    then find all...

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2000)

    Why don't you build a selection criteria :
    <pre>If IsNull(FromDate) and IsNull(ToDate) Then
    strCriteria = "[RecordDate] Like '*'"
    Else
    strCriteria = "[RecordDate] Between #" & FromDate & "# and #" & ToDate & "#"
    End If
    DoCmd.OpenReport "YourReport" , , , strCriteria
    </pre>

    Francois

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Complex Query (2000)

    Sorry, but it isn't clear whether your missing dates are in the table data or on your form. Which one are you talking about? I thought you were saying that they might not type in a TO or FROM date. From your post, it looks like that wasn't your question at all.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Dec 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2000)

    Sorry for the confusion, I am concerned about trying to find datefields in the table that are blank. I can handle the question if the user doesn't enter dates in the query form - that is relatively straight forward. Hope that makes sense.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Complex Query (2000)

    Then all you need to do is use something like "Between ThisDate And ThatDate or Is Null" in the criteria line. Actually, as I think about it, are these fields actually null? Make sure you format the field in the query as a short date to eliminate any stray times you might get otherwise.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •