How Do I saggregate(Saparate)last characture from Text Field in MSAcess Xp Version

[danamma hiremath]

Hi,
I have a field Batch Ticket number data type Text.
Exp
BTNumber 12345R : R Stands for Rework
If I want to separate 12345 and R .which function should I
use:

Any alternate Solution much appreciated

Thanks

 

[Bob Barrows]

Hi,
I have a field Batch Ticket number data type Text.
Exp
BTNumber 12345R : R Stands for Rework
If I want to separate 12345 and R .which function should I
use:

Any alternate Solution much appreciated

Thanks

If it is always 5 characters followed by 1 character, then use the Left()
and Right() functions.
If the pattern can be different, you need to provide further examples if you
wat specific help. In the meantime, look up string functions in Access
Online Help

 

[John W. Vinson]

Hi,
I have a field Batch Ticket number data type Text.
Exp
BTNumber 12345R : R Stands for Rework
If I want to separate 12345 and R .which function should I
use:

Any alternate Solution much appreciated

Thanks

Some combination of InStr, InStrRev, Right and Mid, depending on the actual
problem. Since you only posted one example and no information that would let
us determine the actual scope of the problem, it's hard to be more specific.

For instance: do only some ticket numbers end in R? (I'm guessing so but it's
not clear). Do all records contain the initial text string "BTNumber "? Might
they contain other strings? Is the number always five digits or might there be
other text? Will the number and the R always be in the last part of the
string?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[JHB]

Hi,
I have a field Batch Ticket number data type Text.
Exp
BTNumber 12345R : R Stands for Rework
If I want to separate 12345 and R .which function should I
use:

Any alternate Solution much appreciated

Thanks

If the text always is at the end, then use the Val function.

The Val function stops reading the string at the first character it can't recognize as part of a number.

Jørn

 

[Gene Wirchenko]

If the text always is at the end, then use the Val function.

The Val function stops reading the string at the first character it can't recognize as part of a number.

In general, this is not safe.
val("12345E6")
would give
12345000000
which is probably not what is wanted.

People have lost data or had it corrupted as a result of
"friendly" string to number (or date) conversion. See:
http://catless.ncl.ac.uk/Risks/24.19.html#subj6.1

Sincerely,

Gene Wirchenko

 

[JHB]

In general, this is not safe.
val("12345E6")
would give
12345000000
which is probably not what is wanted.

People have lost data or had it corrupted as a result of
"friendly" string to number (or date) conversion. See:
http://catless.ncl.ac.uk/Risks/24.19.html#subj6.1

Sincerely,

Gene Wirchenko

Yes you are right, sorry.

Jørn