Separate out information that has been typed into one field?

[slenish]

I imported some data from an excel spreadsheet to a table in Access. There is one field where they have two pieces of information typed into one column that i want broken in to two columns. Is it possible to write a code or validation rule to automatically seperate the information with out having to go back and do it by hand?

for example;

Column 1
[Name 1 Frank] (info)

I want to break this in to two columns

[Colm 1]......[Colm 2]
[Name 1]....[Frank]

Just wondering if it can be done because going back and entering in a thousand of these by hand is really time consuming.

[TheSmileyCoder]

Would depend on the date. You can do alot of seperating if you can find a valid criteria for the whole dataset. Try to supply us with some real test data, and we can look at it.

The easiest is if there is any delimiter used between the 2 data types, then you can use Split.

[NeoPa]

As Smiley says, you need to have a clear understanding of what criteria to use to determine where the split should occur. Is it on a number of characters? Is it on a separator character?

Your example data wouldn't be obvious as the spaces between your fields are not the only spaces. It would split it [Colm],[1],[Colm],[2], etc.

[slenish]

I do have a clearn understanding of where to split. First part starts with Box and then a number like (Box 1) then after that is a name like Jeff.

The word Box always stays the same but the number next to box changes and goes up by one each time. Also the name next to box could be the same for so many records and then change. so there is a good dividing point.

There are no dates next to the records only thing I can see that stays constant is an account number. So maybe I could make it pull account number 5 column Shipper then split box 1 from name Jeff....

What do you think??

[NeoPa]

If that's your clear understanding then we have a problem. It doesn't match your example data. For us then, it is far from clear. I would hesitate to proceed on this basis.

Your third paragraph makes no sense to me at all. In the context of the example data provided I see nothing that remotely matches what you say.

This may simply be the example data is poorly chosen, but to proceed it is important that what you say matches the data so that we can get a clear understanding (if we're to be of any reliable help).

[topher23]

This is a classic exercise in string manipulation. Since you're splitting a string in half, you're going to need to use at least 3 string functions: Left, Right and InStrRev (or InStr). Look up the functions in Access help to get an idea of how they work. You'll end up with two statements: one that gives you 'Box 1' and one that gives you 'Jeff'

If "Box 1 Jeff" as in your example is the ONLY thing in this field, your expressions will end up looking something like

Left([FieldName],InStrRev([FieldName]," ")-1)
Right([FieldName],InStrRev([FieldName]," ")-2)

where [FieldName] is your actual field. In the case of "Box 1 Jeff", the first expression produces "Box 1" and the second expression produces "Jeff"

Hope this is helpful!

[slenish]

Nice example topher23.

The only question I have now is where would I put that code? Im not using a form im just directly taking a downloaded excel file and importing it in to a table so would i put that as a validation rule??


NeoPa,
My appologies on the third paragraph Im not sure what i started writing there. I should have just left the third para out. Didnt really need it.

I have another problem that im trying to deal with that is more urgent than this. I am trying to create an if statement for a number box on my form. What I want to do is make it so you have to enter in at least 5 numbers in to the box no more no less. I have it half working where if you enter in more than 5 you get an error message but less than 5 does nothing. Here is what i have so far.

Dim number, zipCode As Integer
Dim error As String

#Const number = 0
zipCode = 5

If number < zipCode 
error = " Not enough numbers. \n"

ElseIf number > zipCode Then
error = " To many numbers in field. \n"
End If
'End If

[NeoPa]

Since you're splitting a string in half, you're going to need to use at least 3 string functions: Left, Right and InStrRev (or InStr).

In fact Smiley has already suggested a solution that uses only the one function (Split()) which is perfectly viable.

What we are simply waiting for now is a clear and consistent definition of what determines the split. None of these solutions works well if the count of the characters is what's used to split the data.

[NeoPa]

NeoPa,
My appologies on the third paragraph Im not sure what i started writing there. I should have just left the third para out. Didnt really need it.

That's not a problem Slenish. However, the situation is still quite unclear due to the example not matching your explanation.

I have another problem that im trying to deal with that is more urgent than this. I am trying to create an if statement for a number box on my form. What I want to do is make it so you have to enter in at least 5 numbers in to the box no more no less. I have it half working where if you enter in more than 5 you get an error message but less than 5 does nothing. Here is what i have so far.

Dim number, zipCode As Integer
Dim error As String

#Const number = 0
zipCode = 5

If number < zipCode 
error = " Not enough numbers. \n"

ElseIf number > zipCode Then
error = " To many numbers in field. \n"
End If
'End If

This may not be answered here. New questions require separate threads. On the plus side, that is quite easy for you to do.

[slenish]

Im guessing that it cant be done. Because there is no other way to seperate the information except for the count of characters, and that is not the best because the count can change. It does not stay constant. There could be between 11 - 13 characters.

Apperciate the help on this but it will take longer to figure this out than it will be to seperate by hand. I will just do it the old fashion way.

Also you dont have to be rude when replying to my questions!!

[NeoPa]

Also you dont have to be rude when replying to my questions!!

I wasn't aware I was being. If you'd like to be more specific I could consider what you're saying.

Frankly, I thought I was being quite patient considering the number of times I seemed to need to repeat the same points. Consideration would be shown by replying to points raised by someone trying to help you. I'm quite used to a much lower level of behaviour from the general public though, than I would hold myself to, so I try to show patience.

If you think that being in a position where I need to point out again that you haven't responded to my request for some information that is consistent is somehow an indication that I lack manners, then clearly there is a large gulf between our different understandings of what determines considerate or polite behaviour.

Ultimately, there is no rule that you must reply in any way when requested for information, but to fail to do so is not likely to encourage anyone to want to volunteer their spare time trying to help you. If you want to make that my fault then I'm ok with that, but it won't get you very far ultimately.

[slenish]

It was not the comment of you wanting more information it was the comment of you saying

"This may not be answered here. New questions require separate threads. On the plus side, that is quite easy for you to do.

I find that rude on the basis as it comes off as you saying hey well at least you are smart enough to do this!

I tried to provide as much info as I could. Im not sure what kind of information you are looking for?? I thought I was pretty straight forward. I was going to post a screen shot but it does not look like I can. At this point it seems more trouble than its worth.

So im moving on to my other question in a new post

[TheSmileyCoder]

Hi Sleenish

There are several quite good and easy ways to do this. The problem is for us to decide, we need "high quality" test samples. The language of programming is so specific that when we write it we need to be so as well. What NeoPA (and myself) gets frustrated about is asking the same question to the Original Poster, several times over. Sometimes we end up doing this 5 times per forum visit, and the frustration builds up, and happens to get released on the 5th poster (in this case you).

Please provide 5 records exactly as they stand in your table.

Your example for instance,
[Name 1 Frank] (info)
When I look at that, I know it would be quite easy to make the split if the square brackets [] and the () are truly there. But im unsure as to whether they are truly part of the data, or if its something you added, trying to make your example more clear.

[NeoPa]

It was not the comment of you wanting more information it was the comment of you saying

"This may not be answered here. New questions require separate threads. On the plus side, that is quite easy for you to do.

In that case you can ignore my earlier comments. I can just assure you that my intention when writing that was not to sound patronising. You'd be surprised at the variety of posters we get on here. Some are so unsure of themselves, and so not at home when posting (as it all feels so foreign to them), that they really do post in the same thread because they think just starting a new thread is complicated. I don't wish to bore you with all the reasons why this makes it harder to work with for both the OP (You in this case) and the experts alike, but it's important for all the moderators (I'm also a moderator even though I'm an administrator) to ensure this is fixed when it occurs.

It's obviously easier to guess how someone will react after they've been posting a while, and easier to slip up when dealing with newer members. Let me just say that the wording there was an attempt to be friendly (as a moderator it's very easy to ruffle feathers anyway), rather than the reverse, and absolutely no offense was intended.

[slenish]

Neo Pa,

Everything is cool. I appologize for taking what you were saying the wrong way. I will also work toward trying to make sure I write out my questions better and word things better so they come across clearer. I do apperciate your help in all the questions I have posted thus far. I know you have been there on a few of them and I do apperciate you taking time out or you day to be there for people such as myself. I hope we can just put this behind us and keep moving forward on a positive beat.

See you in the next post

slen

TheSmileyOne about the [] brackets I put around my example. Sorry about that they were not supposed to be part of the wording I was just trying to use those symbolize a column. Is there another way I should be doing this to make it more clear?

Thanks