date format into text

A

Akrt48

Help, I have an access data base that I have to transfer information into
another db. The setup of the other one is where there are birthdays or just
dates they are done in text format eg 19651031 the first data base says
31-10-65 is there a simple way I can convert for a transfer. I have been
micky mousing in excel, but its a real pain. Thanks
 
O

Ofer

Is the field in the first MDB that contain the date is a Date Format Or text?
If it's a date, you can use that
Format(DateField,"yyyymmdd")
 
C

Chris2

Akrt48 said:
Help, I have an access data base that I have to transfer information into
another db. The setup of the other one is where there are birthdays or just
dates they are done in text format eg 19651031 the first data base says
31-10-65 is there a simple way I can convert for a transfer. I have been
micky mousing in excel, but its a real pain. Thanks
Click to expand...

Akrt48,

Public Function ModifyDate(InputDate As String) As String

Dim ModYear As String
Dim ModMonth As String
Dim ModDate As String
Dim FinalDate As String

ModYear = IIf(Val(Right(InputDate, 2)) <= 99 _
And _
Val(Right(InputDate, 2)) >= 50 _
, "19", "20")

ModYear = ModYear & Right(InputDate, 2)

ModMonth = Mid(InputDate, 4, 2)

ModDate = Left(InputDate, 2)

FinalDate = ModYear & ModMonth & ModDate

ModifyDate = FinalDate

End Function

The above changes: "31-10-65" into "19651031"

Adjust the IIf statement to meet your needs on how you are converted
two digit years to four digit years.


Sincerely,

Chris O.
 
A

Akrt48

Wow not sure about this I will try the date format first and use this when I
understand it. But thank you both so much!
 
S

Steve Schapel

Akrt48,

How you apply it will depend on how you are doing the data transfer to
the other database. If you are using a TransferText macro or
DoCmd.TransferText method or TransferDatabase macro or
DoCmd.TransferDatabase method, you can just do the transfer based on a
query. And in the query, in the place of your date field, you put a
calculated field using Ofer's idea, i.e. in the Field row of the query
design grid you put the equivqalent of...
TextDate: Format([YourDateField],"yyyymmdd")
 
C

Chris2

Akrt48 said:
Wow not sure about this I will try the date format first and use this when I
understand it. But thank you both so much!
Click to expand...


Akrt48,

Format(DateField,"yyyymmdd") will convert "31-10-65" into "19651031"
just as well as the VBA function.

Also FORMAT will do:

"31-10-99" gets changed into "19991031"
"31-10-00" gets changed into "20001031"

But:

"31-10-10" gets changed into "19311031"
"31-10-15" gets changed into "19311015"

So, basically, FORMAT can switch around what it thinks is the year in
a string. You don't control that.


When the VBA fuction is used on those dates, you get:

"31-10-99" gets changed into "19991031"
"31-10-00" gets changed into "20001031"
"31-10-10" gets changed into "19101031"
"31-10-15" gets changed into "19151031"

Which is, I think, what you were expecting.


Also, using the VBA function:

"49-10-00" gets changed into "20491031"
"50-10-00" gets changed into "19501031"
"31-10-00" gets changed into "20001031"
"31-10-01" gets changed into "20011031"

You get to control how two digit years get converted into four digit
years.

With FORMAT:

"49-10-00" gets changed into "19491031"
"50-10-00" gets changed into "19501031"
"31-10-00" gets changed into "20001031"

But:

"31-10-01" gets changed into "19311001"


Sincerely,

Chris O.
 
S

Steve Schapel

Chris,

I am pretty sure that Akrt48 is trying to export Date data. The Format
function will get it right every time. The value of the date is not
affected by its format.
 
C

Chris2

My apologies:



Format(DateField,"yyyymmdd") will convert "31-10-65" into "19651031"
just as well as the VBA function.

Also FORMAT will do:

"31-10-99" gets changed into "19991031"
"31-10-00" gets changed into "20001031"

But:

"31-10-10" gets changed into "19311031"
"31-10-15" gets changed into "19311015"

So, basically, FORMAT can switch around what it thinks is the year in
a string. You don't control that.


When the VBA fuction is used on those dates, you get:

"31-10-99" gets changed into "19991031"
"31-10-00" gets changed into "20001031"
"31-10-10" gets changed into "19101031"
"31-10-15" gets changed into "19151031"
Click to expand...

That should be:

"31-10-10" gets changed into "20101031"
"31-10-15" gets changed into "20151031"

Which is, I think, what you were expecting.


Also, using the VBA function:

"49-10-00" gets changed into "20491031"
"50-10-00" gets changed into "19501031"
Click to expand...

That should be

"31-10-49" gets changed into "20491031"
"31-10-50" gets changed into "19501031"

---------------------------------------------

Sorry, that's what happens when you stop paying attention to what
column you are changing the numbers in as you type in one window and
read in another.


Sincerely,

Chris O.
 
C

Chris2

Steve Schapel said:
Chris,

I am pretty sure that Akrt48 is trying to export Date data. The Format
function will get it right every time. The value of the date is not
affected by its format.
Click to expand...

Steven,

Oh. :eek:

It seemed like the OP was exporting a text data type stored as
"mm-dd-yy" in db#1 to an external string/character data type in db#2.


Sincerely,

Chris O.
 
S

Steve Schapel

Well, that's not how I read it, but you could well be right... maybe
we'll never know :)
 
Top