I'm imported data from a csv file. The dates stored in there are in a dd-mm-yyyy time format. How can I work with this since Access's date format is just mm/dd/yyyy? I imagine I need to do some sort of conversion? Does Access provide anything for me to do this?
Date Conversion (1 Viewer)
- Thread starter Piller187
- Start date
if you rename the file to a text file then you will get a Wizard when you import which will give you more choices.
You could use a update query to put real dates into a new column in the table
MyDate :dateserial(right([TextDate],4),left([textDate],2),mid([textdate],3,2))
Air code
but should be close
HTH
Peter
You could use a update query to put real dates into a new column in the table
MyDate :dateserial(right([TextDate],4),left([textDate],2),mid([textdate],3,2))
Air code
but should be closeHTH
Peter
Something I tried was to change the column def to short date. It shows just the short date but if I query on it it doesn't find it. THen I did a highlight in the column and dragged mouse right and it then showed the tim ein there also. Very strange. I was hoping it would remove the time that was on there. In fact it only hide it. But it's still there which means I can't just query on the date.
Last edited:
ghudson
Registered User.
- Local time
- Today, 18:43
- Joined
- Jun 8, 2002
- Messages
- 6,195
Access will still recognize your dates as long as the field you are updating is set to a date/time field. You can use the format function to display the date in a format you want.
=Format([YourDateField],"MM/DD/YYYY")
You can use that in an update query to change the format of the date from the import to the table. An import spec could [should] also allow you to do it.
=Format([YourDateField],"MM/DD/YYYY")
You can use that in an update query to change the format of the date from the import to the table. An import spec could [should] also allow you to do it.
ghudson said:
Thanks for reminding me of this ghudson!!!! I'm working on a little parameter input form and I needed 2 sets of date prompts, one set with long date and one set with short date. The set with short date is equal to the set with long date, but I needed the 2 formats because of the underlying data's date formats, which must be kept this way. Been banging my head on not being able to pull the report for a day till I realized it was a date format issue, and then spent another hour or so trying to remember how to do this on the parameter form until I did a search here.
Seek and ye shall find indeed! Thanks again.
ghudson said:Glad to hear somebody knows how to use the forums search function.
AccessBoy, are you reading this?!?!
ghudson,
almost every issue I've had, I've been able to run the answer down in a search, because of all the info here. It may take a while, especially in trying to "ask" the right search, but it usually bears fruit. I think the only big issues I had were a screwy text file import, dynamic Xtab reports, and what to expect on change in size of DB when one finally normalizes their table structure, vs old, non-normal structure. Probably could have had one of these answered by searching deeper.
Thanks again!
