Numerical Order of Records

For HanDBase related topics that are not specific to one platform.

Numerical Order of Records

Postby royaltiger » Tue Jun 06, 2017 10:11 am

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
Posts: 20
Joined: Wed Mar 16, 2016 6:29 am

Re: Numerical Order of Records

Postby mjhanna » Tue Jun 06, 2017 7:01 pm

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.
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
Posts: 164
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Numerical Order of Records

Postby Brian_Houghton » Sat Jun 17, 2017 7:58 am

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.
Kind Regards,
Brian Houghton, DDH Software
Posts: 2248
Joined: Wed May 20, 2009 8:30 am

Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 2 guests