Using Split() with MultiValue Fields (1 Viewer)

Hi there,

I am not sure if what I am attempting to do is even possible. Let me also preface this with: using a multi value field - I know, I know. When I started this DB it was a fabulous idea, now that the functionality has grown so much it makes it difficult.

My field tblProposals.ServiceAddress is a multi-value field and throughout forms, reports and exporting information into various templates. I would like to be able to build a string based on the fields value but I want it to not duplicate the street name. I was thinking that the split function maybe good for this although I am just not confident it would be the best way.

Currently ServiceAddress.value = 444 Monroe Street, 446 Monroe Street, 6250 Wright Ave
I would like to build a string so the result is = 444 & 446 Monroe Street, 6250 Wright Ave

Any Ideas?

multivalue fields are actually a form of link table, not an array which you would use split for.

however this

ServiceAddress.value = 444 Monroe Street, 446 Monroe Street, 6250 Wright Ave

implies the whole address is one field from another table, so you should be trying to do your string manipulation in that table.

If this is the case then you would need to use a vba function. But what that function would look like is anyone's guess since it would presumable need to cope with a whole variety of potential address structures - split may or may not be relevant

On a basic level you would
1. split the address
2. remove the numbers (use the val function) from the first two elements
3. compare the remaining text of the first two elements
4. if the same, rebuild your string by concatenating the elements but replace the text in the first element with an ampersand
5. if not the same, leave the address string alone.

but this is fraught with issues - for example what if there are typo's in the first two elements, or there are missing commas in the address

Hi CJ, thank you for your response.

CJ_London said:

ServiceAddress.value = 444 Monroe Street, 446 Monroe Street, 6250 Wright Ave

implies the whole address is one field from another table, so you should be trying to do your string manipulation in that table.

Click to expand...

ServiceAddress is a multi select field and it combines the addresses with comma separators by itself. Is there any other way to manipulate it without split? I am not so concerned with typos causing an issue sometimes it does happen and I have to go back and correct information. Worst case it wont combine properly. This is an application I use all day, chances are I will catch it.

I was thinking I could use split with commas as the deliminator but is there anyway for me to ignore directionals that are part of the address ie.(1990 30th St, NW)? I guess worst case I could remove all of the commas between the street and directional and prevent using them on input.

With this address example it would also cause issues with using val() Sometimes we also have letters as a part of the house number. In my mind I would almost need to use split again using the space as a deliminator?

What function would I use to compare the characters?

This may be too complicated for me ; I do like a challenge though and I manually do this for every project we complete. Thanks for your help!

you probably don't want to hear this, but I wouldn't use MVF's. It's not hard at all to manage these by normal means, and then at least you know exactly how your data is built.

Yes, that is why I addressed it first "using a multi value field - I know, I know". It would be a mess to go back now. :banghead:

what you are seeing is just a presentational view of the data which is in separate records - and I would expect those separate records will be in a table somewhere.

Because multivalue fields hide some of the internal workings of how the data is linked I think you will have a major problem in trying to resolve it. (I don't have a clue how you would do what you want to do). As Gemma suggests, it would be much better to spend the time disentangling your multi value field rather than trying to develop a workaround.

A normal address would either be presented as a single field in one record, or a number of fields in one record (line1, line2, town, county, postcode etc). With your multivalue construct you have each line in a separate record.

you will need to use something like the concatrelated function developed by Allen Browne here http://allenbrowne.com/func-concat.html which will work with multivalue fields. Use it to construct the address as a single string which you can save in the same table as your multivalue field (just create a new text field and use an update query).

You can either leave it at that, or then deconstruct the string (using the split function) to populate a number of fields as suggested above.

once you have done that, you can then use the process I outlined in my original post to merge the first two parts of the address.

I think in general terms though, what you are trying to do is probably quite difficult.
You need to specify exactly what you want to do.

Say you have 3 addresses in High Street. 50, 51, and 83 High Street. How do you know that the first 2 need grouping together, and not the third. Or that they are all distinct addresses. Are you checking every part of the address to make sure that everything else is identical. If so, typos in parts of the address will give no match, when there should be a match.

Really I think you might be better having a manual address-grouping table or field to indicate that certain addresses can be handled together.

There are 2 aspects in this thread.
(1) Multi value fields. Microsoft dumbs down the user interface of Access and makes it harder for developers. However, you can get to the individual values by opening a recordset based on the field value. Using the OP's thread
set rs = db.openrecordset("SELECT tblProposals.ServiceAddress from tblProposals where ProposalID=x")
set childRS= rs.ServiceAddress.value

The various multi values will be contained in the records of childRS

(2) the issue cited by Dave. It's easy enough with
444 Monroe Street, 446 Monroe Street, 6250 Wright Ave
but what about
444 Monroe Street, 446 Monroe St, 6250 Wright Ave , 67 wright avenue
not to mention suburb, city

Thank you all for your valuable input. After serious consideration I realize as my DB grows in functionality I will be hit with this limitation of the multi-value field. For now I will mark this as solved. Maybe this winter I will have more time to detangle this web and redo the address field.

Final question - With all this in consideration to achieve what I want in building my addresses I should treat building number and street name as two separate fields? this way it eliminates the various inconsistencies brought up?

I should treat building number and street name as two separate fields? this way it eliminates the various inconsistencies brought up?

Click to expand...

really depends on what you are trying to do and where the data comes from. What about apartments in buildings for example. House names rather than numbers?

You can take normalisation too far. I would be inclined to standardise on postcode (UK anyway) which means you know county and town from other lookups, but leave the rest as 'free type'

I would be inclined to standardise on postcode (UK anyway) which means you know county and town from other lookups, but leave the rest as 'free type'

Click to expand...

This is how it is now really - USA so a little different. So, this is used in a commercial construction company and we would look at the entire building so I don't have to be concerned about apartment numbers.

tblServiceAddresses is the table that my addresses are stored in with ServiceAddress as the building number and street name with direction, (this is a free type with unique values). My city field is a look up in that table that brings in the State. One city can have several zip codes so that is a independent field in tblServiceAddresses.
*Now there are instances where information is duplicated like others have suggested would be a problem. I have tried preventing this by making users search for the address they need prior to adding and it is a unique field. Although, I still look through the entries and find errors and eliminate. I think there is no way to really fool proof this unless I was to take a step further and make BuildingNumber, StreetName, StreetSuffix, StreetDirection all separate fields. Which seems like overkill and a tremendous feat. They would still find a way to duplicate information, Garbage in garbage out right!

tblProposals is where this multi-value field is located. I suppose this is really considered a junction table. Forgive me if my vocabulary is off. This table has JobNumber as my PK then pulls in other lookup fields. ie Company, ContactName, JobStatus and ServiceAddress as a multivalue lookup. So in order to eliminate the multivalue field I need another "junction table" which would allow me to select addresses relevant to the JobNumber then pull it back into tblProposals.

Am I atleast on the right track? Sorry some of this I am just thinking outloud, I really need to do some research on recreating the multi value on my own and pulling it all together.

You could try to write a public function that builds the string.
This is a bit simplistic, no error or format checking.

Query...

Standard module...