Date and time format conversion problem (not the usual)

[Bruce Cushman]

My DB is Access 2000 based "possibly written in 2002" and I have to send and
receive update files, ported to a UNIX server by FTP, DB unknown.
The file content uses a fixed length string, zero filled left.
ie;

001332001695001995040601040630

where,
001332,001695,001995,040601,040630

Filename: Newprice.txt - detail record

Field Name
Data Type
Description

OLCC Item Code
Numeric(6,0)
Zero filled to the left.

New Price
Numeric(6,2)
Old Price
Numeric(6,2)
Start Date
Numeric(6,0)
Format YYYYMMDD. Beginning date when new price takes effect.

End Date
Numeric(6,0)
Format YYYYMMDD. Last date when new price no longer applies.

My problem is that Microsoft DB format does not accept dates without
delimiters ie; / / or , , or _ _
The same goes for a similar time format in another file where time is hhmm
as opposed to MS hh:mm
How can I convert or get Access to import and export this format.

ie: yymmdd=040520=04/05/20
and 04/05/20=040520

I have to go both ways.

I hope I have provided enough information and appreciate any help you can
provide.

Bruce "Luckydog"

[Allen Browne]

Hi Bruce.

Parse the text field with Mid(), and generate the dates with DateSerial()

If the text field is called "d", and contains strings of numbers
representing yymmdd, this example pulls out the 2nd date:
DateSerial(Mid([d], 7, 2), Mid([d], 9, 2), Mid([d], 11, 2))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bruce Cushman" <cu*****@hevanet.com> wrote in message
news:20*******************@news.newsreader.com...

My DB is Access 2000 based "possibly written in 2002" and I have to send and receive update files, ported to a UNIX server by FTP, DB unknown.
The file content uses a fixed length string, zero filled left.
ie;

001332001695001995040601040630

where,
001332,001695,001995,040601,040630

Filename: Newprice.txt - detail record

Field Name
Data Type
Description

OLCC Item Code
Numeric(6,0)
Zero filled to the left.

New Price
Numeric(6,2)
Old Price
Numeric(6,2)
Start Date
Numeric(6,0)
Format YYYYMMDD. Beginning date when new price takes effect.

End Date
Numeric(6,0)
Format YYYYMMDD. Last date when new price no longer applies.

My problem is that Microsoft DB format does not accept dates without
delimiters ie; / / or , , or _ _
The same goes for a similar time format in another file where time is hhmm
as opposed to MS hh:mm
How can I convert or get Access to import and export this format.

ie: yymmdd=040520=04/05/20
and 04/05/20=040520

I have to go both ways.

I hope I have provided enough information and appreciate any help you can
provide.

Bruce "Luckydog"