Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format date headaches (Access 2000)

    I use a wonderful code in the AfterUpdate event of the combo box to search for dates
    by the way this is a part of a solution suggested by Francois from the present Forum.

    The code is the follwoing :
    Me.Call_Listing_Subform.Form.Filter = "[day] = #" & Format(Me.Combo11, "dd/mm/yyyy") & "#"
    It works perfectly provided i have a slash and not a do between the dates..
    I tried to change that into a dot like that:

    Me.Call_Listing_Subform.Form.Filter = "[day] = #" & Format(Me.Combo11, "dd.mm.yyyy") & "#"


    I also changed the date setting in the Regional options,in order to have the same format.
    However,when i use dots both in my code and in the Regional settings,i receive the message Syntax
    error.I suppose this is is something to do with the Regional settings,but is there ay way to avoid
    this prolem ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format date headaches (Access 2000)

    You should use US date format mm/dd/yyyy in your code, independently of your regional settings:

    Me.Call_Listing_Subform.Form.Filter = "[day] = #" & Format(Me.Combo11, "mm/dd/yyyy") & "#"

    It is also a good idea to set the Format property of the combo box to one of the date formats.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format date headaches (Access 2000)

    Dear Hans


    Thank you very much for your answer. I do get syntax error also when i use the US Date format:
    Me.Call_Listing_Subform.Form.Filter = "[day] = #" &
    Format(Me.Combo11, "mm/dd/yyyy") & "#"





    My regional setting are set to dot. : dd.MM.yyyy


    If i set them to a slash, then the code works,but i dont want to change the format the way we issue

    documents which are of the type 09.09.04 and not 09/09/04.
    I am attaching the form with this problem.I receive Syntax error on opening.
    If the date format problem is too difficult to solve,is there also another wat to seek data
    as shown in the form ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format date headaches (Access 2000)

    I changed the date format in regional settings to dd.mm.yyyy and got the same error you got - apparently VBA doesn't convert dates correctly. I could get around it by changing / to - in the code:

    Me.Call_Listing_Subform.Form.Filter = "[Den] = #" & Format(Me.Combo11, "mm-dd-yyyy") & "#"

    where Den was originally in Cyrillic (I had to get rid of the Cyrillic characters in order to work with the database).

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format date headaches (Access 2000)

    Dear Hans,

    Many,Many,Many thanks !! I open now my database smoothly and i thank you very much !!!

Posting Permissions

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