Sort by even or odd number

[sammer021486]

I need to take a civic address table and sort the addresses into even and odd for a client, to help make finding an address easier.

The client does not want the table to be sequential even and odd, but separated into sequential odd and sequential even. Also some addresses may have an alpha character too (eg 1A, 1B, 1C) for apartment buildings or houses accessed by the same driveway.

Right now the table is

1 Second Ave
2 Second Ave
... ...

The client would like it to be

Odd Addresses
1 Second Ave
3 Second Ave
... ...

Even Adresses
2 Second Ave


4 Second Ave

[RuralGuy]

You can probably do it with a UNION query of two other queries that have a calculated field of =[AddressField] Mod 2.

[sammer021486]

You can probably do it with a UNION query of two other queries that have a calculated field of =[AddressField] Mod 2.

Thank you for the assistance. The =[addressfield] MOD 2 was exactly what I needed, that and changing the field type from text to numeric.

to find my odd numbers

SELECT hp_add.CivicNumber
FROM hp_add
WHERE ((([civicnumber] Mod 2=1)));


to find my even numbers

SELECT hp_add.CivicNumber
FROM hp_add
WHERE ((([civicnumber] Mod 2=0)));


That will work as long as there is no alpha character in the address, but if there is an alpha character, the field will have to be a text field and the MOD function will not work then.

Is there a solution for this?

I lucked out and do not have any addresses that have alpha characters, but there may be a time that I do.

[RuralGuy]

If you change the field to a string then try:
Val([civicnumber]) Mod 2=0
This will work if the alpha characters are trailing(at the end of the string). See Val() in VBA help.

[sammer021486]

If you change the field to a string then try:
Val([civicnumber]) Mod 2=0
This will work if the alpha characters are trailing(at the end of the string). See Val() in VBA help.

So if I use val() in my Query, it should return all the numeric characters and not pay attention to the alpha characters. Then I can just use the sort function to sort the addresses into alphanumerical order, or in my case numericalpha order.

I am going to be a pain now. The val () stops at the first non numeric character, what should I use if the address has been entered as A10 or 10 - A, instead of 10A?

I could always just go through the list and change it to 10A but, if they require the list to remain in the format or there are too many to change what coding chould I use then?

Thank you for your assistance

[RuralGuy]

Cleaning data can take a lifetime. I would just give it a try and see what problems you have. Then deal with the problems as they come up. It is amazing how many ways users can stray from a standard input.

[sammer021486]

It is amazing how many ways users can stray from a standard input.

Yes, especially when there is multiple users entering data as well. If there is just one user, they normally follow a pattern.

Another post on theses forums, I had to create a street address table and limit the user to only choosing the address that was in that table. This was to ensure that no matter who used the database, they could only enter the street address that I provided for them in the drop down box on the form. Because it is an internal database, the user does have the option to go into the streets table and add or change the way that the address is entered. But for the mean time I will be the one doing the modifications on the database, which is for a GIS database.

thank you again for your help

[RuralGuy]

Try and do as much validation in the input form. Separate fields is a big help because you can always put them back together. In the apartment (flat) number case you described you could use two unbound controls than have different validation, one numeric and one alpha and simply put them together in the AfterUpdate event of the two controls to an invisible control that is bount to the field.