string function ( right function )

[murray83]

this is what i have found by searching the great wide web

Right([SerialNumber],3)

If [SerialNumber] is “CD234”, the result is “234”.

ok that is fine but i dont/cant put that in the control source and would like a VBA way of doing it on update or click of command button

this is my data

062963340006 003 B003SEP2191558BA0423 2 (300)

and the bit i need to trim is the "2 (300)"

all i need to keep is the data with in the brackets

help appreciated

 

[Ranman256]

you want the left 3 of the right4:

=left(Right(field,4),3)

(if its always a 3 digit string in the parens)

 

[Minty]

Is the (300) always in the exactly the same place - same number of characters into the string ?

 

[static]

If it's the only value in brackets

value = Split(Replace("062963340006 003 B003SEP2191558BA0423 2 (300)", ")", "("), "(")(1)

 

[murray83]

you want the left 3 of the right4:

=left(Right(field,4),3)

(if its always a 3 digit string in the parens)

Is the (300) always in the exactly the same place - same number of characters into the string ?

In answer to both questions no its not always 300 it's between 1 and N but yes always same place

if this helps this is a screen shot of the place im pasting it into

 

[murray83]

could i put something like this in the on current or on load for the form

Me.Total.Value = Right([Total], 2)

???

 

[static]

Where does the value come from?
Do you need to keep the original value or just the trimmed result?

If you store and need to keep the whole string you can just use a calculated value in a field or control.
=right(total,7)

 

[murray83]

no need to keep original just the trimmed

so would i put that in the control source ?

or on button click event

 

[static]

If you keep the original value...

You would put that in the control source of a textbox

or a field in query

TrimmedValue: right(total,7)

which is the source for a control.

You didn't answer the first question.

If you paste the value into a control you would strip out the unneeded part in an event e.g. the controls beforeupdate event so only the part you need is stored in the table.

 

[murray83]

query would this work

 

[static]

You have put the string function in the criteria row.
Criteria is used for filtering.

Instead of Total you need to type what I wrote above.

TrimmedValue: right([total],7)

Where TrimmedValue is the name you want to give the calculated field.

Right gets the right of the string you pass in, in this case the field total.

'2 (300)' is 7 characters, not 3.

 

[murray83]

You have put the string function in the criteria row.
Criteria is used for filtering.

Instead of Total you need to type what I wrote above.

TrimmedValue: right([total],7)

Where TrimmedValue is the name you want to give the calculated field.

Right gets the right of the string you pass in, in this case the field total.

'2 (300)' is 7 characters, not 3.

ok i pasted

TrimmedValue: right([total],7)

into criteria and had the following error

 

[static]

edit reading fail

you need to remove the criteria and paste into the field row

 

[murray83]

here is the database so you can see

 

[arnelgp]

Use:

Mid([Total],Instrrev([Total], "(")+1,Instrrev([Total], ") ")-Instrrev([Total], "(")-1)

 

[static]

You added the new field.
You just need to change the controlsource for the Total textbox in the subform to the new field.

View attachment de-allocate.accdb

The data in your table didn't match the format you said it should be.

 

[murray83]

Use:

Mid([Total],Instrrev([Total], "(")+1,Instrrev([Total], ") ")-Instrrev([Total], "(")-1)

im guessing you mean in the query, in the field yeah

 

[arnelgp]

Yes you are correct. Can parse any length.