Visual Basic - Dear Rick, Ken, and other super-gurus, regional date settings revisited

Author

Dear Rick, Ken, and other super-gurus, regional date settings revisited

Frisbee®

2005-12-14, 6:57 pm

I've got a rather unusual situation with my code. I've googled changing
regional date settings and noticed a few threads in which Rick and Ken
responded along with others. I totally agree that changing the regional
settings is a bad idea, so I'm hoping that somebody can tell me exactly
what's going on with my code so that I can fix it rather than force a
certain date format setting, which for some reason, fixes the problem.

I'm reading a third-party text file from ADP which is a payroll processing
company.

Their date is in a very simplistic format, a two-digit month, followed by a
two-digit day, and a one-digit year. I guess that Y2K doesn't apply to
these people. I guess I can understand that, however, since these aren't
aged invoices, or anything, it's always a current year payroll. However, I
suspect they may have problems when a payroll dated at the end of a decade
(say December 2009) is processed in the next decade (say January 2010), as
if the current system date is 2010, and the data file says the year is "9",
you'd have to write some code other than what I have or else it might think
it's 2019.

Anyway, in this particular transfer file, every single record has the same
date, as it is after all, a payroll run. The date passed is "12095" which
translates to "12/09/2005".

I have a user-defined type to capture all the detail information for each
employee, including this date, and the field is defined as type Date. The
simple code to translate is:

1130 strDate = Mid$(strRecData, 33, 2) & "/" & _
Mid$(strRecData, 35, 2) & "/" & _
Format$((Year(Now) Mod 10) + Val(Mid$(strRecData, 37,
1)), "0000")
1140 If Not IsDate(strDate) Then
1150 MsgBox "Invalid date " & QuoteIt(strDate) & " at line " & _
CStr(lngRecNo + intSkipRecs) & " of ADP Import File",
vbCritical
1160 blnError = True
1170 Exit Sub
1180 End If
1190 .InvDate = CDate(strDate)

(Note the .InvDate is inside a "With udtADP" line...)

Now, for some reason, if the regional date settings are "MM/dd/yyyy" there
is no problem. However, if the regional settings are "M/d/yyyy" it fails,
but here's the other interesting thing. It does not fail at the above code.
It fails later in another subroutine when I assign another Date type
variable from the udtADP.InvDate variable. It returns a type mis-match
error. Debugging the code:

50 dteInvoice = udtADP.InvDate

Which is where the error occurs, returns "udtADP.InvDate = '12/9'" So, what
happened to the year? Why would it work with the leading-zero format, but
not without it?

Any and ALL help greatly appreciated.

Bill "Frisbee" Hileman

Bob Butler

2005-12-14, 6:57 pm

"Frisbee®" <billLASTNAME@yahoo.com> wrote in message
news:OIuGZ%23LAGHA.2912@tk2msftngp13.phx.gbl
> 1130 strDate = Mid$(strRecData, 33, 2) & "/" & _
> Mid$(strRecData, 35, 2) & "/" & _
> Format$((Year(Now) Mod 10) +
> Val(Mid$(strRecData, 37, 1)), "0000")
> 1140 If Not IsDate(strDate) Then

Don't try to do string-to-date conversions if you can avoid them; if you
can't then use a format that won't give you problems (e.g. yyyy-mm-dd
instead of mm/dd/yyyy or dd/mm/yyyy)

If you know the input data is valid then you can go direct (but I'd probably
break it up to make it easier to read):
y=(year(now) mod 10) + clng(mid$(strRecData,37,1))
m=clng(mid$(strRecData,33,2))
d=clng(mid$(strRecData,3,5,2))
if year(now)<y then y=dateadd("y",-10,y) ' adjust for 2019
dt=dateserial(y,m,d)

If you need to test validity then just reformat what you have
y=Format$((Year(Now) Mod 10) + Val(Mid$(strRecData, 37, 1)), "0000")
m=mid$(strRecData,33,2)
d=mid$(strRecData,35,2)
strDate=y & "-" & m & "-" & d
If IsDate(strDate) Then...

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

Frisbee®

2005-12-14, 6:57 pm

"Bob Butler" <tiredofit@nospam.com> wrote in message
news:OPnDoHMAGHA.3872@TK2MSFTNGP12.phx.gbl...
> "Frisbee®" <billLASTNAME@yahoo.com> wrote in message
> news:OIuGZ%23LAGHA.2912@tk2msftngp13.phx.gbl
>
> Don't try to do string-to-date conversions if you can avoid them; if you
> can't then use a format that won't give you problems (e.g. yyyy-mm-dd
> instead of mm/dd/yyyy or dd/mm/yyyy)
>
> If you know the input data is valid then you can go direct (but I'd
> probably
> break it up to make it easier to read):
> y=(year(now) mod 10) + clng(mid$(strRecData,37,1))
> m=clng(mid$(strRecData,33,2))
> d=clng(mid$(strRecData,3,5,2))
> if year(now)<y then y=dateadd("y",-10,y) ' adjust for 2019
> dt=dateserial(y,m,d)
>
> If you need to test validity then just reformat what you have
> y=Format$((Year(Now) Mod 10) + Val(Mid$(strRecData, 37, 1)), "0000")
> m=mid$(strRecData,33,2)
> d=mid$(strRecData,35,2)
> strDate=y & "-" & m & "-" & d
> If IsDate(strDate) Then...

I very much appreciate the feedback, Bob, but can you, or anyone else
explain why the other method works fine with one format and not the other?
And also why it does NOT fail in the bigger snippet of code, but does fail
in the other, as far as using "IsDate" and "CDate?"

After posting, I considered it might even be the hyphens versus the slashes,
but I checked with my partner on whose computer it also failed with the
"M/d/yyyy" format to make sure he did have slashes instead of the dashes.
He'd already changed it, but he was sure it was slashes.

I'll mess around with my computer some more and try to see exactly what's
going on in a line-by-line basis.

Thanks again.

Bob Butler

2005-12-14, 6:57 pm

"Frisbee®" <billLASTNAME@yahoo.com> wrote in message
news:uLtPLXMAGHA.4004@TK2MSFTNGP15.phx.gbl
> I very much appreciate the feedback, Bob, but can you, or anyone else
> explain why the other method works fine with one format and not the
> other? And also why it does NOT fail in the bigger snippet of code,
> but does fail in the other, as far as using "IsDate" and "CDate?"

What is not clear to me in your OP is at the point you do
1190 .InvDate = CDate(strDate)
what is the value of "strDate" and what value gets assigned to .InvDate? If
it is correct here and wrong later then something else is corrupting it.

You said that .InvDate was defined as a date type -- are you sure of that?

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

Frisbee®

2005-12-14, 6:57 pm

"Frisbee®" <billLASTNAME@yahoo.com> wrote in message
news:uLtPLXMAGHA.4004@TK2MSFTNGP15.phx.gbl...[color=darkred]
> "Bob Butler" <tiredofit@nospam.com> wrote in message
> news:OPnDoHMAGHA.3872@TK2MSFTNGP12.phx.gbl...

Okay, I am officially an idiot.

The above line returns "0010" because instead of Year MOD 10, I should have
used Year / 100 (or Year \ 10). The desired result was the current century
and decade, but what I returned was the (one-digit) year, then added the
record's year (5) and of course, the result is 10.

I'm still befuddled, however, as to why it only fails on formats without
leading zeroes.

I must be getting senile, been programming WAY too long to make that simple
of a mistake.

Frisbee®

2005-12-14, 6:57 pm

"Bob Butler" <tiredofit@nospam.com> wrote in message
news:OGuS$fMAGHA.3436@TK2MSFTNGP10.phx.gbl...
> "Frisbee®" <billLASTNAME@yahoo.com> wrote in message
> news:uLtPLXMAGHA.4004@TK2MSFTNGP15.phx.gbl
>
> What is not clear to me in your OP is at the point you do
> 1190 .InvDate = CDate(strDate)
> what is the value of "strDate" and what value gets assigned to .InvDate?
> If
> it is correct here and wrong later then something else is corrupting it.
>
> You said that .InvDate was defined as a date type -- are you sure of that?

We have a winner. I can't believe I missed something so basic (no pun
intended).

Apparently, when I defined the user-defined type, I matched it byte-for-byte
with the third party file's layout, intending to transfer the long input
string to its corresponding fields in the type, but apparently later, I
thought I had defined it as a Date type instead of String * 5. I can't
believe I missed that, and I also can't believe (again) it only fails with a
different format. I think my brain is on overload. The fact that I haven't
taken a vacation in 6 years must have something to do with it. :-)

Thanks, once again, Bob.

Bob Butler

2005-12-14, 6:57 pm

"Frisbee®" <billLASTNAME@yahoo.com> wrote in message
news:uyqCmpMAGHA.3984@TK2MSFTNGP14.phx.gbl
>
> We have a winner. I can't believe I missed something so basic (no pun
> intended).

We've all been there. After all, we "know" how it is defined so there's no
reason to check, right?

BTW, I see that I glossed right over your (Year Mod 10) as well. It made
perfect sense at the time.

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

Rick Rothstein [MVP - Visual Basic]

2005-12-14, 6:57 pm

> I'm reading a third-party text file from ADP which is a payroll processing
> company.
>
> Their date is in a very simplistic format, a two-digit month, followed by
a
> two-digit day, and a one-digit year. I guess that Y2K doesn't apply to
> these people. I guess I can understand that, however, since these aren't
> aged invoices, or anything, it's always a current year payroll. However,
I
> suspect they may have problems when a payroll dated at the end of a decade
> (say December 2009) is processed in the next decade (say January 2010), as
> if the current system date is 2010, and the data file says the year is
"9",
> you'd have to write some code other than what I have or else it might
think
> it's 2019.
>
> Anyway, in this particular transfer file, every single record has the same
> date, as it is after all, a payroll run. The date passed is "12095" which
> translates to "12/09/2005".
>
> I have a user-defined type to capture all the detail information for each
> employee, including this date, and the field is defined as type Date. The
> simple code to translate is:
>
> 1130 strDate = Mid$(strRecData, 33, 2) & "/" & _
> Mid$(strRecData, 35, 2) & "/" & _
> Format$((Year(Now) Mod 10) + Val(Mid$(strRecData,
37,
> 1)), "0000")
> 1140 If Not IsDate(strDate) Then
> 1150 MsgBox "Invalid date " & QuoteIt(strDate) & " at line " & _
> CStr(lngRecNo + intSkipRecs) & " of ADP Import File",
> vbCritical
> 1160 blnError = True
> 1170 Exit Sub
> 1180 End If
> 1190 .InvDate = CDate(strDate)
>
> (Note the .InvDate is inside a "With udtADP" line...)

I see your problem has already been solved, but I'd like to suggest you get
rid of the strDate variable and create a **real** date directly.

.InvDate = DateSerial(CStr((Year(Now) \ 10) & _
Mid$(strRecData, 37, 1)), _
Mid$(strRecData, 33, 2), _
Mid$(strRecData, 35, 2))

Then, the date will be correctly assigned no matter what the local setting
of the computer is. The only possible problem with this might be the error
you are protecting against in your

If Not IsDate(strDate) Then

statement. Does such an error actually happen? I mean, it seems like the
data from ADP's program should be pretty solid. Anyway, you can incorporate
an On Error statement to protect this assignment if you really think the
data is not always correct.

Rick

Sponsored Links