Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mysterious Changing Date Format! (Access 2000 VBA)

    Can someone explain something for me? I have a table where a date (FromDate) is defined as Date-Time/Short Date. Form1 displays that table (Continuous Form) and each date displays ok. If a row on Form 1 is clicked, the OnClick routine states:
    datFromDate=Me.FromDate ' datFromDate is a Public variable in a Module, defined as Date
    DoCmd.OpenForm "frmForm2"

    Form2 (when it appears) displays FromDate (same table) correctly too, BUT it only displays one record, and that record is selected from the table by coding the following in the form's OnActivate event:
    Me.Filter = "FromDate = " & datFromDate
    Me.FilterOn = True

    The puzzling thing for me is that this doesn't work! UNLESS I code 'Me.Filter = "FromDate = " & Format(datFromDate, "mm/dd/yyyy")'. If I don't, the form shows with no record displayed, i.e. found.
    As I'm in the UK, Short Date to us means dd/mm/yyyy, so why does the filter seem to require the date to be formatted incorrectly (mm/dd/yyyy) to correctly filter a table field defined as Short Date? What am I missing here? It makes no difference if I code 'datFromDate = Format(Me.FromDate,"dd/mm/yyyy")' - I still get no result unless I format datFromDate as mm/dd/yyyy in the filter!

    Any explanation for this would be very welcome. I spent most of today trying to work out why Form2 displayed some records and not others (because the day and month values happened to be the same!).

    Thanks.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mysterious Changing Date Format! (Access 2000

    Just as strings need to have quotes (") around them, dates need to have hashes (#) when used in SQL statements / conditions etc. So if you use:

    Me.Filter = "FromDate = #" & datFromDate & "#"

    instead, it should work ok. Otherwise, if the date is 14/02/2006 (say), Access will try and calculate 14 divided by 2 divided by 2006.
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mysterious Changing Date Format! (Access 2000

    Eww! My mistake! I didn't put the # in the original post, but I DO put it in the VBA code, so 'Me.Filter = "FromDate = #" & datFromDate & "#"' is what I code - and again unless I code Format(datFromDate,"mm/dd/yyyy") it doesn't work.

    Without the # marks, it wouldn't work even if the day and month values were the same - which it does!

    Thanks anyway.

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

    Re: Mysterious Changing Date Format! (Access 2000 VBA)

    Waggers already commented on the mssing # signs.

    The filter is processed by SQL, and SQL is strictly US oriented: literal numbers must use the point as decimal separator, and dates must be in mm/dd/yy or mm/dd/yyyy format, regardless of your regional settings. The same goes for literal values in VBA code:

    Dim dteDate As Date
    dteDate = #01/02/2006#

    would result in dteDate being set to 2 January 2006 instead of 1 February 2006, whether you're on a US system or on a UK system.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mysterious Changing Date Format! (Access 2000 VBA)

    Thanks, Hans. You learn something new every day! One day, I really will get some proper training.......

  6. #6
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mysterious Changing Date Format! (Access 2000

    Sorry Keith, I should have read your post more thoroughly. If you open the forms in Design View and view the properties of the FromDate fields, is anything in the Format property? It may be worthwhile specifying dd/mm/yyyy as a custom format rather than using the set "short date" option, and ensuring that the table and both forms are using the same format.

    Other than that, <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>...
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mysterious Changing Date Format! (Access 2000

    Ok, scrap that, I've just seen HansV's reply!
    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I didn't know that about SQL either. Clearly worth remembering!
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mysterious Changing Date Format! (Access 2000

    Thanks for that. Actually, Hans has already explained that the filter is processed by SQL and is ALWAYS US format, so any manipulation in Short Date is useless! At least I found a workround! Thanks for taking the time though.

Posting Permissions

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