Need Help And Tips About Problems That You Encounter When Working With Your Database - Find Them Here.

February 16th, 2010 | Uncategorized

If you’re new here, you may want to subscribe to my RSS feed. Thanks for visiting!

When we use Microsoft Access or Microsoft Excel, it is easy and common to sort text or number columns. There are special buttons in both programs that allow us to sort in ascending or descending way. The button for ascending sorting has a blue A above the red Z and an arrow pointing down. And on the button for descending sorting we see a red Z above the blue A with the same arrow. All you have to do is to put the cursor in the column you want to be sorted and click on the needed button.

This function is great for text and number columns. But, when you need to sort the mixed types of columns with both text and numbers, the challenges start. The simplest examples would be something like this, keeping in mind that we want to achieve the ascending sort. The first example is the part numbers such as W4, W39 and W212. And the second example, the addresses, 4 Main Street, 39 Main street and 212 Main street. Which would come first on the computer?

We would normally sort them differently from the way computer does it. People look at the whole string of characters, whereas Access or Excel would sort them on a different logic, because to a computer, 4 comes after 2, that is why W212 would be before W4.

In order to solve the Part Numbers problem, try to number the parts in a way, so that all numbers had the same amount of characters, for example W000004, W000039, etc. In this case the program would sort it the way we expect. If it is impossible to change the pattern, you should create a new Access field or Excel column and name it PartNumberSortable. Then create a query in Access that will convert all the numbers to a sortable type. A logic like left([PartNumber],1)$ format(mid([PartNumber], 2),”000000”) can be placed under the new field in the query. Your traditional part numbers will continue to list in all reports and queries but they will be sorted by this new field, which will stay hidden. It is also important to modify all your data entry forms, for them to automatically generate the new field.

As for those, who work with Excel, you can also create a new formula for the column. Something like this: =Left(A1,1)&Right(“000000”&Mid(A4,2,8),6). It is assumed in this formula that the original number is in column A.

It is just one of the problems that you can encounter working with your database. Access is a very complex database, so it will always bring you pleasant or unpleasant surprises. If you have an address problem, or something bigger, you should know that there is always a way to solve it.

Anything about microsoft access database: great training materials, all sorts of business products (that can be tailored for the specifics of your business), special microsoft access database blog with tips and advice about MA databases.

All this and more from a trustworthy UK based company which majors at microsoft access database and is helping clients all over the world.

Today we live in the world where knowledge makes life easier.

Due to this if you are properly armed with the knowledge in your sphere of interest you can be sure that you will always find the solution to any bad situation. So, please make sure to visit this site on a regular basis or - an ideal solution for you - sign up to its RSS feed. In such an easy way you will have a direct shortcut to the freshest info updates here. Blogs can be helpful, you just need to know how to use them.