|
 |
access thread: VB/SQL Question
Message #1 by pmcmillin@m... on Wed, 02 May 2001 11:24:24 -0500
|
|
--=====================_10178315==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
I have a question regarding date values being stored as integers. What is
the best way to work with dates being stored as integers? We are working
with a new program called "Actuate" and it is being used with our AS/400
Artesia program which stores the dates as integers. I have seen the
difficulties already involved with it not being in a date format.
What is the best way to convert this field to a date data type? Where in
your parameters you can do a range of dates (Between [] and []).
Pam McMillin
Systems Technician
Schlumberger - IPM Asset Management
500 W. Texas, Suite 500
P.O. Box 2726
Midland, TX 79702-2726
Office 915/571-4600
Cell 915/556-0749
Fax 915/571-4788
pmcmillin@m...
Message #2 by "John Ruff" <papparuff@c...> on Wed, 2 May 2001 15:56:30 -0700
|
|
Pam,
Can you provide a sample of how the dates are stored in the AS400?
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Pam McMillin [mailto:pmcmillin@m...]
Sent: Wednesday, May 02, 2001 9:24 AM
To: Access
Subject: [access] VB/SQL Question
I have a question regarding date values being stored as integers. What is
the best way to work with dates being stored as integers? We are working
with a new program called "Actuate" and it is being used with our AS/400
Artesia program which stores the dates as integers. I have seen the
difficulties already involved with it not being in a date format.
What is the best way to convert this field to a date data type? Where in
your parameters you can do a range of dates (Between [] and []).
Pam McMillin
Systems Technician
Message #3 by pmcmillin@m... on Thu, 03 May 2001 08:37:14 -0500
|
|
John,
April 30, 2001 is stored as 4302001.
Any help would be appreciated. I haven't had all the time necessary to
fully test your changes made. My company has started this Actuate program
and I am involved in extensive training regarding that. But so far it
looks great and I can't wait to show it to the operations group. They will
be so impressed with your changes.
At 03:56 PM 5/2/2001 -0700, you wrote:
>Pam,
>
>Can you provide a sample of how the dates are stored in the AS400?
>
>
>John Ruff - The Eternal Optimist :-)
>
>-----Original Message-----
>From: Pam McMillin [mailto:pmcmillin@m...]
>Sent: Wednesday, May 02, 2001 9:24 AM
>To: Access
>Subject: [access] VB/SQL Question
>
>I have a question regarding date values being stored as integers. What is
>the best way to work with dates being stored as integers? We are working
>with a new program called "Actuate" and it is being used with our AS/400
>Artesia program which stores the dates as integers. I have seen the
>difficulties already involved with it not being in a date format.
>
>What is the best way to convert this field to a date data type? Where in
>your parameters you can do a range of dates (Between [] and []).
>
>
>
>
>Pam McMillin
>Systems Technician
>
Message #4 by "John Ruff" <John_Ruff@m...> on Thu, 3 May 2001 08:11:07 -0700
|
|
Pam,
Call this function from within your code to convert the integer to a date.
Public Function ConvertAS400Date(AS400Date As Long) As Date
Dim strDate As String
' Places the Date from the AS400 into a string.
' The Trim function removes the hidden +- from the string.
strDate = Trim(Str(AS400Date))
' If the month is 1 thru 9, the length of the string will be 7.
' If the month is 10 thru 12, the length of the string will be 8.
If Len(strDate) = 7 Then
ConvertAS400Date = DateSerial(Right(strDate, 4), Left(strDate, 1),
Mid(strDate, 2, 2)) ' Return a date.
ElseIf Len(strDate) = 8 Then
ConvertAS400Date = DateSerial(Right(strDate, 4), Left(strDate, 2),
Mid(strDate, 3, 2)) ' Return a date.
End If
Debug.Print ConvertAS400Date
End Function
John Ruff - The Eternal Optimist :)
-----Original Message-----
From: Pam McMillin [mailto:pmcmillin@m...]
Sent: Thursday, May 03, 2001 6:37 AM
To: Access
Subject: [access] RE: VB/SQL Question
John,
April 30, 2001 is stored as 4302001.
Any help would be appreciated. I haven't had all the time necessary to
fully test your changes made. My company has started this Actuate program
and I am involved in extensive training regarding that. But so far it
looks great and I can't wait to show it to the operations group. They will
be so impressed with your changes.
At 03:56 PM 5/2/2001 -0700, you wrote:
>Pam,
>
>Can you provide a sample of how the dates are stored in the AS400?
>
>
>John Ruff - The Eternal Optimist :-)
>
>-----Original Message-----
>From: Pam McMillin [mailto:pmcmillin@m...]
>Sent: Wednesday, May 02, 2001 9:24 AM
>To: Access
>Subject: [access] VB/SQL Question
>
>I have a question regarding date values being stored as integers. What is
>the best way to work with dates being stored as integers? We are working
>with a new program called "Actuate" and it is being used with our AS/400
>Artesia program which stores the dates as integers. I have seen the
>difficulties already involved with it not being in a date format.
>
>What is the best way to convert this field to a date data type? Where in
>your parameters you can do a range of dates (Between [] and []).
>
>
>
>
>Pam McMillin
>Systems Technician
>
|
|
|
View Cart
