Access 2002 (Office XP) and Win XP ' dd/mm/yyyy ' Date Dilemma 2

[blakey2]

Hello All,

I am running Win XP pro as a standalone PC, with Office XP (Access 2002) installed. I live in Australia and as such set my default date preferences to the Aust. standard of dd/mm/yyyy (as opposed to mm/dd/yyyy), when I installed Windows.
Recently I installed XP SP2 which appears to have set back my windows default preferences to the US mm/dd/yyyy standard. I developed my database from scratch over the last three weeks on this platform and I assumed that the Access environment dictated that I must use the US date format.

It was only today that I investigated my windows settings, and discovered they had reverted back to the US settings.

After changing the locale etc back to Australia, and a reeboot all seemed fine, except I noticed that my queries would not pick up a specific date.

I'll embelish a little on my stored dates.
<beginEmbilish>
My db imports a weekly (sales) file downloaded from a 3rd party off the internet. Every Thursday they update their weekly report, such that a report downloaded on a thursday will be for the week ending 3 days prior to that thursday. A report downloaded on the Friday thru to the following Wednesday will be the same file, hence the date is is stored in the db must be the same.
</endEmbelish>

Anywho, my (VBA) function which works all of this out for me was working like a charm.... until I changed my system settings to dd/mm/yyyy.

Here is the code for my function:

Public Function establishWeekEnding() As String
    Dim today As Integer

    ' Set variable 'today' to the numeric value as a weekday
    ' i.e. Sun = 1, Mon = 2... Sat = 7, Sun = 1... etc
    today = DatePart("w", Date)
    
    Select Case today
        Case 1 'Sunday
            establishWeekEnding = DateAdd("d", -7, Date)
        Case 2 'Monday
            establishWeekEnding = DateAdd("d", -8, Date)
        Case 3 'Tuesday
            establishWeekEnding = DateAdd("d", -9, Date)
        Case 4 'Wednesday
            establishWeekEnding = DateAdd("d", -10, Date)
        Case 5 'Thursday -- NEW WEEK REPORT COMES OUT!
            establishWeekEnding = DateAdd("d", -4, Date)
        Case 6 'Friday
            establishWeekEnding = DateAdd("d", -5, Date)
        Case 7 'Saturday
            establishWeekEnding = DateAdd("d", -6, Date)
        End Select
   
End Function

Now, all was good until the change. Afterwards, strange things started happening. My function continued to work fine except for when the dd and mm had the same value (ie 11/11/2004, 12/12/2004) When they fit the " dd == mm " criterion they, (after being processed by my function), turned into the US date format. ie Thursday 11th November should have returned 07/11/2004 (7th November 2004), instead it returned 11/07/2004 (11th July 2004)!

Part of my embedded SQL (Jet) relies on these dates and the queries are sometimes running and returning no records (none exist for the false/backward range), or else the query which stores the SQL is coming up with an SQL error, essentially truncating the SQL statement halfway through.

If however, I select the 'bad' dates, run a debug.print, copy and paste the resultant sql into a new query window and change the date fields to the correct values in the dd/mm/yyyy format then all is well.

I assume that my problem lies with the inbuilt function " DateAdd" (Note: I am passing it a -ve value to subtract a certain number of days).

Does anyone else know of any alternatives, or how I might go about coding one up for myself? -- I had thoughts of coding my own one before I found the 'DateAdd' function, and the prospect did not appeal to me that much ;D

Sorry about the length of this submission (READ: Ramble), all thoughts and suggestions are appreciated. -- In any case, I have to find a solution, so if none are forthcoming, i'll keep this space posted.

Thanks -- blakey2.

 

[PHV]

The problem is here:
Public Function establishWeekEnding() As [highlight]String[/highlight]
Either change the type to Date or make the string a US formatted date with the Format function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blakey2]

Hey PH,

Thanks for picking that up, but it did not solve the problem. I read a multitude more posts, and Duane had suggested a link:

http://members.iinet.net.au/~allenbrowne/ser-36.html

This took a little while to open, so I read it after I made my post, but essentially it says, that SQL in access MUST have a US date format.

I am not the most knowledgeable kid on the block, but my experience suggests that this is a fallacy (of sorts).
Above I mentioned that if entered the correctly formatted (AUS) date dd/mm/yyyy into my SQL query (in place of the mm/dd/yyyy) then it worked a treat, even though Access decided to randomly store that particular date in mm/dd/yyyy format.

Just to state the problem (as I understand it) more generally.

[] My Win system settings specify dd/mm/yyyy.
[] Access is happy to use this format
[] EXCEPT when I generate a date where the criterion
are 'dd == mm' (Using 'DateAdd' library function)
[] THEN it will force the result to be backwards. ie (mm/dd/yyyy - 11/07/2004). NOTE: Access still thinks of this as being in ( dd/mm/yyyy - 11/07/2004) format.

[] JET SQL seems more than happy to take all dates in dd/mm/yyyy format and run with them.
[] JET SQL treats the 'false mm/dd/yyyy - 11/07/2004' as 'dd/mm/yyyy - 11/07/2004' (due to the Win settings)

[] Access does not have a problem with the query as is syntactically correct, however returns no records as it is searching a date of the 11th of JULY, not 7th November.

[] My query works fine for all other dates. ie Will return 31/10/2004 which can ONLY be valid if date format is dd/mm/yyyy.

[] When I cut and paste the SQL generated string into a new query I get:

    And P.PeriodEnding Between #11/07/2004# And #11/07/2004#

Then I replace 11/07/2004 with 7/11/2004 to get:

    And P.PeriodEnding Between #7/11/2004# And #7/11/2004#

Then the query generates the expected outcome, even though the value is stored as 11/07/2004 in the table in the db.

I am pretty sure that the problem lies with the 'DateAdd' (built in) function.

I shall continue to investigate (my code and other info).

Thanks - blakey2.

 

[PHV]

the value is stored as 11/07/2004 in the table
Date are stored as number of days, dot.
To avoid any ambiguity do the date tests with values like:
#yyyy-mm-dd#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blakey2]

Hello PH,

>>the value is stored as 11/07/2004 in the table
>Date are stored as number of days, dot.

I used the wrong terminology, sorry. What I meant to say was that when the table is viewed '11/07/2004' is the value in the column.

>To avoid any ambiguity do the date tests with values like:
>#yyyy-mm-dd#

I am a little unsure how to do this. The user never inputs a date, I get the current date by calling the Date() library function. I then use library functions, DatePart and DateAdd to work out the date I want.

When the user actually wants to query the db, the date range is selected by means of a drop down box, which is populated with distinct date values from the table.

Because of this setup, I am not sure that I can force the date range?

Thanks - blakey2.

 

[blakey2]

Hey PH,

>To avoid any ambiguity do the date tests with values like:
>#yyyy-mm-dd#

OK I am a little slow, it just dawned on me after writing my previous post that I should manually enter dates in my SQL as yyyy-mm-dd!

Will do asap and get some results!

 

[payback]

Please look at the DATEVALUE function for Jet SQL. It uses the Regional Settings for the computer to properly handle any date selections (there is also a function for SQL Server that provides similar functionality). I successfully develop the same application for European date format and US date format computers and since utilizing this function (since 1997) I have had no issues. Also in reports, to ensure that there will be no confusion when the report is distributed, I use the dd mmm yyyy format e.g. 7/11/04 in the US becomes 11 Jul 2004 in reporting, as will 11/7/04 on the European formatted computer.

 

[Dalain]

hello blakey2

There are are alot of good answers here. So I just wanted to add my bit to it as well.

For starters, no matter what format you have selected for your System date, at one point or another when converting the Date from [green]"text type data" to "date type data"[/green], ACCESS will READ the Date in a mm/dd/yy format. (Microsoft is a US product. The US default date format is mm/dd/yy)

To help you understand how access uses dates this is how it is explaned in the Access Help File.

[blue]Date expression
Any expression that can be interpreted as a date, including date literals, numbers that look like dates, strings that look like dates, and dates returned from functions. A date expression is limited to numbers or strings, in any combination, that can represent a date from January 1, 100 to December 31, 9999.

Dates are stored as part of a real number. Values to the left of the decimal represent the date; values to the right of the decimal represent the time. Negative numbers represent dates prior to December 30, 1899.[/blue]

Access then uses this number to control how the date is displayed. This is why you can display so many different date formats The problem is when access converts date information from a date field to a text field. Instead of using the windows default system format as a guide, it[red] defaults to a General Date format 4/3/93, 05:34:00 PM[/red] (mm/dd/yy, hh:nn AM/PM)

So if you are using a date in a SQL statement or the CDate function, you may get some weird results. For example if my PC's date setting is DD/MM/YY and Me.FPDateExp = 03/09/003, where 09 is the month of Sep.

When using RunSQL you must convert the date type date into text. In this example when access converts the Date value into text it is displayed as 09/03/03,

Example
DoCmd.RunSQL ("UPDATE FPSettings SET FPSettings.ExportDate = #" & Me.FPDateExp & "#;")

When converted back to a date value will be seen as 9 Mar 03. (General Date format mm/dd/yy)

To solve the problem, use the format function. Use the three m's (mmm) which is the month converted to letters as this will ensure the 03 is displayed as Mar, which when converted to the number 03, not 09. Also the four y's (yyyy)since 2003 must be the Year value, (Year 03 ?? not so sure).

DoCmd.RunSQL ("UPDATE FPSettings SET FPSettings.ExportDate = #" & Format(Me.FPDateExp, "dd mmm yyyy hh:nn") & "#;")

Here is a simple function that I use to solve the problem.
Example

DoCmd.RunSQL ("UPDATE FPSettings SET FPSettings.ExportDate = #" &DtoC(Me.FPDateExp) & "#;")

Place this code it in a global module.
[blue]
Function DtoC(InpDate As Date)
'This function converts a date type data to a String type data
Dim strmonth As String
Dim NewDate As String

Select Case Month(InpDate)
Case 1
strmonth = "Jan"
Case 2
strmonth = "Feb"
Case 3
strmonth = "Mar"
Case 4
strmonth = "Apr"
Case 5
strmonth = "May"
Case 6
strmonth = "Jun"
Case 7
strmonth = "Jul"
Case 8
strmonth = "Aug"
Case 9
strmonth = "Sep"
Case 10
strmonth = "Oct"
Case 11
strmonth = "Nov"
Case 12
strmonth = "Dec"
End Select
DtoC = Day(InpDate) & "-" & strmonth & "-" & Year(InpDate)

End Function
[/blue]

For people in the USA, this usally not an issue as thier system date is set to mm/dd/yy, but for me, like you the date format in Canada is dd/mm/yy.

Hope this helps
Dalain

 

[blakey2]

Dear All,

Thankyou all for your feedback. I WISH I had of checked the post three hours ago (or indeed a day or two earlier), as i fear I wasted three hours writing a 'dirty' solution. -- In short I just re-wrote my own version of the DateAdd function to return an unambiguous string (ie 11-Oct-2004), so Access would not be tricky and try and 'helpfully' convert dates into US format.
NOTE: JET SQL is more than happy to deal with a date (formatted as a string) of the form '11-Oct-2004' .

Since I wasted my time rewriting the function, I'll post it anyway, as maybe someone out there will find the code of use.

A slight change in my original code (above) as a result of a suggestion by Dalain meant that 'DateAdd' returned a dd/mm/yyyy date.

establishWeekEnding = DateAdd("d", -7, Format(Date, "dd mmm yyyy  hh:nn"))

- I could have sworn that I tried this earlier, apparently not though as it works a treat now and is the option I am going to use.

I am still playing with the DateValue function (as suggested by payback). I've read the MSDN docs, but so far I have not figured how to force it to an dd-mmm-yyyy format (ie. 10th November 2004 is still displaying as 10/11/2004 which turns into a US date when the SQL is processed -- I want to turn it into 10-Nov-2004 to use in the SQL) -- I'm sure i'll get it though, tis too late/ too early here and the brain no work so good capn'.

Otherwise i can achieve the same thing using the library 'MonthName' function (see my code below in the buildDate function for an example). -- Dalain this does the same as your code above (in blue) --

Enough of my ramblings. Here is my code. It was designed specifically and only to solve my problem of subtracting days from the current date. (or any date given). The max number of days to be subtracted should be < 58. Any more and it won't work.

Option Compare Database

'*******************************************************************
' lakeyDateAdd takes two arguments:
'   nuDate:         Is a date value which represents the starting
'                   date to which a number of days will be
'                   subtracted from.
'   daysToChange:   Must be NEGATIVE!! Indicates the number of days
'                   to remove from nuDate.
'
' NOTE: This function/module is a quick/dirty hack designed to allow
' the programmer (me) to pass dates in UK/AUS standard of dd/mm/yyyy.
'
' Access/VBA has the annoying feature of being 'intelligent', and
' tries to convert all dates into the US mm/dd/yyyy format UNLESS is
' not ambiguous ie 31/04/2004 or 31-Apr-2004.
'
' It is for this reason that all 'Dates' are passed as strings to
' preserve their integrity.
'
' Written: Chris Miles Lakey. Tues 16th November 2004.
'
' *******************************************************************

Public Function lakeyDateAdd(nuDate As Date, daysToChange As Integer) As String
       
    
    Select Case daysToChange
        ' This is the case of a negative value (ie days subtracted)
        Case Is < 0
            lakeyDateAdd = subtractDays(nuDate, daysToChange)
        
        Case Else
        'Case where daysToChange is +ve
          MsgBox " This function will only take a -ve number of days!" & _
                   " Returning initial date!!!", vbCritical
                        
            lakeyDateAdd = nuDate
  
    End Select

End Function

Private Function subtractDays(nuDate As Date, daysToChange As Integer) As String
    'Declare variables
    Dim dayPart As Integer
    Dim monthPart As Integer
    Dim yearPart As Integer
    
    'Initialise variables
    dayPart = day(nuDate)
    monthPart = month(nuDate)
    yearPart = year(nuDate)
    
    'Check to see if days subtracted are less than zero
    'If YES then easy -- Only need to subtract days from current month
    If dayPart + daysToChange > 0 Then
        dayPart = dayPart + daysToChange
        subtractDays = buildDate(dayPart, monthPart, yearPart)
    Else
        'Need to establish month stuff
        establishMonth nuDate, daysToChange, dayPart, monthPart, yearPart
        'Now build the date
        subtractDays = buildDate(dayPart, monthPart, yearPart)
    End If

End Function

' *******************************************************************
' buildDate takes three arguments:
'   dayPart:    Is the integer value of the day
'   monthPart:    Is the integer value of the month
'   yearPart:    Is the integer value of the month
'
' The function takes the calculated dates and using the library
' function 'MonthName' creates an unambiguous string in the form
' of 16-Nov-2004.
'
' NOTE: I should stress that the locale/region settings of windows
'       MUST be set to the dd/mm/yyyy format otherwise this code
'       will not work.
'
'   Written: Chris Lakey 16th November 2004
' *******************************************************************
Private Function buildDate(ByVal dayBit As Integer, ByVal monthBit As Integer, _
                                ByVal yearBit As Integer) As String
                                
    Dim dateString As String
               
    ' Build the date as an 'UNAMBIGUOUS' string in dd-mmm-yyyy format
    ' ie 11-Nov-2004
    dateString = dayBit & "-" & MonthName(monthBit, True) & "-" & yearBit
    
    buildDate = dateString
                                
End Function

Private Function establishMonth(ByRef nuDate As Date, ByRef daysToChange As Integer, _
                                ByRef dayPart As Integer, ByRef monthPart As Integer, _
                                ByRef yearPart As Integer)
   
    Dim dayDiff1 As Integer
    Dim dayDiff2 As Integer
    Dim monthDiff As Integer
        
    ' Work out difference (multiply daysToChange by -1 to make positive)
    ' dayDiff stores the number of days to subtract off the (previous) month.
    dayDiff1 = (((-1) * daysToChange) - dayPart)
    
    'Need to work out how many days in a month.
    dayDiff2 = getMonthDays(monthPart, yearPart)
    
    dayPart = dayDiff2 - dayDiff1
   
End Function

Private Function getMonthDays(ByRef monthPart As Integer, _
                                ByRef yearPart As Integer) As Integer

    'MsgBox "Month Part:  " & monthPart

    'Check for year change!!
    If monthPart = 1 Then
        monthPart = 12
        yearPart = yearPart - 1
    Else
    ' Subtract 1 from value of the month as interested in previous month
        monthPart = monthPart - 1
    End If
    
    'MsgBox "Month Part:  " & monthPart
    
    Select Case monthPart
        Case 9, 4, 6, 11
                getMonthDays = 30
        Case 1, 3, 5, 7, 8, 10, 12
                getMonthDays = 31
        Case Else
            If (isLeapYear(yearPart)) Then
                getMonthDays = 29
            Else
                getMonthDays = 28
            End If
    End Select
    
End Function

Private Function isLeapYear(yearValue As Integer) As Boolean

    'Leap Years occour every four years ie. 2004 2008
    If yearValue Mod 4 = 0 Then
        'Is a leap Year
        isLeapYear = True
    Else
        'Is not a leap year
        isLeapYear = False
    End If

End Function

Goodnight everyone, and a big thankyou to everyone whom replied.

Cheers - blakey2.