import text file with text date

[rody]

Hi Guys and Gals.
I am very new to access ( 3 weeks )but for a lot of things it seems pretty easy for the basic things I have been doing and Microsoft has set us up to win... However I have a text file that I have to add to my DB every day with a date in this format AUG-28, now I can import this into access as text, and after its in the new table I can change it to a date/time field and this works fine. Another option Is to import the txt file into excel first fix the formating and then upload it to access. These methods seem to be more cumbersome than they should be.

I need to import the file with different options or automate a conversion to date after the import. I am hoping that there is a better way to import the file so that the date is a date field because looking at the cdate function looks daunting at the moment. Here is a sample of the text file and how i need it separated.
462 |Truck |25-AUG |17:44 |1480-7S-57284 |Grease |12 704 |11703 |13997
105 |Shovel |25-AUG |23:41 |0850-07-7990 |Grease |25 705 |8788 |10480
107 |Shovel |25-AUG |07:26 |1480-7S-57284 |AW46 |15 705 |8714 | 8946
|
I would appreciate any help, I am really enjoying using access so far but this issue is starting to feel like 3d chess, if only I could look at it from the right angle it would be simple.

thank-you


Rody

ps:
I tried the search function and there are a lot of threads about converting txt to datetime and most use cdate but I did not understand how to use them.

[June7]

Odd that the year is not included in the date value.

I don't think will be able to convert the text to date using the import Wizard.
What this data is really lacking is the year.

Suggest you import the data as is. Have another field in the table and run an UPDATE sql action to populate this field. Options:

1. Time is in a separate field, this should also be in a date/time field. Actually, recommend date and time be in the same field. Populate the field with the combined date and time value.
UPDATE tablename SET [field name] = CDate([date field] & " " & [time field]) WHERE [field name] Is Null
Access will assume the current year in the date conversion.

2. Just populate the field with the year value then calculate the full date/time from the 3 fields whenever needed.
UPDATE tablename SET [field name] = Year(Date()) WHERE [field name] Is Null
The calculation to use when needed would be similar to the expression from option 1.
CDate([date field] & "-" & [field name] & " " & [time field])

Of course, both options assume all the newly imported records are for the current year.

I just tried an import of the data you posted and Access wizard is not letting me select anything in the Other delimiter option. Weird.

[rody]

the reason you got delimiter I think is because of the| lines I put in to show data breaks in the text file, those are not in the real file. I have been playing with>>> date_1: Format([column with txt],"General Date") and this works and even inserts 2014 at the end all by it self, but when i try to update the table with this i get an error. I will look at your solution and see if i can figure out how to make it work, and I'm not sure how to make sure the dates match the rest of the record

[June7]

The error may be because Format function returns a string, not a date value. Must understand that a date/time field actually stores a date as a number (long integer). You see 12/13/2014 but the actual value in the field is 41986. This is why must use date specific functions such as CDate or DateValue or delimit the string with # characters.

[rody]

sum_fluid.txt

this is the file I use to update my DB every day. I don't understand where to put the code from your previous post. Do I need to make an update query to update the table and then append to the master table? I'm sorry if these questions sound dumb, everything to this point has been fairly straight forward with a little Google foo, but I'm having a hard time figuring this out. It would be very ease to combine date and time columns I only separated it to try to fix the date.

[rody]

I'm not sure if I did this right but it works now. I made my master list and converted the text date column to date time, then I imported my new days worth of records and left the date column as text. I then made an append query and to update the daily records and it appears to do the conversion from text to datetime automatically.

I really appreciate all the help, and while I did not use what you showed me, it did lead me to searching in a new direction.

thanks a bunch
rody