Convert Dates / Sorting (1 Viewer)

Hi,

I have 2 problems partly inter related. In a field (Rev) in a table structure where no actual rev was on the document I picked up from another access app the date. What I would like to do is change that from dd/mm/yy to yyyy (or yy)mmdd as a number.

Then I need to do something a bit weird.

I need to sort on this field so that letters fall below numbers eg. 2, 1, 0, D, C, B, A and then the converted date. The first part is hard enough the second I think near impossible and I may have to resort to a second field in the table for the numerical date.

All help appreciated.

Thanks

Huw

Hi. Welcome to the forum. In Access, it’s easier to combine things than to parse them. So, if you think you can use two fields, I’d say go for it, if it will help you do your job easier.

HuwGro said:

I need to sort on this field so that letters fall below numbers eg. 2, 1, 0, D, C, B, A and then the converted date. The first part is hard enough the second I think near impossible and I may have to resort to a second field in the table for the numerical date.

Click to expand...

Your first sort isn't natively supported so you would be deriving another field just to sort it like that. Then the field for the modified date.

Your sort will be slow because every record will need to be read and processed. If it is a big table you may find it loads very slowly because all that processing has to complete before the first record loads.

If users are regularly subjected to this, it might be better to derive a value that can be sorted natively and save it when the records are inserted. An index on this field will allow the results to be sorted almost instantly giving your users a better experience.

if you have a date in format dd/mm/yy - where are you getting dates with "letters"? Those are sort of regular dates, although access may not treat them as dates. They just don't have the century. There won't be any letters in them.

Hi

I got the date thing sussed, copy out to Excel and use a formula - worked well - pasted back. Found some code to sort the Revisions in the, near, right order.