Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-06-2019, 04:13 PM   #1
HuwGro
Newly Registered User
 
Join Date: Jun 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
HuwGro is on a distinguished road
Convert Dates / Sorting

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

HuwGro is offline   Reply With Quote
Old 06-06-2019, 04:20 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,541
Thanks: 29
Thanked 626 Times in 609 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Convert Dates / Sorting

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-06-2019, 06:10 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,489
Thanks: 85
Thanked 1,458 Times in 1,376 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Convert Dates / Sorting

Quote:
Originally Posted by HuwGro View Post
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.
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.

Galaxiom is offline   Reply With Quote
Old 06-07-2019, 12:33 AM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,744
Thanks: 55
Thanked 1,017 Times in 983 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Convert Dates / Sorting

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.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Reply

Tags
converting dates , sorting

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Data Mining - Convert Oracle / SQL Text Dates to real dates types to move into Excel Rx_ SQL Server 0 08-09-2014 09:19 AM
Convert dates elsanto General 6 07-30-2003 01:26 AM
Sorting Dates in Reports HaleDog Reports 8 05-24-2003 02:13 PM
Sorting dates littlej General 4 03-14-2003 11:21 AM
Sorting Dates Shawny Queries 1 11-04-2002 02:24 PM




All times are GMT -8. The time now is 07:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World