Easiest approach is probably to import the data as is to a temporary table
with the text date in it, and the 3 separate name fields.
Create a second table that's what you want the data to look like (use a
Date/Time field instead of the Text field for the date). As to the names, I
think you're better off leaving them as 3 separate fields (we'll get back to
this)
Create a new query based on your temporary table. Drag all of the fields to
the query grid, then change your query to an Append query (you'll find this
on the Query menu) and put in the name of the second table.
If you kept the same names between the two, you should have field names in
the Append To: row of the query grid. Fill in any of the ones that are
missing.
Find the field that represents your text date. Assuming that field is called
something like MyDate, replace the word MyDate in the field row of the query
grid with DateSerial(CInt(Left$([MyDate], 4)), CInt(Mid$([MyDate], 5, 2)),
CInt(Right$([MyDate], 2)))
Run the query (hit the Exclamation mark, or choose Run from the Query menu),
and your new table should now be correct.
Getting back to the names. You would not believe the number of times the
question "How do I split my Name field into its component parts?" posted
here. I personally would leave it the way it is. However, for those times
when you need it concatenated, create a query on your table, and add a
computed field that uses the formula I posted before. You do this by typing
something like the following into the Field cell in an empty column in your
query grid:
FullName: [LastName] & (", " + [FirstName]) & (" " + [MiddleName])
Use the query wherever you would otherwise have used the table, and you'll
have the best of both words.
--
Doug Steele, Microsoft Access MVP
Bob said:
Doug,
I appreciate your help. I have to beg you for
some more help. I'm a major NEWBIE when it comes to
programming. Where do I put those lines in at? Also the
text file I have is a space delimited file that has a
whole bunch of information in it. So I'm assuming I
create a query to make this table? I'm a bit lost of
where to create the table and how to merge it back in
with my regular data. I would appreciate any help you can
give me.
Thanks again,
Bob
-----Original Message-----
If your text date is called txtDate, and is in yyyymmdd format, you can use
DateSerial(CInt(Left$(txtDate, 4)), CInt(Mid$(txtDate, 5, 2)),
CInt(Right$(txtDate, 2)))
to convert it into a date. (watch for word-wrap: I entered that all on one
line)
To merge your names, try something like:
[LastName] & (", " + [FirstName]) & (" " + [MiddleName])
The combination of & and + as concatenation symbols will handle those cases
when FirstName and/or MiddleName are Null.
--
Doug Steele, Microsoft Access MVP
bob said:
I need some major help!!! I have two problems.
I have some data in a text file that I imported into
access as a table. When I tried to convert my date column
as a date access kicks it out as a error on import. If I
do text it brings the dates in with no problem. YES
problem they need to be dates! I need to find a way to
convert this text column to a date one. My format is as 8
characters with the format of yyyymmdd. Any help on how
to change this will be awsome!!!
Second problem. The names that I have imported are as
follows. Last name, First name, and middle name. I need
to put all three of these columns into one column. How do
I merge these.
Wanna be Access programmer, (not really just got thrown
with a nasty job)
CoNeSsIuS
.
