Datevalue

  • Thread starter Thread starter Jay Kay
  • Start date Start date
J

Jay Kay

I have taken over an application that parses out data from a flat file. It
has been running in the UK without issue. I am in the US and am having
problems with a date field. I believe the issue is with datevalue().

The records are in the file in reverse date order. The records from Feb 29
thru Feb 13 process fine. When I get to Feb 12, I have an issue.


sRunDate ="20080212"
jobdate = DateValue(Right$(sRunDate, 2) & "/" & Mid$(sRunDate, 5, 2) & "/" &
Left$(sRunDate, 4))


When run in the UK (or when I set the short date format on my machine to
dd/mm/yy), jobdate evalutes to 2/12/08


When run with my short date format set to US, jobdate evaluates to 12/02/08

The odd thing is that when the sRunDate is something like "20080227", it
evaluates to 2/27/2008, as if it realizes that since 27 is not a valid month,
to flip-flop the month and day.

I don't want to have to change the date format on my machine every time I
need to run this. Is there a way to set the date format in the code? Or how
else can I go about correcting this?

Thanks

Regards
 
DateValue requires 3 arguments, not a single concatenated string.

The year must the the *first* argument, followed by the month, and then the
day.

We did not see your declarations, but use Option Explicit (so Access catches
any undeclared variables), and declare your variables like this:

Dim sRunDate As String
Dim jobdate As Date
sRunDate ="20080212"
jobdate = DateValue(Left$(sRunDate, 4), Mid$(sRunDate, 5,2),
Right$(sRunDate, 2))

For more info on handling different date formats, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
 
Hi Jay Kay,

try in this way
sRunDate ="20080212"
if fLocaleInfo(LOCALE_SSHORTDATE)="dd/mm/yyyy" then 'UK date format
jobdate = DateValue(Right$(sRunDate, 2) & "/" & Mid$(sRunDate, 5, 2) &
"/" &
Left$(sRunDate, 4))
elseif fLocaleInfo(LOCALE_SSHORTDATE)="mm/dd/yyyy" then 'US date format
jobdate = DateValue(Mid$(sRunDate, 5, 2) & "/" & Right$(sRunDate, 2) &
"/" &
Left$(sRunDate, 4))
endif

You can find the flocalecode that you have to paste in a module @
http://www.mvps.org/access/api/api0017.htm

HTH Paolo
 
If your date string is always present and is always in the format yyyy mm dd
then I would use

DateValue(Format(sRunDate,"@@@@\-@@\-@@"))

That should always return the date correctly no matter what your short date
format.

You can force that to handle nulls and "bad" dates by testing the formatted
string with IsDate(Format(sRunDate,"@@@@\-@@\-@@"))
On my computer (US)
DateValue(Format("20080212","@@@@\-@@\-@@")) returns 2/12/2008

Double check just to make sure, I formatted that to show the abbreviated
month.

Format(DateValue(Format("20080212","@@@@\-@@\-@@")),"dd mmm yyyy") returns
12 Feb 2008

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
You're thinking of DateSerial, Allen.

The syntax for DateValue is

DateValue(date)

Where the required date argument is normally a string expression
representing a date from January 1, 100 through December 31, 9999. However,
date can also be any expression that can represent a date, a time, or both a
date and time, in that range.

Note that DateValue IS one of the few functions in Access that respects the
user's Regional Settings. <g>

Your suggested code should actually be

Dim sRunDate As String
Dim jobdate As Date
sRunDate ="20080212"
jobdate = DateSerial(Left$(sRunDate, 4), Mid$(sRunDate, 5,2),
Right$(sRunDate, 2))

although I usually simplify it to

Dim sRunDate As String
Dim jobdate As Date
sRunDate ="20080212"
jobdate = CDate(Format(sRunDate, "####\-##\-##"))
 
If I interpret the question correctly then your dates are in a string in the
format yyyymmdd and if this is so then the following example should convert
it to a date in any country's default format. I have separated all the
components because I think that it is easier to follow but some, if not all,
of the functions could be combined into one line.

Sub Date_Conversion()

Dim sRunDate As String
Dim yearDate As Long
Dim monthDate As Long
Dim dayDate As Long
Dim myDateSerial As Long
Dim myDate As Date

sRunDate = "20080212"

yearDate = Val(Left(sRunDate, 4))

monthDate = Val(Mid(sRunDate, 5, 2))

dayDate = Val(Right(sRunDate, 2))

myDateSerial = DateSerial(yearDate, monthDate, dayDate)

myDate = myDateSerial

MsgBox myDate

End Sub
 
Hi again,

I omitted to say that if you dimension myDateSerial as Date instead of long
then you can use it as the date and omit the line myDate = myDateSerial.
 
Back
Top