Sorting numerically WAS working; not any more.

[Pam Maples]

Access has all of a sudden forgotten how to put numbers in numerical order! I used to be able to sort the SiteID field in my tables numerically by clicking on the column header and clicking the A - Z button in the access toolbar. Not any more.

Every entry in the field looks like this: NAV001, NAV002, NAV003, etc. It used to sort just fine regardless of the letter number combo. For some reason, it has a problem with that now. The reason could be that I put an input mask for this field on the tables and on the forms: ?NAV"&&&

Everything worked until I put the input masks. Now, access puts in the NAV part and the user (me at this point) puts in the number part, and it will not sort in the tables anymore.

I tried copying the data from a table into a blank table. That worked and it was able to sort UNTIL I USED A DATA ENTRY FORM TO ADD DATA TO THE TABLE. After that, it was not able to sort again.

So I tried calling the Val function by putting the following code into an advanced filter/sort property for that field in the table with no luck;

Code: IIf([txtSiteID] Is Null, 0, Val([txtSiteID]))

IT WON?T STAY THERE. When I close the property dialogue box it goes away.

It is the value function that makes access overlook non-numeric values, right? Is there somewhere else to put the code? What else could I try?

Oh yes, since the tables will not sort, the form datasheet views and reports will not sort either. I did try a query and if I query the database on any one table, it will sort the data by the ID field just fine. But I don?t need a query, I NEED IT TO SORT IN THE TABLE.


Thanks, Pam


EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[S.Clark]

With an Input Mask, you can opt to store, or not store, the literal
characters. So, if you just want to show the 'NAV', but not store it, try
changing the setting, and see if this helps your issue.

See the Input Mask in the Help file for parameter options.

 

[Gina Whipp]

Pam,

You say you NEED to sort this data in the table. Note, tables are like
buckets (that hold water), they just hold the water. They don't
differetiate between *old* water and *new* water, water is just water. Now
replace water with data and you start to get the picture. On forms, queries
are used for sorting, it is there you can use the Val() function to get what
you want. Set up your query and use that as the RecordSource form your
form.

Now that I got that out the way AND you still want to use the table... Try
applying the input mask to the form and not to the table and see if that
helps your cause.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[KARL DEWEY]

You did not say what the order of the that is being produced.
If you have old data with the letters 'NAV' storing 'NAV001' and not store
'001' the only numerical characters will sort out first.

You could update to add the 'NAV' to each record that does not have it,
remove all, or in your query test for leading 'NAV' and concatenate for
sorting.