string function ( right function ) (1 Viewer)

murray83 · Sep 20, 2017

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

Code:

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 · Sep 20, 2017

you want the left 3 of the right4:

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

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

Minty · Sep 20, 2017

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

static · Sep 20, 2017

If it's the only value in brackets

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

murray83 · Sep 20, 2017

Ranman256 said:
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 · Sep 20, 2017

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

Code:

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

???

static · Sep 20, 2017

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 · Sep 20, 2017

no need to keep original just the trimmed

so would i put that in the control source ?

or on button click event

static · Sep 20, 2017

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 · Sep 20, 2017

query would this work

static · Sep 20, 2017

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 · Sep 20, 2017

static said:
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

Code:

TrimmedValue: right([total],7)

into criteria and had the following error

static · Sep 20, 2017

edit reading fail

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

murray83 · Sep 20, 2017

here is the database so you can see

arnelgp · Sep 20, 2017

Use:

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

static · Sep 20, 2017

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 · Sep 20, 2017

arnelgp said:
Use:

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

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

arnelgp · Sep 21, 2017

Yes you are correct. Can parse any length.

string function ( right function ) (1 Viewer)

Games Collector

Well-known member

AWF VIP

Registered User.

Games Collector

Attachments

Games Collector

Registered User.

Games Collector

Registered User.

Games Collector

Attachments

Registered User.

Games Collector

Attachments

Registered User.

Games Collector

Attachments

..forever waiting... waiting for jellybean!

Registered User.

Games Collector

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread