David40

Thanks, I'll give that a try as soon as I can figure out where to find them.

Yes, I have created that field already, I call it Formatted_Address. It's all in my layout but I still don't know how to make it go. What is going to make it actually perform the calculation on all the existing records?

Thanks, I'll have to study that thread. Update I read the other thread and using the Trimall function I believe the formula with my fields would look something like this: TrimAll ( House_Number & " " & House_Number_Suffix & " " & Street_Direction & " " & Street_Name & " "& Street_Type & " " & Street_Direction_Suffix & " " & Unit_Type & " " & Apartment_Number ; 1 ; 1 ) Here is the big question. Now that I have a formula I believe will do the job, how do I make it go? I have a database with thousands of records and I basically need to apply this to the data one time so the formatted address field shows the formatted address for every record. What would be the simplest way to do this? Thanks.

Thanks for responding. My post got scrambled in the translation. I had separated the field descriptor from the sample data with a bunch of spaces since the Tab function does not work on the text here. When I posted my topic the system removed all my spaces and placed a comma in some while others it did not. The descriptors do remain the same. I just used this as an example of what I want to do I have not attempted it yet because I no nothing about joining a bunch of fields together as one with the spacing done correctly. The end result needs to be just one line as a house address without the state, city, or zip.

Would appreciate some help in the form of an example or a referral to a tutorial or something of that sort. I need to create a formatted address field from a number of elements. The elements with sample data are as follows: House_Number, 1234 House_Number_Suffix, 1/2 Street_Direction, East Street_Name, Data Street_Type Avenue Street_Direction_Suffix, West Unit_Type, Bldg. A Apartment_Number 206 An example using all the elements combined in a single formatted field would be: 1234-1/2 East Data Avenue West Bldg A Apt.206 Thanks for any direction on how to do this. David

Guess my problem is I don't know how to use it. With my limited experience with this I only know how to use a calculation field to return a result, it doesn't sort anything until I use the results in the field as part of a sort. As far as I know I can specify a calculation field as part of a sort but only if the field contains something that can be sorted either ascending or descending. I am not aware of a way to do a sort formula, now that would be the ticket. As far as I can see my sort options are limited to ascending, descending, and summary field. If you can get it to work then I am missing some basic procedure for implementing it. I'll try to mess with it later. I need to get busy at my real job where I actually know what the hell I'm doing. Later ************** Got back home early. Wow and Hallelujah!! That sample you sent is fantastic. A picture is worth a thousand words. That's exactly what I was trying to do. All I need to do is set up those extra fields and plug my stuff into it. You be da Superman. Thanks a million. David

Doesn't work for me. When I enter your formula for the calculation field and execute it, it only effects the results in the calculation field, which has no ascending or descending value it's going to be a one or a zero no matter what. It has no effect on sorting the house number field other than which are even and which are odd. So basically I end up with the order I described in my second example. I appreciate your trying to help me out, and I can't thank you enough, but I think I'm going to try to figure out how to do this by using two calculation fields and using them in the sort as I described.

Vise versa just means if I wanted to switch it around to say start at the other end of the street. To further clarify. Using the example of 25 houses numbered 1 to 25 and for this example Street #1. My printed list should look like this: 1 - 1st Street 3 - 1st Street 5 - 1st street (and so on up to) 25 - 1st Street then down 24 - 1st Street 22 - 1st Street 20 - 1st Street ( and so forth down to) 4 - 1st Street 2 - 1st Street Then repeat the same thing for 2nd Street and 3rd Street etc. The list I created with the first formula you gave me groups the houses by even and odd but they are both ascending (or descending if I reverse it)and looks like this: 1 - 1st Street 3 - 1st Street 5 - 1st Street (and so on to the end) 25 - 1st Street ( then it does the even houses) 2 - 1st Street 4 - 1st Street ( and so on up to the end) 24 - 1st street I suppose the walker could just skip to the last house on the sheet and work backwards but I'd prefer to have it in the correct order to prevent confusion.

Sure, be glad to. One side of the street has all even numbered houses and the other side has all odd numbers. So in order to print a walk list to go up and down the street in order house by house, with out having to cross the street and zig zag you need a list of all the houses on that street; even numbers ascending and odd descending or vise versa. It's like a postal carrier route. Your mail man doesn't zig zag up the street delivering mail, he would walk up one side and then back down the other. Less walking that way.

Yes, same order for all streets. It would need to be two methods: Start at #1 (odd house numbers) Ascending and Start at #24(even house numbers)Descending These would be placed after first Sorting the Street Name Ascending, and lastly sorting by House Number For now the street order will be alphanumeric in an ascending order until I can create a value table for the named streets.

That's probably true but I have not been able to figure out how to apply it in a way that gives me a list I can use. Using your example I should end up with a list that starts with Street #1 ascending starting with house #1 ascending, then #24 descending, then repeat the same thing for street #2 House #1 ascending, then #24 descending, and so on. It needs to represent walking up one side of the street then walking back down the other side, moving over to the next street and going up and back again, and so on. I did a bit of research on this and the USPS and UPS among others call it a sequential deliver list or something to that effect. Of course I'm sure they use a formula that's much more complex as it takes the cross street into account as well. That I would not even attempt. If I could get my calculated field results into two separate fields I think I can get it to do what I need.

The first formula got me almost there and I got the second formula to function but it does not return the results I need. It gives me a list that is sorted with all the even addresses and then all the odd addresses. Not what I had in mind for a walk list. I think I figured out a way to do what I need but I don't know how to write the formula for it. I hope I can explain this correctly. I figure that if I create 2 calculated fields (c_even and c_odd) instead of one I can have one field set a 0 for every even house number and in the other field a 1 for every odd house number. That way when I do my multi field sort I can do the even addresses ascending and the odd addresses descending(or vise versa) The sort would look like this: Sort Street_Name (Acsending) Sort c_even (Ascending) Sort c_odd (Descending) Sort House_Number (Ascending) Is there a way to use the MOD function set a result of 0 for an even address (or a 1 for the c_odd field) and then leave the field blank if the number is odd, or would that be a completely different function? Any comments welcome. Thanks, David40

I know, I see two of each as well, that's why I was surprised to get the error message. Maybe I have an extra space in there or something. Must be I have the syntax wrong somewhere. I cut it from your post and pasted it in the formula box then changed the field name to what I am using.

Comment, I tried that little formula: Case ( Mod ( HouseNumber ; 2 ) ; -HouseNumber ; HouseNumber ) and it would not work. I get an error message that there are too many "(" or not enough ")".... Looks like it won't accept a nested formula. Is there any other way to do that?

Yes, alphanumeric, the numbered streets 1, 2, 3, 4... for the maned streets the first letter of each name C, D, E.... I think I understand. I can assign a numeric value to each street regardless of it's actual name in a table. "0" could represent Central. Then 1st and 2nd, then Dartmouth could be "2.5, then 3rd and 4th etc. Thanks to all of you. Great bunch of users here. :(

OK. I didn't think it was related close enough to be considered the same topic. Anyway, in reply: Alphanumeric ascending or descending. For example the physical streets are laid out Central, 1st, 2nd, Dartmouth, 3rd, 4th, 5th, Emerson, 6th, etc. That way walkers could go street to street up and back down each one. They could just flip through the sheets to find the street they are on but I wanted to make it as easy and foolproof as possible. Be nice if all they had to do was follow the list and concentrate on their work instead of figuring out where they are going.

Yes, I generally break them down by precinct. Then whomever is doing the canvassing can work alone or divide the sheets among helpers.

I am working with street addresses to create efficient walk sheets for door to door canvassers. A problem I am having is to sort the street names in order because we have numbered streets with named streets in between. For example Emerson Ave is between 5th and 6th Ave. So when I sort the street names all the numbered streets are first and then all the named streets. (The named streets are Alphabetic.) What makes it more difficult is that there are only named streets between some of the numbered streets, not all. I would be grateful for any advice on how to get these to sort in the proper order. Thanks, David40

Got it, Thanks so much. Now as an added touch I'm going to try figure out how to sort it further by sorting the odd numbers ascending and the even numbers descending (or vise versa). That would make it darn near perfect.

Yes I did, I'm just not sure how it works. I don't know what "Mod" means or the significants of the punctuation. I'll play with it and the formatting, and see what happens. I'm considered an expert at several things but math is definitely NOT one of them. Thanks much.

Totally broken up by street name/number, street name suffix (Ave, St, Blvd), house number, etc. It was easy to get them sorted by the street name and house number but sorting it further by even and odd house numbers is beyond my expertise. It's the database of all the voters in the county I live in. Just under 700,000 records. I am doing walk lists for political canvassers. I sort by precinct, party affiliation, then street name, and house number. Adding a sort on even and odd house numbers on top of that would be sweet.

Looks like you've done that before. I've never had to set up a calculation field before so what you're saying is Greek to me, but hopefully it will make sense when I get in there and actually see the options. Thank You.

Not sure this is the right place to post this because I'm not sure what method to use to do what I want to do. I have a database of street addresses and I want to create a walk list. This would mean sorting the addresses ascending/descending based upon even and odd house numbers, so that the walkers can have a sheet with the addresses they need to visit. The addresses need to be in the proper order to walk up one side of the street and then back down the other side. I've gotten as far as sorting the addresses by street and house number but I don't have a clue about how to isolate/sort even and odd house numbers. I would really appreciate any advice. Thanks, David40