Tech Support Guy banner
Status
Not open for further replies.

Solved: Date Format in Access

5K views 6 replies 2 participants last post by  OBP 
#1 ·
Can anyone give me a hand with this? I have imported some data into Access with dates written in the form mddyyyy (for Feb 14, 2005 it gives 2142005 and for Dec 25, 2006 it gives 12252006) i.e. they can be 7 or 8 digits long and are not formatted. The field is [date needed].

What I would like to do is show the records containing the date on a form sorting them in reverse chronological order. I set up a query to get the data from the table and set up my subform and then it seems I would have to dissect the date into year, month and day portions and sort each of them. [Maybe there's a way to handle dates like this built in but I do not know] It's easy enough to get the year with the right function as in Year: Right([date needed],4) but the days and months are a little more difficult since the leading month can be either 1 or 2 digits. Note that the days are always two digits i.e. 01 for the first of the month. If the month is 1-9 then there are 7 digits in the date and the month is the first character and the days are characters 2 and 3. If the month is 10, 11 or 12 then the month is the first two digits and the day the second two.

My question is whether this is the right way to do it and can a query field use some sort of if/then/else statement with the len function. What would it look like? If this could be done then I could use the Left function to get the month (i.e. first digit only when len = 7 and first two digits when len=8) and the mid function for the days (second and third digits when len=7 and 3 and 4 when len=8).

If this is not possible then is it possible to format the data in the table as mm/dd/yyyy? In that way it would be easier to use date sorting or break the date apart since the number of digits would be invariant?
 
See less See more
#2 ·
Dennis, you can use iif, left right, mid and len in a query, but would it be better to do the same with some VB to format the Dates correctly?
i.e. you could add the leading zero to the month part of each of the dates that have a length of 7 characters.
Are they in the Table as Dates or text?
 
#3 ·
They are present as text fields. Unfortunately in their current state Access does not recognize them as dates.
So is what you're suggesting to alter the data to change all the 7 digit records to 8 digit in the table holding the data? I could do this at the time I import the data.

Dennis
 
#4 ·
Dennis, I would import them as they are and then manipulate the data with VB to turn them in to real dates and at the same time transfer them to a Date field in your table.
If you can import them in to table and then post it on here or email it to me I will convert and repost it.
 
#5 ·
Dennis, the attached database has a table, query & form. If you check the data in the table you will see it is 7 and 8 character text dates. The form's button has the VB that will do just what you want. It has message boxes in it that are not necessary, they are just there to show you the conversion from text to date.
 

Attachments

#6 ·
Thanks, OBP. I solved it independent of your second post after reading the first post and decided to do it within a query: my data is downloaded from a mainframe and I bring i into Access by an Append query after deleting the previous data. Rather than bring it in verbatim I modified [date needed] to look like mm/dd/yyyy. This will be more normal to (American) readers.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top