Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Search (Access 97)

    Ok, here's a tough one that has all the programmers here scratching their heads...

    i have a key sign out table that has a date field (let's just say this date is for when a key was returned)

    i have a form that is used to enter search criteria, one section of which searches on that date field. i have 2 text boxes in the form for dates, one is "after" (search for dates after the one entered) and one is "before" (search for dates before the one entered). if both are entered, it searches between the "after" and the "before dates. if no dates are entered (searching on something other than the date field) it enters the date "12/30/1899" (the beginning of time according to Access) and "Now()" to find all dates between the beginning of time and today (basically all dates). this works perfect for records where there is a date in this date field. however, this date field will have many (in fact most) records where this date is blank (because all keys will not have been returned yet). this search method will only work when there are dates in the field, but if date search criteria is not entered (wanting to retrieve all records) it will eliminate all records where the date is blank.

    i have used this method when searching a text field. if the field has blanks, i use the Nz(field) function to find all records when searching for "*" in that field. this obviously will not work for dates, because the date "0" is not between 12/30/1899 and now.

    please help!
    thanks in advance

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access 97)

    Nz takes two arguments, the second of which is optional. So, you could say something like Nz([field],#12/30/1899#) if you wanted to do it that way (I just wouldn't bother doing a search if neither "before" nor "after" was filled in - just return everything).

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access 97)

    thanks for your reply

    your solution works - yay!. however, using the nz function changes the date format to a string. this means that instead of finding dates between 12/30/1899 and 3/12/2002, it finds text strings between the text string "12/30/1899" and "3/12/2002". using format and changing it back to "short date" does not work.

    please help!
    thanks again

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Search (Access 97)

    How about searching against:
    <pre>iif(isnull(returndate),{today / today + 1 / dec 2020 / etc}, returndate) </pre>

    That way if you want to exclude keys not returned from this or another query you can use a default return date
    of tomorrow or sometime far in the future...

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access 97)

    hey, thanks for the help. looks like it is working. here is the syntax i used.

    DateReturnedSearch: IIf(IsNull([tblKeyRequisitions].[DateReturned]),(Now()+1),[tblKeyRequisitions].[DateReturned])
    this will always return 1 day ahead of today. thanks alot

Posting Permissions

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