De-Concatanate

[ddrew]

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?

If you mean

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

then yes that bit worked.

The original field was called 'Odour' and it held 'Drugs (Test)' when I ran the query I got 'Drugs (Test)' in the first column and 'Drugs' in the next. In the 3rd column I got #Error. I was hoping to get 'Test'.

 

[vbaInet]

You said the Mid() function wasn't working so of course I'm asking about the Instr() function within the Mid() function

 

[ddrew]

I have created another query with just a single expression in it

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

It produces the word #Error when I run it!

 

[vbaInet]

Like I said before, test the Instr() function on it's own first. Remember the coloured parentheses? It shows you the boundaries of each function.

 

[ddrew]

Well Idid this:

Instr(1, [Odour], "(")

And it produced the number 7, I'm assuming that means either the amount of characters or the amount of spaces!

But I dont know what the number '1' means in the Instr statement!

 

[vbaInet]

But I dont know what the number '1' means in the Instr statement!

Did you read the help file or at least Google? These are very well documented functions.

 

[ddrew]

I've been reading loads and I think 1 is the start position, is that right?

 

[vbaInet]

Eureka!

But it's not the same position for all functions.

Do you know what the Immediate Window is? When you write a function in a query an explanation of the parameters don't pop up but when you do it in the Immediate Window or better in a module, it will pop up.

 

[vbaInet]

I've been reading loads and I think 1 is the start position, is that right?

It is Left()

 

[vbaInet]

I think it might be too complicated for you, so to put you out of your misery here are two lines of code you can choose from:

Mid(Left([[COLOR="Blue"]Field[/COLOR]], Len([[COLOR="blue"]Field[/COLOR]]) - 1), InStr(1, [[COLOR="blue"]Field[/COLOR]], "(") + 1)
Split(Left([[COLOR="blue"]Field[/COLOR]], Len([[COLOR="blue"]Field[/COLOR]]) - 1), "(", 2)(1)

 

[ddrew]

Eureka!

But it's not the same position for all functions.

Do you know what the Immediate Window is? When you write a function in a query an explanation of the parameters don't pop up but when you do it in the Immediate Window or better in a module, it will pop up.

But the first word, in this case is 'Drugs' other first words could be longer/shorter, so how do I extract the bit that I want, in this case the word 'test'?

Dont know how to bring up the immediate window, sorry.

 

[vbaInet]

But the first word, in this case is 'Drugs' other first words could be longer/shorter, so how do I extract the bit that I want, in this case the word 'test'?

Not sure what you're talking about here. You said the first parameter refers to the start position and I agreed, that was what my comment was related to.

Did you see my last post?

 

[ddrew]

Not sure what you're talking about here. You said the first parameter refers to the start position and I agreed, that was what my comment was related to.

Did you see my last post?

Just saw it, thank you, the top line seems to do what I need, I will take the time to dissect it and try to understand it. Many thanks for your patience.

 

[vbaInet]

No problemo!