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?
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?