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

Topic

New Reply

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

Reply | Quote

Replies

Kevin,

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

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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 ?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote