I set up a database, there's a file i originally set up as text and the format was defined as "99\->L<LL\-0000;;_" so that the date can be entered as 12-jun-2008. however I realized that it's impossible for me to do any query with this format. I was wondering whether it can be converted to date using the same entry as 12-jun-2008 or 12-06-2008? Can anybody help? And because I am really not good at computer, please using simple terms as I don't know the technical terms at all? Thanks a lot!!!!
how to convert text into date
- Thread starter dontknow
- Start date
Brianwarnock
Retired
- Local time
- Today, 22:55
- Joined
- Jun 2, 2003
- Messages
- 12,701
Add another field to your table with Format medium date, I'll call it createddate.
Then run an update query selecting the field createddate and in the update to field put
Cdate([yourcurrenttextdatefield])
Brian
Then run an update query selecting the field createddate and in the update to field put
Cdate([yourcurrenttextdatefield])
Brian
Hello,
This is my first time doing this.
There are a lot of ways to do this. You could open the table in design view and change the data type to date/time. Or if you wanted to learn something more you could copy and paste this simple function into a module and then setup a query like this.
Function ConvertMonth(strMonth As String) As Integer
Select Case strMonth
Case "jan"
ConvertMonth = 1
Case "feb"
ConvertMonth = 2
Case "mar"
ConvertMonth = 3
Case "apr"
ConvertMonth = 4
Case "may"
ConvertMonth = 5
Case "jun"
ConvertMonth = 6
Case "jul"
ConvertMonth = 7
Case "aug"
ConvertMonth = 8
Case "sep"
ConvertMonth = 9
Case "oct"
ConvertMonth = 10
Case "nov"
ConvertMonth = 11
Case "dec"
ConvertMonth = 12
End Select
End Function
Query
Field
DateSerial(Right([Date],4),ConvertMonth(Mid([Date],4,3)),Left([Date],2))
Enjoy!
Art
scalextric59
Registered User.
- Local time
- Today, 14:55
- Joined
- Dec 20, 2008
- Messages
- 87
Use DateValue("12-Jun-2008). You will get 06-12-2008. Use the date formats included in Access
Hi Brian, I tried your suggestion, but it showed "data type mismatch in criteria expression". I don't know whether there's something wrong I did, I selected "createdata" in the "update to" field" in the query. any solution?
thanks!
thanks!
Hi Art, thanks for the suggestion. I tried to just change the data type from text to date, but it didn't work and I'm at the risk of loosing all the data.
I tried to use the second suggestion you provided, I set up a new module and copied the function, however, I got lost in the query section. I guess I was supposed to select a query, but I really don't know what to do with "
DateSerial(Right([Date],4),ConvertMonth(Mid([Date],4,3)),Left([Date],2))", where am I suppose to put it? I am really a dummy on access and not familiar with all the functions at all. Please bear with my ignorance and be patient. Thanks for your help in advance!!!!
You could put 'DateSerial(Right([Date],4),ConvertMonth(Mid([Date],4,3)),Left([Date],2))' into the 'Field:' row of a select query and this will display the date. You may then use an actual date in the 'Cirteria:" row to display the results you need.
Hope this helps.
Art
Hope this helps.
Art
stopher
AWF VIP
- Local time
- Today, 22:55
- Joined
- Feb 1, 2006
- Messages
- 2,395
Might be worth some extra explanation here. There are two fundamentally different solutions being presented here...
Scalextric and Art's code show a solution where the data in your table remains the same. But instead, you use their functions to give you the date in a date datatype when you need it e.g. for sorting, formatting or other date manipulation. If the date is legal then these should work fine. The downside is you will always have to keep using them as your underlying data will still be a string.
Brian's solution and Art's proposal to change the data type in the table design will actually change the data in your table. Personally I'd take a copy of the database then just try Art's proposal to see what happens i.e. open the table in design view and change the data type to date/time. You will be informed if any of the data can't be changed e.g. if a record is an illegal date. The problem with this is that if you do have illegal entries, you don't get much clue as to which ones. If you do Brians solution in a select query for starters, then you can examine the results. Illegal entries will show as errors.
Chris
Scalextric and Art's code show a solution where the data in your table remains the same. But instead, you use their functions to give you the date in a date datatype when you need it e.g. for sorting, formatting or other date manipulation. If the date is legal then these should work fine. The downside is you will always have to keep using them as your underlying data will still be a string.
Brian's solution and Art's proposal to change the data type in the table design will actually change the data in your table. Personally I'd take a copy of the database then just try Art's proposal to see what happens i.e. open the table in design view and change the data type to date/time. You will be informed if any of the data can't be changed e.g. if a record is an illegal date. The problem with this is that if you do have illegal entries, you don't get much clue as to which ones. If you do Brians solution in a select query for starters, then you can examine the results. Illegal entries will show as errors.
Chris
Brianwarnock
Retired
- Local time
- Today, 22:55
- Joined
- Jun 2, 2003
- Messages
- 12,701
Sounds like you misunderstood
Field CreatedDate
Table yourtable
Update to Cdate([yourcurrenttextdatefield])
Brian
