-
ACCESS Help Converting text to Dates
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
-
My format is as 8 characters with the format of yyyymmdd. Any help on how to change this will be awsome!!!
Go ahead and import it as text.
Add a date field, dtmSomeDate.
Let's say your text field is strDateField.
Create a query that updates the dtmSomeDate field with the information from your text field reformatted as follows.
CDate(Mid(strDateField,5,2) & "/" & Mid(strDateField,7,2) & "/" & Left(strDateField,4))
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
Same gig, in fact you could do it in the same query
Add a field, strFullName.
Update it with
[First name] & " " & [middle name] & " " & [Last name]
If you want to do anything like removing the extra space when there is no middle name, etc. post back I can give you a chunk of code for that.
-
AltatemTC,
Man I appreciate the help greatly. I finally figured out about a week ago on how to do it. Howerver that no middle space for name would be nice to have. Everybody kept telling me to put that code in and I couldn't figure out where to put it. I was in the Sql editor, the Modules editors, etc etc. I never relized to put it inside of the top line of the query. I did put it in the criteria field but to no anvil no results. Then I was like how do you get a query into a table. So then I figured out that you take the data base that you want the table to be in and import the query as a table from the old database into the new. It all worked greatly!!! I was so happy.
Then all my fields lined up like they should. The date's got reconized, number were able to be numbers, etc etc. Thanks again.
CoNeSsIuS
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|