UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dates In International Apps, Access 2010    
 
   
DBink
post Nov 13 2017, 02:53 PM
Post#1



Posts: 15
Joined: 19-November 13



I have a DB that will be used in the US and India, so I'm doing some testing to see what breaks.

For testing...I have the Win 7 machine set to Hindi(India) in the Region and Language settings...


Calling the function (AdjustDate) and passing a Datetime:
?AdjustDate(#11/10/2017 2:00 AM#)...this is in mm/dd/yyyy format

iEnd is a date from a recordset with a value of: 03:30:00 (3:30:00 AM)

When I check the values in the immediate window I get some values that I don't expect.

?CDate(Format(dtTempAdjustedDate, "mm/dd/yyyy ") & !End)
11-10-2017 03:30:00 ....This is expected
?cdbl(CDate(Format(dtTempAdjustedDate, "mm/dd/yyyy ") & !End))
43019.1458333333 ....this is the value for Oct 11, 2017???

?dtTempAdjustedDate
10-11-2017 02:00:00 ...I thought VBA datetime variables would show US format
?cdbl(dtTempAdjustedDate)
43049.0833333333 ....this is the value for Nov 10, 2017


?dtTempAdjustedDate <= CDate(Format(dtTempAdjustedDate, "mm/dd/yyyy ") & !End)
False

Nov 10, 2017 02:00:00 <= Nov 10, 2017 03:30:00
Should be true??

Any insight would be appreciated!
Go to the top of the page
 
DanielPineault
post Nov 13 2017, 03:16 PM
Post#2


UtterAccess VIP
Posts: 5,398
Joined: 30-June 11



What is AdjustDate?

Have you seen: http://allenbrowne.com/ser-36.html or http://www.trigeminal.com/lang/1033/codes.asp?ItemID=7 ?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
theDBguy
post Nov 13 2017, 03:20 PM
Post#3


Access Wiki and Forums Moderator
Posts: 71,037
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Re: "I thought VBA datetime variables would show US format"

I could be wrong but VBA expects US format but will display (show) dates in whatever Regional Settings is set up on the computer.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
DBink
post Nov 13 2017, 03:27 PM
Post#4



Posts: 15
Joined: 19-November 13



Function AdjustDate(ByVal dtTempAdjustedDate As Date) As Date

I have read that article, but my issue seems to be with the date variables and CDate() function.

Here is the complete function:

Function AdjustDate(ByVal dtTempAdjustedDate As Date) As Date

Dim dtTempReturnDate As Date
Dim strAction As String
Dim iTempDOW As Integer ' weekday #
Dim x As Integer
Dim rstTemp As DAO.Recordset
Dim dbTemp As DAO.Database
Dim strSQL As String

On Error GoTo ErrorHandler
Set dbTemp = CurrentDb

iTempDOW = DatePart("w", dtTempAdjustedDate, vbMonday)
strSQL = "Select * from tbltempDayHoursLookup Where TimeZone ='" & gstrShortTZ & "' And DOW = " & iTempDOW & " order by DOW, Start"

Set rstTemp = dbTemp.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

With rstTemp
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
For x = 1 To .RecordCount
'iStart and iDay will be times stored in a table ...ex: iStart = 6:30:00 PM iEnd = 3:30:00 AM
If dtTempAdjustedDate >= CDate(Format(dtTempAdjustedDate, "mm/dd/yyyy ") & !start) And dtTempAdjustedDate <= CDate(Format(dtTempAdjustedDate, "mm/dd/yyyy ") & !End) Then
Select Case !Action
Case Is = "O" 'Return original date
dtTempReturnDate = dtTempAdjustedDate
Exit For
Case Is = "DS" 'Change to same day start
dtTempReturnDate = CDate(Format(dtTempAdjustedDate, "mm/dd/yy ") & gstrWD_Start)
Exit For
Case Is = "NDS" ' Change to next day start
dtTempReturnDate = CDate(Format(DateAdd("d", 1, dtTempAdjustedDate), "mm/dd/yy ") & gstrWD_Start)
Exit For
End Select
End If
.MoveNext
Next x
Else

End If
End With

AdjustDate = GetNextWorkDay(dtTempReturnDate) 'Added by David Binkley 11/11/2017

StandardExit:
If Not rstTemp Is Nothing Then
rstTemp.Close
End If
Set rstTemp = Nothing

If Not dbTemp Is Nothing Then
dbTemp.Close
End If
Set dbTemp = Nothing

Exit Function
ErrorHandler:
Select Case Err.Number
Case 1 To 49999 'Application error
DisplayError "{PROCEDURE_NAME}", Err.Number, Err.Description, "{PROCEDURE_NAME}", "C"
Case Is > 49999 'User Defined error
DisplayError "{PROCEDURE_NAME}", Err.Number, Err.Description, "{PROCEDURE_NAME}", "I"
End Select

Resume StandardExit

End Function
Go to the top of the page
 
PhilS
post Nov 14 2017, 04:28 AM
Post#5



Posts: 399
Joined: 26-May 15
From: The middle of Germany


QUOTE
?CDate(Format(dtTempAdjustedDate, "mm/dd/yyyy ") & !End)

This might break on Computers with Non-US date format configured in the regional settings.

CDate converts the date based on a "best guess" approach which will take the regional settings into account. It will probably work for unambiguous dates as 2017-12-31, but will fail for 2017-11-10 if the date order is different on that particular computer.

Either use a named format that takes the regional settings into account, e.g.:
CODE
?CDate(Format(dtTempAdjustedDate, "Short Date"))
Or do not use any string representation in your conversion at all, e.g.:
CODE
?CDate(Clng(dtTempAdjustedDate))


The same most likely applies to the time value stored in iEnd. But from your code sample I can't figure out where iEnd end is coming from and what is its original value.
This post has been edited by PhilS: Nov 14 2017, 04:31 AM

--------------------
Go to the top of the page
 
DBink
post Nov 14 2017, 08:17 AM
Post#6



Posts: 15
Joined: 19-November 13




'iStart and iDay will be times stored in a table ...ex: iStart = 6:30:00 PM iEnd = 3:30:00 AM
I am going through the table and getting a field that tells me how to adjust the date when dtTempAdjustedDate is between iStart and iEnd



It will intermittently fail when:
dtTempAdjustedDate = 11/2/2017 6:30 PM
iStart = 6:30 PM
I'm not sure how to get around using the format function to get the DATE from dtTempAdjustedDate and concatenate the TIME in the DB for the comparison....I need to give this some thought and try to come up with a different approach. Thanks for the feedback...

dtTempAdjustedDate >= CDate(Format(dtTempAdjustedDate, "mm/dd/yyyy ") & !start)
Go to the top of the page
 
PhilS
post Nov 15 2017, 05:39 AM
Post#7



Posts: 399
Joined: 26-May 15
From: The middle of Germany


QUOTE
I'm not sure how to get around using the format function to get the DATE from dtTempAdjustedDate and concatenate the TIME in the DB for the comparison....I need to give this some thought and try to come up with a different approach.

Well, I outline two possible approaches for the date already. You can use them analogously for the time values, provided they are stored in the database as Date/Time and not as Text.

Here are is my sample code extended to handle the Time in a Date/Time datatype:
CODE
?CDate(Format(dtTempAdjustedDate, "Short Date") & " " & Format(iEnd,"Short Time"))

?CDate(Clng(dtTempAdjustedDate) + cdbl(iEnd))

If the times are not stored as Date/Time, I would encourage you to change that in the table design. If this is not possible, you need to write your own function to parse the text values into a Date/Time Date datatype.


--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th November 2017 - 12:37 AM