De-Concatanate

[ddrew]

Hi I need to de-Concatanate a field, there are some common things in it the field but I don't know how to go about it. The field would contain something like

Cadaver (Blood) or Explosives (RDX TNT)

I need to break it down into something like:


Cadaver (

Blood

)

or

Explosives (

RDX TNT

)

Hope this makes sense!

 

[pr2-eugin]

Without a proper Data Structure, giving a suggestion might be hard.

 

[ddrew]

Sorry not sure what you mean. The fields that they would be broken down into are all text fields if that's what you mean.

 

[pr2-eugin]

No ! What I meant was a generic form of data. In the example the constants which we can look at are "(", ")", and the " or " Keyword to break the long string into multiple segments. If this is only one pattern well and good. But if there could also be something like "/", "*", " and ". Then we have to change the code. Understand what I mean?

 

[vbaInet]

So show pr2 the different variants of that field. Show lots more examples (if there are different).

 

[ddrew]

OK got you.
Examples then are:

Cadaver (Blood)
Cadaver (Skin)
Explosives (RDX TNT)
Explosives (Semtex A)
Explosives (Semtex H)
Explosives (Semtex A+H)
Drugs (Heroin)
Drugs (Scentlogix Cannabis)
Money (Paper)
Money (Coin)
Firearms (Weapon Parts)

Hopefully that's enough. There are certainly 'constants' in there, those being it will always be [Word, Space, Open Brackets, Word, Close Brackets]

 

[vbaInet]

I remember the word "Cadaver" from your other thread

Over to you pr2-eugin!

 

[ddrew]

I remember the word "Cadaver" from your other thread

Over to you pr2-eugin!

Yup this is the very last bit, everything else is fine but I just need to understand how to do this.

 

[vbaInet]

Over to you pr2-eugin!

Actually over to you ddrew

You will need four things functions:
Left(), Mid() and Right() and Instr()

Give those a go!

 

[pr2-eugin]

Over to you pr2-eugin!

So kind of you !

Yup this is the very last bit, everything else is fine but I just need to understand how to do this.

Well we need to have a bit more info now, where do you want to use this info? Form text box? What are you intending to do with the output? If you only wish to know how, this is how.

1. You first get the String, use InStr function to find the first occurrence of the ( then break the string there.
2. Loop until you can find the next character.

The above is very basic, complication will arise ! As the sequence of how the special characters occur will have an impact on this looping.

 

[ddrew]

So kind of you !
Well we need to have a bit more info now, where do you want to use this info? Form text box? What are you intending to do with the output? If you only wish to know how, this is how.

1. You first get the String, use InStr function to find the first occurrence of the ( then break the string there.
2. Loop until you can find the next character.

The above is very basic, complication will arise ! As the sequence of how the special characters occur will have an impact on this looping.

It will be appended into a table [tbl_Odour] Fields are [txt_OdourType] (this is where this bit Cadaver ( goes. Next Field is [txt_Odour] this where this bit Blood goes. Next field is [txt_Bracket] this is where this bit ) goes. Finally [txt_FullName] this holds the concatenated name Cadaver (Blood)

Hope that makes sense and I thank you for your time and patience.

 

[vbaInet]

So did you give the functions mentioned a try?

 

[ddrew]

So did you give the functions mentioned a try?

Not yet, I'm at work but will have a go this evening!

 

[vbaInet]

I'll start you off with the first part:

Left[COLOR="Blue"]([/COLOR][Field], Instr[COLOR="Red"]([/COLOR]1, [Field], "("[COLOR="red"])[/COLOR] - 1[COLOR="blue"])[/COLOR]

The colour parentheses is just to show you where each functions parentheses begins and ends.

By the way you don't need to extract the ")" because you already know it's going to be ")".

 

[namliam]

Sounds like more of a job for a custom build function, sounds like a "simple" space delimited situation unless you are within brackets.

 

[vbaInet]

Sounds like more of a job for a custom build function, sounds like a "simple" space delimited situation unless you are within brackets.

It's a follow up to one of ddrew's threads. He's splitting the values once so that he can save them into separate fields. A one off exercise really.

 

[ddrew]

I'll start you off with the first part:

Left[COLOR="Blue"]([/COLOR][Field], Instr[COLOR="Red"]([/COLOR]1, [Field], "("[COLOR="red"])[/COLOR] - 1[COLOR="blue"])[/COLOR]

The colour parentheses is just to show you where each functions parentheses begins and ends.

By the way you don't need to extract the ")" because you already know it's going to be ")".

Well Iv'e been bashing around with this for best part of an hour and a half, not really sure what Im doing!

Where should I be writing this, I've been trying to put it as an expression in the Append Query but I'm not sure that's the right place!

 

[vbaInet]

Start with a SELECT query, get the desired result before thinking about an UPDATE query. You can convert it later. It needs to go in as a field in the query. Think of it as creating a new field from an existing one.

 

[ddrew]

OK got the left working but when I do Mid that produces the word "Error" when I run the query.

This was what I used for Left

Left([Odour],InStr(1,[Odour],"(")-1)

And for Mid

 Mid([Odour],InStr("("+1,[Odour],")")-1)

 

[vbaInet]

When you first start out writing code as a beginner it's best to test one unit at a time. So with that in mind, did your Instr() function work in the first place?