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 (customized for your needs), special microsoft access database blog with tips and advice about MA databases.

All this and more from a reputable UK based company which majors at microsoft access database and is assisting clients round the globe.

There are many objects contained in Microsoft Access including forms, tables, reports, queries, etc. All these objects help you store and work with data. And while the Access database is very complex, some malfunction may occur. So, when you try to access any of the data, you can encounter an error message that happened due to access to the same component by multiple users at the same time, virus infection or database corruption. After this error message you can no longer access the data in the component you needed. Usually, you would be saved by the backup, but if it is not available, you need to use powerful software of Access recovery.

Let’s consider this example, when you try to open a financial report in Access database, used by multiple users and which is saved in the network. One of the following errors can be encountered: “There is an error in executing the command” or “Not able to carry out the command”.

You discover that the financial report, you needed to open, is inaccessible to all network users after the error message occurred. The main reason for this problem is that more than one user tried to change the same record in the report, and the financial report became damaged. In order to fix the corrupted report, you need to use the special Access utility “Compact and Repair”. This tool, inbuilt in the application, allows the user to repair the damaged Access database. But, in case the Compact and Repair tool wasn’t able to recover your financial report, then you will need to use advanced and powerful Access repair software. This program uses powerful scanning techniques to restore and repair Microsoft Access data.

If you encounter problems and errors in your database often, you need a powerful and effective Access repair program. These types of software usually have user-friendly interface and typically provide complete Access database restoration in practically all data loss cases. These Access recovery utilities have a lot of benefits, because they usually support all Microsoft Access database editions from 2000 to 2007 edition. These recovery tools can repair and restore all Access database objects like tables, forms, queries, reports, macros and modules. It also is able to recover Yes/No fields, all types of queries, Auto Number, hyperlink as well as Object Linking and Embedding.

There are many Access recovery utilities out there and you should pick the one, which would the best solution for you when you are faced with database corruption and data loss situation. You will be able to scan for damaged files and restore them to a default folder or another specified location. You will be able to restore the whole database or separate corrupted objects.

The world of computers and computer applications grows all the time. But you can turn this problem into your strong point by catching up with this knowledge and becoming an expert, for example in microsoft access database sphere.

Please visit this web site that is dedicated to microsoft access database topic and has a great line of microsoft access database products available.