Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: Problem with date format (half Access, half VB)

    Hi all

    I'm having some trouble getting VB to recognise my date format. First, I'll explain what I'm doing and how I'm doing it, then I'll show a screenshot of what I did.

    Right, I have a table with a date field in the format dd/mm/yyyy (which is also the same as my System Short Date. Dates are entered in that manner, no problem. Query runs off from the table, same dd/mm/yyyy format.

    Now I created a form, with an unbound date field to accept dd/mm/yyyy format as well. This field acts as a filter, so that users input a date in there, and (hopefully) they get a filtered result from my query.

    I'm using VB 6.5 that came with my Access 2007 to run this code:

    Code:
    Private Sub Btn_RenMtr_Click()
    
      DoCmd.OpenQuery "Expiry Query", acViewNormal, acEdit
      DoCmd.ApplyFilter , "Expiry = #" & Forms!View!Date1 & "#"
    
    End Sub
    And then after all that, I get filtered results that messed up my date. Instead of getting 01/03/2010 (1st of March), I'm getting 03/01/2010 (3rd of Jan).

    Short of changing my table, query and form to mm/dd/yyyy, is there a way I can get the VB to recognise my filter correctly? Here's the screenshot I mentioned, the top part is my form, the middle is my VB code, and the bottom is the filtered results.

    Please help, guys.
    Attached Thumbnails Attached Thumbnails date.png  
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is a Microsoft conceit that the rest of the world uses illogical date formats like the US. VB will assume your dates are mm/dd/yyyy unless this is impossible.

    I use a function that checks the date value: if it could be a dd/mm/yyyy date then I swap the month and day, otherwise I leave the date as is. I don't know if this is the best solution (the best solution would be to use ISO 8601 format, but Access baulks at this) but it works for me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Pootle flump is right. Internally, Access does not take the local settings of Windows in account and expect a date in mm/dd/yyyy format. In your case, you can use:
    Code:
    DoCmd.ApplyFilter , "Expiry = #" & Format(Forms!View!Date1, "mm/dd/yyyy") & "#"
    Have a nice day!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sinndho - that looks so simple I can't believe I didn't investigate it and reject it. Are you sure that works ok?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    I would have loved this to work, but it does not!

    In fact, I tried alternatives of using Format( ) on everything else imaginable... I even used

    Code:
    Dim date_one as Date
    date_one = Me.Date1       ---> with and without the Format( )
    ...
    DoCmd.ApplyFilter , "Expiry = #" & date_one  & "#"
    It just doesn't work for me.


    @pootle_flump
    Nope, even if it couldn't be a possible date... ie 31/03/2010 can never be mm/dd/yyyy... well it just returned me a blank filter.


    Any more suggestions, guys?
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the source (field or variable) is properly defined as a Date/Time data type, it works, I use it very often (see the attached screenshot).
    Attached Thumbnails Attached Thumbnails Date format.jpg  
    Have a nice day!

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Having encountered this problem before, I tend to pass dates to functions as formatted strings (dd-mmm-yyyy). I also use this format for date fields in tables so that there is no doubt about it.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Jul 2009
    Posts
    32
    Quote Originally Posted by Sinndho View Post
    If the source (field or variable) is properly defined as a Date/Time data type, it works, I use it very often (see the attached screenshot).
    Sorry mate, not sure I got everything you said. I did correctly specify my table field as Date/Time, which is then replicated as such in the Form, where the date picker shows up correctly as well.

    @weejas
    I would have loved to go with your suggestion, but 2 problems arise. Let's dismiss the look and feel of that format (which I personally dislike), but you're also assuming everyone understands the "mmm" format. Doesn't happen where I work in a non-English speaking environment, people actually make frequent mistakes with names of days and months. I won't stop you from laughing, because that's what I'm still doing after close to 2 years here *rotflmfao*


    Anyhow, after a night's sleep over it, I came back at it and managed to workaround the VB problem. Not pretty, but it'll suffice for now. In short, I passed the date function to mm/dd/yyyy format before applying the VB functions, then after that I reverted the format back to dd/mm/yyyy.

    Here's my code for those interested:

    Code:
    Private Sub Btn_RenMtr_Click()
    
    'Pass Date functions in mm/dd/yyyy format for VB
      Me.Date1 = Format(Me.Date1.Value, "mm/dd/yyyy")
      Me.Date2 = Format(Me.Date2.Value, "mm/dd/yyyy")
    
      DoCmd.OpenQuery "Expiry Query", acViewNormal, acEdit
      DoCmd.ApplyFilter , "Expiry Between #" & Forms!View!Date1 & "# And #" & Forms!View!Date2 & "#"
    
    'Pass Date functions back to dd/mm/yyyy format for Access
    'but if Day > 12, it will not revert so we'll have to address it differently
      If Day(Me.Date1) > 12 Then
        Me.Date1 = Format(Me.Date1.Value, "dd/mm/yyyy")
      Else
        Me.Date1 = Format(Me.Date1.Value, "mm/dd/yyyy")
      End If
    
      If Day(Me.Date2) > 12 Then
        Me.Date2 = Format(Me.Date2.Value, "dd/mm/yyyy")
      Else
        Me.Date2 = Format(Me.Date2.Value, "mm/dd/yyyy")
      End If
    
    End Sub
    Instead of converting the field to and fro, I'm guessing I could pass a new Date variable and reference it with the filter, but I'm lazy to recode at the moment. So far it's working for me =)

    Cheers and thanks for all your comments guys!
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a date variable then reformatting the date will work

    if its a text variable it wont work, so you may have to cast the date to a date variable and then format it to the desired types. eg cdate(mytextcontrol). Bear in mind that Access/JET doesn't store a date value as a dd/mm/yyyy, yyyy/mm/dd or whatever it stores dates as a date / time value which is IIRC the integer number of days since 1900 and the number of seconds in the day as a decimal complement to the date. so an internal date of 1.5 equates to 1st Jan 1900 @ 12:00. the presentation layer handles the formatting to whatever type your specify OR your machines default date type.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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