Converting Date Format in an Access Table 1

[jeffy]

I have imported data from Oracle, into an Access Table.<br>
I need to export the data out of Access, into another database file format (Foxpro).<br>
The problem is with the &quot;date&quot; field. It is data type: date, and the format is m/d/yy.<br>
Ideally I would like to convert the dates to data type: text, formatted as yymmdd (no slashes). This is necessary to be able to compare it to other data, previously entered in data type: text, for example 990131 for Jan 1, 1999. Alternatively, if I could convert the data type: text, formatted as yymmdd to data type: date, formatted as m/d/yy, that would be acceptable. I have been able to change the DISPLAY format, but cannot figure out how to change the STORED value that would be exported. This is my first post, after just now discovering this forum, and I would appreciate any help with this. Thanks.

 

[DougP]

I start by breaking the dates parts out in separate variables.<br>
<br>
Public Function ConvertDate(mydate)<br>
Dim x, year1, month1, day1 As Variant<br>
year1 = Year(mydate)<br>
month1 = Month(mydate)<br>
day1 = Day(mydate)<br>
'now assemble how ever you want<br>
' no slashes<br>
x = Right(year1, 2) & month1 & day1<br>
ConvertDate = x<br>
End Function<br>
<br>
also<br>
<br>
Date is 970630 convert to 06/30/97 in one line.<br>
&lt;&lt;Current format is 970630, which I want to change to a date field as 06/30/97.&gt;&gt;<br>
Is the current format always yymmdd? If so, you can perform the date conversion with a few format statements:<br>
format(format$(960630, &quot;@@/@@/@@&quot, &quot;mm/dd/yy&quot<br>
<br>
Not really sure which way you want to go but these may help<br>
<br>
Also I see you are using Access as a go between for Oracle and FoxPro<br>
Did you know Access can LINK to both of them directly.<br>
<br>
Right click on the tables TAB and click &quot;Link Tables&quot;<br>
Then click &quot;Files of Type&quot; at the bottom<br>
you will see ODBC which Oracle has drivers for and &quot;Microsoft Foxpro&quot; <br>
<br>

 

[jeffy]

Thanks for the reply!<br>
I can go either way with this - would like to understand both, but will be happy to get one working.<br>
One table has dates as data type:text, as 990601.<br>
The other table has dates as data type:date/time, as 6/1/99 (note:not 06/01/99).<br>
I need to convert the dates so that both tables have them in the same format.<br>
Doesn't matter which format, as long as they are the same.<br>
<br>
I created a module, for the ConvertDate function.<br>
When I ran the Update Query, got the following:<br>
"MS Access can't update the fields due to a type conversion failure".<br>
<br>
Next I looked at your suggestion to create the "format statements", but I don't know what to do with this, where to put it, etc.<br>
<br>
Regarding your suggestion about the LINKS, the links were used between Oracle and Access. The Foxpro is on a separate system, that doesn't have Access or Oracle. The Access file, if it can be properly formatted for the date fields, will be saved as a Foxpro file, and then loaded into Foxpro for further processing.<br>
<br>
Thanks again - I hope you will be able to fine tune this potential solution, based on my feedback.

 

[elizabeth]

To change m/d/yy to yymmdd:<br>
NewDate:Year([OldDate])] & IIf(Month([OldDate])&lt;10,&quot;0&quot;,&quot;&quot & Month(OldDate]) & IIf(Day([OldDate])&lt;10,&quot;0&quot;,&quot;&quot & Day([OldDate])

 

[DougP]

So I guess the FoxPRO database cannot be seen by going in Network neighborhood.<br>
If it can you can attach to it.

 

[jeffy]

Elizabeth: Your function successfully returns a data type:text, but in the<br>
format yyyymmdd. I need the date to be yymmdd. We're very close here - perhaps<br>
you can give me the final clue. Thanks.

 

[DougP]

Try this<br>
<br>
NewDate: Format(Year([Date]),&quot;yy&quot & IIf(Month([date])&lt;10,&quot;0&quot;,&quot;&quot & Month([date]) & IIf(Day([date])&lt;10,&quot;0&quot;,&quot;&quot & Day([DAte])<br>
<br>
Slick one liner Elizabeth

 

[jeffy]

When I looked at DougP's fix, inserting the &quot;yy&quot; in the year format, I thought &quot;that's it!&quot;<br>
However, when I run the query it returns &quot;05&quot; as the &quot;yy&quot; for every entry, regardless of the actual date. The month and day comes out perfectly, but I don't u/stand why the year is 05. Any ideas?