Results 1 to 7 of 7
  1. #1
    cewolf is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2013
    Posts
    5

    Date Search Box


    Greetings

    this is supposed t be simple but it is driving me crazy
    i searched all over for an answer until i gave up

    i have a form with a search box set to date
    and afterupdate i run this code

    Private Sub Plan_Date_Search_AfterUpdate()
    Me.Filter = " [PLANS RCVD DATE] = # " & [Plan_Date_Search] & "#"
    Me.FilterOn = True
    End Sub

    and it does not filter the date i am looking for ... all the records are filtered out !!!

    what am i doing wrong ?

    thanks in advance

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are you sure the date you re searching for is stored within your table in field [PLANS RCVD DATE]?
    What exactly gets returned?
    Have you tried Me.Requery after you assigned a filter value and set it ON?

  3. #3
    cewolf is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2013
    Posts
    5
    Yes .. the date i am looking for is in the records !!


    Click image for larger version. 

Name:	Capture.JPG 
Views:	11 
Size:	66.8 KB 
ID:	26202


    there are several fields that i can filter ...all work fine except for the date field
    what get returned is nothing ... all record are filtered out !!

    this is strange !!

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    when using # to convert your text date to a date type, the format must be us style - mm/dd/yyyy - so 3/10/14 is being interpreted as 10th March

    just as a quirk, if you look for 29/04/13 it will find it because there are not 29 months in the year so access assumes the format is dd/mm/yyyy

    try

    Me.Filter = " [PLANS RCVD DATE] = # " & format([Plan_Date_Search],"mm/dd/yyyy") & "#"

  5. #5
    cewolf is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2013
    Posts
    5
    so that is why ?!!
    I thought something was off ... but that never came to my mind ...

    thanks a lot

    ok ... the end user of this database is using mm/dd/yy format as the system date on his PC , i assume that this mean i should not apply the dd/mm/yy format or he will face the same problem
    correct

    Zanks

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if the system format is mm/dd/yyyy then probably not a problem, but personally I believe it is better to be safe.

    Date datatypes are just numbers which have special date formats, so the format is actually text or presented as text.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 6
    Last Post: 04-19-2016, 03:58 PM
  2. Replies: 4
    Last Post: 04-21-2015, 07:12 PM
  3. Search for date
    By azhar2006 in forum Forms
    Replies: 12
    Last Post: 07-10-2014, 10:02 AM
  4. Replies: 9
    Last Post: 11-25-2013, 04:33 PM
  5. Replies: 3
    Last Post: 09-11-2013, 09:49 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