Date Search (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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).

Reply | Quote

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

Reply | Quote

How about searching against:

iif(isnull(returndate),{today / today + 1 / dec 2020 / etc}, returndate) 

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…

Reply | Quote

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

Reply | Quote