Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    ACCESS 2007 Is there a way to check Computer Date format in VBA

    Hi, I am trying to correct a database issue that usually appears after a windows update gets pushed out to users: My situation is that in ACCESS I am establishing a Rate ID key field which eventually the user needs to find using a Dlookup function. This process in the database requires all dates be in the 4 digit year "YYYY" format when the key is created. Over time some of our users (approx 40 total) will get a windows update pushed to their desktop which changes the date format on system date from a MM/DD/YYYY format to a MM/DD/YY format.

    My question basically is that I am looking for a way to check the system date format to make sure it is set to the 4 digit year format before they process records and create a Key field improperly, from there I want to make sure they know to change the system date format on the control panel to the four digit format.

    The issue at hand is that some computers operate with the 4 digit format and some operate with the 2 digit format and when in the 2 digit format a dlookup function does not work as it always looks for a 4 digit dtae format.

    hope this makes sense and thank you in advance for any help you can give
    Last edited by WendellB; 2016-01-11 at 08:22. Reason: fix minor typo
    Kevin

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    How are you generating the keys? Are you using code? If so can you post the code?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Give this a try:
    Code:
    Sub SysYearLength()
    
    '*** Assumes Year is the LAST part of the date!        ***
    '*** If format is YYYY/mm/dd change sub in Msgbox to 0 ***
    
       Dim vDateParts  As Variant
       
       vDateParts = Split(Date, "/")
       
       MsgBox "System Date Year length is " & _
               Len(vDateParts(2)) & _
               " Digits in Length.", _
               vbOKOnly + vbInformation, _
               "Current System Date Info:"
       
    End Sub  ' SysYearLength
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I do it thru an update query:

    SQL version is:
    UPDATE RATES SET RATES.RateID = RATES!RateName & RATES!RateVersion & " " & RATES!Code
    WHERE (((RATES.RateID) Is Null));


    RateID = a text string of a name we give the rate
    RateVersion = the date the rate was established... this is where it gets messed up... the rate version is a date formated field and when the person running the query has a system date that consists of a 2 digit year it will complete the query with a 2 digit year... then when a user who has a system date format as a 4 digit year tries to process something that requires it to find that key thru a "DLOOKUP" function I pass the RateID and RateVersion to the function from a form and it does not find it because of the date format mismatch.


    Does this help ?
    Kevin

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Is RateVersion actually stored as a date field or is it stored as a text field with the formatting included? If it is stored as a date field, then you could use the Format() function to force the date to always be in 4-digit mode regardless of the current workstation settings.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    it is in fact stored as a date field... I fooled around with that idea awhile back but could not make it work consistently because when retrieving those records based on that key I used a form to trap the RateID and RateVersion and then lookup the key based on those variables, however some computers would be set to 2 digits and some would be 4 digit years and computers creating the key would always end being different from computers retrieving the records so I was hoping to find a way to determine the system date and notify user to set the global ddate settings all the same
    Kevin

Posting Permissions

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