Results 1 to 14 of 14
  1. #1
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126

    Inputformat for dates ??

    I want the user to be able to input "dates and periods" in 3 different formats:

    1) dd-mm-yyyy
    2) mm-yyyy
    3) yyyy

    in the SAME textbox.

    Then I think I can set up a filter for the dataquery to show me the records.

    How can I do that ?

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Combobox so they can select which format? This would alter the format of the text box (perhaps even make the text box enabled too). Then the user can fill in the text box.

  3. #3
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126
    Yeah, when it can't be in 1 format-string (and it can't - I have tried !) I, think it would be faster with 3 radiobuttons

    thx - I'll try that.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    If you changed the mask using the option group_change event to ensure you get a consistent response and made three invisible text boxes you could assign each one as the date month and year and filter from there

  5. #5
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126
    I used the radiobuttons and 1 txtbox and it works nicely, BUT ...

    When I use the format mm-yyyy and then have a filter like - and I've checked it "StepByStep":

    Me.Filter = "HDato >= #01-08-1949# and HDato <= #31-08-1949#"

    the resulting set of records INCLUDE a date "14-07-1949" !!!!!!!

    Why ????????

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Your dates are wrong in filter.
    08-01-1949 is first august.

    Checkout usage here


  7. #7
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126
    Woops ... why doesn't Access check my Windows-setting where I have a date-setting as dd-mm-yyyy ?

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Sql always works that way round. No matter where you are.

  9. #9
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126
    Grrrrrrrrrr !

  10. #10
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126
    I'm so sick and tired of messing around with dates in DANISH and SQL formats - it's driving me crazy !!!!!!

    a DANISH date (dd-mm-yyyy) is AUTOMATICALLY converted to SQL-format ... IF IT CAN be done:

    01-07-1949 converts to 07-01-1949 but ...
    14-07-1949 'converts' to 14-07-1949

    What is "best practice" - not Quick&Dirty !

    How can I manage that in an organized way ?

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Is the date a text field still?

  12. #12
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126
    I manage it as a Date-datatype

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    OK I need clarification

    Your user presses a toggle button and types in a date in a field txtdate?


    If the date that is entered has format dd-mm-yyyy then the following will convert that text to a date field

    Code:
    DateSerial(CInt(Right([txtdate],4)),CInt(Mid([txtdate],4,2)),CInt(Left([txtdate],2)))
    You should be able to use this in a filter
    Code:
    me.filter = "Hdato = " & DateSerial(CInt(Right([txtdate],4)),CInt(Mid([txtdate],4,2)),CInt(Left([txtdate],2)))

  14. #14
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    126
    I haven't seen problems yet when the input mask is dd-mm-yyyy - but maybe there is anyway !

    The problem is obviously when using inputmask mm-yyyy and yyyy - here I have to construct an interval of dates in VBA and I can't test while I'm doing the coding because of this automatic converting I told in my earlier posting !

    I would like a "Best practice solution" where the Windows-settings is taken into account in the code too.

    I beleave this fidling around with "Bits&Bytes" is a dirty solution - eventhough it solves the problem.

    EDIT:

    If I do this in the immidiate window:

    txtDate=#14-07-1949#

    ? DateSerial(CInt(Right(txtDate,4)),CInt(Mid(txtDate ,4,2)),CInt(Left(txtDate,2)))
    14-07-1949

    txtDate=#01-07-1949#

    ? DateSerial(CInt(Right(txtDate,4)),CInt(Mid(txtDate ,4,2)),CInt(Left(txtDate,2)))
    07-01-1949

    What do I mis ... I see no change at all !

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

Similar Threads

  1. Select Records based on dates or no dates or both
    By usatraveler in forum Queries
    Replies: 3
    Last Post: 08-12-2016, 07:52 AM
  2. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  3. List Dates between Start and Finish Dates
    By Buddus in forum Queries
    Replies: 1
    Last Post: 07-26-2013, 01:58 PM
  4. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM
  5. Mixed dates - show only complete dates?
    By weeblesue in forum Queries
    Replies: 3
    Last Post: 10-27-2010, 02:15 PM

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