Sort not working (Ascending) (1 Viewer)

[Poppy]

Goodday All

I am having a bit of a problem with one of my tables. I am using VBA code to add data to one of my tables, my problem is that even when I manually sort the table, it still gives me problem.

For example, I have a table that stores mobile numbers by year and month. I manually sort it by year- Ascending, but it still gives me this jumbled up table. I even tried writing a query to pull out the data and set the Sort by ascending and yet still it gives me this. What am I doing wrong?

MobileNo	             Year	Month	Rental	   Fees 	
+2724121428	2004	Dec	0.00	89.60	
+2724587374	2004	Dec	0.00	607.89	
+2725680161	2004	Dec	0.00	607.89	
+2724599866	2005	Feb	0.00	246.00	
+2724679023	2005	Feb	0.00	246.49	
+2724927556	2005	Feb	0.00	335.96	
+2727811585	2005	Jan	0.00	782.04	
+2724944253	2005	Jan	0.00	357.40	
+2727810066	2005	Jan	0.00	137.70

Thanx for all the help

Kind Regards

 

[Uncle Gizmo]

It looks to me like that table is sorted on year, and then month name. This sort order is correct for the month name because the months are in ascending order.

If you want the months to sort in Chronological order you will need to extract the month number from the date and sort by that. For example the month number for January is 1 and the number for February is 2.

this code will return the month number for the date entered where "fldDate" is the name of your field with the date in it. use this in your query and sort by this field .



DateCronOrder: DatePart("m",[fldDate])

 

[Uncle Gizmo]

Correction , sort by the year field ascending , and then the Month chronological field ascending.

 

[Rich]

Since tables are used only to store data their sort order is irrelevant, why do you need to do this?

 

[Poppy]

Hi Gizmo

Is there a way to do this using a macro?

Thanx

Kind Regards

 

[Poppy]

Since tables are used only to store data their sort order is irrelevant, why do you need to do this?

Well since the person working on the database is not very comfortable with pcs, she gets easily disconcerted when things aren't as they should be. I want to avoid all the questions now. And actually it's bugging me as well that things are not in order.

Kind Regards

 

[Mile-O]

Having month and year as different fields is not going to help you. You just need one date field that contains all this information.

 

[neileg]

And actually it's bugging me as well that things are not in order.

The sort is correct. The field that you see as a month is just a piece of text and is being sorted in aplabetical order. That's why SJ McA made his comment. If you had one field that held month and year as a date/time field this would sort the way you want it to.

 

[Poppy]

Aaaaaaaaaaaaaaaaaahhhhhhhhhh die! die! die sort die!

Good day Experts (Make that bad day)


Uum excuse my rantings. I really appreciate all the good advice given to me so far, however my user is now telling me she does not want the data sorted according to year and month at all. What she wants is her own arrangement. In other words she wants to put records in any order that she wants in a table (Dont know if that makes sense).

While the rest of us humans would want this table sorted in Ascending or Descending Order like this:

Cell Phone Number	Year	Month	Rental	Fess 
+2723337826	        2005	Mar	0.00	335.96	
+2723718369	        2005	Mar	0.00	607.89	
+2723719224	        2005	Mar	0.00	246.49	
+2723719226	        2005	Mar	0.00	246.49	
+2723719231	        2005	Mar	0.00	246.49	
+2723719233	        2005	Mar	0.00	335.96	
+2723719242	        2005	Mar	0.00	1,215.44	
+2723719251	        2005	Mar	0.00	246.49	
+2723728191	        2005	Mar	0.00	335.96	
+2723728230	        2005	Mar	0.00	246.49

She wants to put it in no particular order. Neither Ascending nor Descending. According to her she recieves her invoices in a particular order and she wants to have the records in that order.

My question is, is it possible to do this? I have tried and tried, but everytime I rearrange the records and close the table, it just reverts to Ascending or Descending order again.

Thanx for all the help

Kind Regards

 

[neileg]

Basically, a table is an unordered dataset. There is no relevance to the position of a record in the table. Therefore, you have to have something in your data that imposes the order your user wants. This might be a date/time field or an autonumber. If you sort on this field it will consistently give you the same order.

 

[Poppy]

Basically, a table is an unordered dataset. There is no relevance to the position of a record in the table. Therefore, you have to have something in your data that imposes the order your user wants. This might be a date/time field or an autonumber. If you sort on this field it will consistently give you the same order.

Then why does my table keep reverting? I don't get it. It's starting to give me a headache.

I'm working with linked tables here with an index.


Kind Regards

 

[Uncle Gizmo]

If your table doesn't have one already, add an auto number field, and sort by this field. This will give you the sort order that the records are entered in, if the records are entered individually by a user.

 

[Mile-O]

Don't use your table either - make a query based on it and choose your sort field her.

 

[boblarson]

GIZMO! I'm disappointed in you. You should remember that an autonumber does not guarantee a consecutive number. It only guarantees a unique number. You DO NOT want to put a sort order on an autonumber. Eventually you will pay for doing so.