Page 1 of 1

Numerical Order of Records

PostPosted: Tue Jun 06, 2017 10:11 am
by royaltiger
I'm struggling to keep a database in numerical order.I made a database of months and years starting from 2009 up to present.with a numerical order YearID, So January 2009 is YearID 01 and January 2017 is YearID 97. They are pulled into another Database using the Database popup field, and all 3 fields are collected using the group value. Everything was going OK until I've now reached the YearID 100, This number is now in position 11 instead of 100, I've changed the YearID Field from text to Integer to Float but cannot keep it in order. I've struggled for ages on these databases trying to keep them in order but always come up with a problem. Obviously the database popup is used for convenience in moving 3 fields at once, that's what I like about it, but when it comes to sort order, I cannot come up with a simple solution where Months are in order, and years are in order at the same time. Have I made a simple thing complicated, or asking too much from database. Is the database popup field not recognizing field as an integer and only as text

Re: Numerical Order of Records

PostPosted: Tue Jun 06, 2017 7:01 pm
by mjhanna
You have run into what in 2000 was called the Y2K problem <grin>. There are lots of other possibly solutions to your problem, but I believe the problem is retrieving these values. I think they always retrieve text. You can use a calculation field based on the retrieved field to convert them. Or you could use a six digit field where Feb 2017 would be: 201702. That six digit field will always sort.

Re: Numerical Order of Records

PostPosted: Sat Jun 17, 2017 7:58 am
by Brian_Houghton
Michael is correct that the DB Popup pulls data as text. You can use the result within a calculation, for example adding the value plus zero, and setting the output to be an integer, to give you a numerical value again.