Search Alphabetically

[virgosam20]

I'm currently developping a database and in the process of re designing my queries for my users needs. one of these is to be able to search entries of the database determinable by their first letter (e.g. find all customers whose name begins with 'A'). Can anyone help me with how to do this. So far I have managed to create a 'Like' query, so the searches are not case sensitive, but i still need to make it search alphabetically.

Any help, much appreciated

Sam

 

[David Eagar]

Use the Sort Ascending in your query for the Customer field

 

[virgosam20]

sort by first letter?

ok, thanks, yh that worked, on the most part, but not for all entries as when i typed in 'd' for instance, top of the list of results was 'AGD'. Is there any way of sorting by the first letter of, say, the customer name?

 

[boblarson]

The query underlying the form should have this as the criteria and be sorted by the customer name:

Like [Forms]![YourFormNameHere]![YourTextBoxNameHere] & "*"

That will get you all items that START with the entered data

You will need to use

Me.Requery

in one of these events:

Use the text box's After Update event if you want to enter the letter(s) and then either tab out of it or hit enter.

If you want it to change with each letter typed you will need to put Me.Requery in the On Change event of the text box (this can be slow in responding if you have a lot of data that has to be queried, so be wary in using the On Change event).

 

[David Eagar]

Or, create a new field in your query and make it equal (left[Customer],1)
That will give you the first letter only and sort ascending on that

 

[virgosam20]

Thanks. I'm trying both these ways out now.
One thing, Dave, what do you mean by adding another field and making it equal? where abouts do i put (left[Customer],1) in the Query Design? do i insert a blank column and put it in the field cell? or the criteria or sort cell? or do it put under another of my field names?

 

[David Eagar]

In your query, go to the next free column and enter your new filed name (eg Cust and then add : after so it will be Cust:
Highlight and selct the Build icon and then enter
=(left[Customer],1)

 

[virgosam20]

Ok, dave, tried this, but when i try to put in my field name + : in the next available field it come up with a warning box which says: 'the expression you have entered contains invalid syntax'.

Your help is much appreciated by the way.

 

[David Eagar]

Let me retract that statement - full expression should be
Cust: = left ([Customer],1)
maybe I should test my suggestions before shooting my mouth off!

 

[boblarson]

There's no real reason to create a new field to sort on as you already have the customer field to sort on. In fact, creating a new field with a single letter will NOT guarantee that the sort will actually be sorted correctly. Because, if you look at this example, you will see where sorting by a single letter will fail you:

Data in the table

ID CustomerName
1 Boots R Us
2 Bill's Ice
3 Bad News Latte

If you sort by the customer name it will come out like:

ID CustomerName
3 Bad News Latte
2 Bill's Ice
1 Boots R Us

which is the correct sort order. But, if you sort by ONLY the first letter, you will get:

ID CustomerName
1 Boots R Us
2 Bill's Ice
3 Bad News Latte

Which is NOT in true alphabetical order. The additional letters are just as important in the sort order.

 

[boblarson]

ok, thanks, yh that worked, on the most part, but not for all entries as when i typed in 'd' for instance, top of the list of results was 'AGD'. Is there any way of sorting by the first letter of, say, the customer name?

Your problem with this one would be that you must have used

Like "*" & [Forms]![YourFormName]![YourTextBoxNameHere] & "*"

Which will return results with whatever string is in the field. You should use my first post

Like [Forms]![YourFormName]![YourTextBoxNameHere] & "*"

To pull all entries that START with the entered string, not if it is somewhere in the string.

 

[virgosam20]

Thanks you both very much. both ways have helped my development of database and my understanding.

 

[virgosam20]

I have just returned to look at this query, and it seems that it will only work when the letter 'd' is typed in. What i mean, is that it only finds results that begin with 'd' and no others. This seems very strange.
This is what is in the 'Criteria' box in my query under Bodyshop Name:
Like [Forms]![Bodyshop Contacts (Add/Delete)]![Bodyshop Name] & "*"

 

[boblarson]

Well, for one - rename your objects to remove the () and the / signs. That is not good to use special characters in names of fields or objects.