date format setting

[whistler]

Hi, I'm trying to accomplish through some VBA code:
read from a flat text file (fixed width) some information to be put in an Oracle table through ODBC.

Nothing special. But the text file has some "date fields" in the format "dd-mmm-yyyy", i.e. the 4th of March reads "04-MAR-2008. Some of the date values are however not correctly imported into the table, leaving the field in question blank after import is finished.

I am in the Netherlands, and I suspect that the problem comes from a difference in "date-format" setting between my MsAccess front end (version 2003) and the application that creates my input file. This latter being from another company's application, it is probably more straightforward to change my VBA code than to ask them to change their file layout.

Can somebody give me hint how to check and manipulate date format settings of my application to avoid the above problem ?

Thanks in advance,
Jos


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[Jeff Boyce]

Have you looked into the CDate() function in a query. It might be that it
can convert your text string into a proper Access Date/Time field value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

Whistler,

Is there any rhyme or reason to which dates don't get imported correctly?

1. I recommend that you create a local Access table that you are going to
import the data into before exporting to Oracle. Define this date field as a
text field. This should make the import run better.

2. I also recommend that you create an import specification, and define
that date field as text. You do this using the File - Get External Data -
Import option. After you have identified the file to import, there is an
Advanced Option (don't forget to name and save the specification).

3. Once you have done this, you can use the cdate function to have Access
convert your "dd-mmm-yyyy" format into mm/dd/yyyy. Then, you can use the
Format( ) function to define the proper format for inserting into Oracle.

HTH
Dale