Converting Text File to Date Format

  • Thread starter Thread starter Steve Hartman via AccessMonster.com
  • Start date Start date
S

Steve Hartman via AccessMonster.com

I imported a text file that displays a date as 40105 (4/01/05). I'm trying
to use the DateSerial function with an update query to change the text
value into a date that access recognizes, but am having trouble. I'm sure
my syntax is wrong. Can anyone help?
 
within the text file itself (before you import it), do the date values
include a leading zero for single-digit months, such as 040105? or including
the slash marks (4/01/05)?
 
no leading zeros and no slash marks just 31105, 41505, 110205, etc...
 
i populated a test table with a text field showing values, as

41505
40105
120105
101505
30205
81105

then ran an Update query using the following, as

DateSerial(Right([MyTable].[MyTextField],2),Left([MyTable].[MyTextField],Len
([MyTable].[MyTextField])-4),Mid([MyTable].[MyTextField],Len([MyTable].[MyTe
xtField])-3,2))

(all on one line, of course) which returned

4/15/2005
4/1/2005
12/1/2005
10/15/2005
3/2/2005
8/11/2005

note the following, from the Remarks section of the DateSerial Function VBA
Help topic:
"Under Windows 98 or Windows 2000, two digit years for the year argument are
interpreted based on user-defined machine settings. The default settings are
that values between 0 and 29, inclusive, are interpreted as the years
2000-2029. The default values between 30 and 99 are interpreted as the years
1930-1999. For all other year arguments, use a four-digit year (for example,
1800).

Earlier versions of Windows interpret two-digit years based on the defaults
described above. To be sure the function returns the proper value, use a
four-digit year."

also, keep in mind that if you're updating a text field in the table, the
value saved is still a text data type, not a date data type.

hth
 
Back
Top