.DBF Date Format

[ChrisBat]

Hi,

I have a .DBF file that has 233,221 records; the data set
was extracted from a UNIX based system using SAS. I have
imported it into Access 2000 without any trouble. I have
2 columns that are supposed to be dates, and one that is
supposed to be text, but are instead Number formats. I
have tried to change the Data Type in the design view, but
keep getting the error message Not enough Memory. I have
tried chunking the data into smaller tables, but keep
getting this error message. I have copied a portion of
the data into Excel, and have tried changing the data
formats here. Instead of getting the date, I get a whole
pile of # in the cell. My thought is that when the data
was extracted, the Date Format was not specified, but I
don't know enough about SAS programming to know if Formats
need to be specified.
Is there anybody out there that can help????
Any suggestions would be appreciated. Re-running the data
is an option, but I would rather try other avenues - the
guy that ran the SAS program for me is on vacation until
the middle of next week, and I need the data ASAP.
Thanks,
Chris

 

[John Nurick]

Hi Chris,

Access normally picks up the field types and sizes from the .dbf file,
so the problem probably is with how SAS created the file.

First, try this. Compact your database. Open the table in design view
and add a new date field and text fields. Then create an update query
that converts the data in the existing fields into the appropriate
format and puts it in the new fields. Finally, delete the old fields and
compact again.

For the conversion, use VBA functions in calculated fields in the update
query. For example,
TextField: Format([NumberField], "00000")
converts a numeric field into a 5-character string with leading zeros.

If you still hit problems with memory or file size limits, don't import
the original table. Instead, use File|Get External Data|Link to link to
the .dbf file. Then create a new table with the field types and sizes
you really want, and use an append query to move and convert the data
from the linked table.