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

  • Thread starter Thread starter danamma hiremath
  • Start date Start date
D

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
 
danamma said:
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
Click to expand...

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
 
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
Click to expand...

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
 
danamma hiremath said:
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
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top