Sorting House Numbers in Acess

[leftpeg]

Probably an easy answer to yo guys and i probably oculd of found it using a search, but i hope this will be quicker.

I have house numbers like below:

11 13 15 17 19 23 52 54 56 68 70 86 90

29A 82B

3 15 16

1 9 20 22 23 24

When i put a number sort in access, it says invalid entried and deletes the ones with letters or symbols in. If i then change it to text sort, it sorts my numbers in 1, 11, 13, 2, 21, 27, 3, 33, etc. which is obviously no good. How can i sort them so they sort as shown above?

Thanks in advance,

Mark

 

[DCrake]

Simple Software Solutions

Numbers are number and Text is text. So what you need to do is to create a new query and add the following columns

Col1
HouseNo:IFF(IsNumeric(Right(fldHouseNo,1),Format(fldHouseNo,"0000"),format(Left(fldHouseNo,Len(fldHouseNo)-1),"0000")

Place a sort Ascending on this column

Col2
fldHouseNo

Explanation:

An assumption is made that a house number will have only 1 character as a house number suffix: a, b, c, etc.

So if it does strip it from the actual house number and preceed the house number with leading zeros. Thus

1a = 0001
11 = 0011
12 = 0012
491b = 0491

etc

Now do a sort on only the number part of the field. The leading zeros will enforce correct ascending order.

Do not show this field in the query


Test the query in datasheet mode and you will see that the house numbers will appear as expected.

CodeMaster:

 

[Brianwarnock]

Sheer laziness is no excuse for not doing a search, it took just a couple of minutes to come up with several threads including this,

Brian

 

[leftpeg]

Thanks DCrake for your answer.

Apologies, but i am completely new to Access. When i type in the code below, the column comes up with an error. I havr replaced fldHouseNo with HouseNo - which is the name of the column with the numbers i require sorting.

Where am i going wrong.

In answer to the laziness post, i am not being lazy. I have several others things oging whilst trying to prepare this database i.e. running a business and organising personel. I thought by asking the question on here it owuld be quicker than trawling through previous posts. Apologies

 

[leftpeg]

it is saying it is missing a closing parentthesis

 

[leftpeg]

Brianwarnocks post is perhaps correct.

Using his link i found the code i needed and put into my query and then sorted it by that. It worked perfectly.

Thanks Brian

 

[DCrake]

Simple Software Solutions

HouseNo:IFF(IsNumeric(Right(fldHouseNo,1),Format(f ldHouseNo,"0000"),format(Left(fldHouseNo,Len(fldHo useNo)-1),"0000")

HouseNo was an alias name I gave to the column, if this was the actual name of the field in the table then give it a different name than the one I used.

IsNumeric(Right(YourFieldName,1)

This is checking to see if the house number is a pure number and not a number with a character stuck in the end. such as 21b


True Element

Format(f YourFieldName,"0000")

This is altering the format of the house number to change it from a number without any leading zeros to a number with leading zeros


False Element

Format(Left(YourFieldName,Len(YourFieldName)-1),"0000")

This is stripping of the last character from the house number (Was 21b now 21) and reformatting it to have leading zeros

As exlpained earlier you house number field should not contain any alpha type character 0 only 0-9 characters

Col1:
0001
0011
0012
0021
0023
0029
etc.

Sort Ascending

If you still can't figure it out - and try to first - there is a simple example attached to this post.

CodeMaster:

 

[leftpeg]

Thanks for your detailed explanation.

You guys really are a great help to us newbies of Access.

I have one more question.

I have a combo box in my table where i select an engineer which work has been allocated to. I then have second box which says who the work was completed by.

Is there anyway i can make the second box only have the one name in it i.e. the name of the engineer who the job has been allocated to?