INT conversion

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Hi,
I'm running an update query on tblInv that, among other things,
extracts an important number from within a string in the text field
[JobNum] . In it's original form, [JobNum]is as follows: From 11965
OFG The critical number I'm extracting always starts in the 6th
position, and is 4 or 5 charactors in length with random text
afterwards.
I use SET tblInvData.JobNum =
LTrim(RTrim(Right(Left(tblInvData.JobNum,10),5))) to extract the
number, and this works fine. Then I manually change the format of the
field to "number" in the table's design. It occured to me that I
should be able to acheive both goals by changing my syntax to SET
tblInvData.JobNum = INT(LTrim(RTrim(Right(Left(tblInvData.JobNum,10),
5)))), but it doesn't change the field to number integer format.Does
anyone know why that doesn't work?
Thanks in advance to all who might help me with this.
Rich
 
Turn your edited string into a number by using Val on it, then surround THAT
with Int Function. I suspect that your number is still a string until it has
been 'val'd
Evi
 
Rich -

See John Spencer's function at:http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/39325/conv...

I tested it against your example and it worked as advertised.

Bob




Hi,
I'm running an update query on tblInv that, among other things,
extracts an important number from within a string in the text field
[JobNum] .  In it's original form, [JobNum]is as follows: From 11965
OFG       The critical number I'm extracting always starts in the 6th
position, and is 4 or 5 charactors in length with random text
afterwards.
I use SET tblInvData.JobNum =
LTrim(RTrim(Right(Left(tblInvData.JobNum,10),5))) to extract the
number, and this works fine. Then I manually change the format of the
field to "number" in the table's design.  It occured to me that I
should be able to acheive both goals by changing my syntax to SET
tblInvData.JobNum = INT(LTrim(RTrim(Right(Left(tblInvData.JobNum,10),
5)))), but it doesn't change the field to number integer format.Does
anyone know why that doesn't work?
Thanks in advance to all who might help me with this.
Rich
Click to expand...
Click to expand...

If I use Val, I risk picking up other numbers in the random (operator
generated) text that follows my critical number. Since I can count on
the first (system generated) 10 charactors ALWAYS being "From
#####....." , I tried using
Val(Left(tblInvData.JobNum,10) but that returned all zeros . What am
I missing here??
 
Try
Val(Mid(tblInvData.JobNum,6,5))

Val looks at all the characters in a string until it hits a non-numeric
character and then it stops. So if your field start with "FROM ...", Val is
going to see the F and stop processing and return 0.

You could still run into a problem, if the next characters after the number
are D and a number or E and a number. Val will treat that combination as if
it were scientific notation.

Also, be aware that VAL will strip off leading zeroes.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Rich -

See John Spencer's function at:http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/39325/conv...

I tested it against your example and it worked as advertised.

Bob




Hi,
I'm running an update query on tblInv that, among other things,
extracts an important number from within a string in the text field
[JobNum] . In it's original form, [JobNum]is as follows: From 11965
OFG The critical number I'm extracting always starts in the 6th
position, and is 4 or 5 charactors in length with random text
afterwards.
I use SET tblInvData.JobNum =
LTrim(RTrim(Right(Left(tblInvData.JobNum,10),5))) to extract the
number, and this works fine. Then I manually change the format of the
field to "number" in the table's design. It occured to me that I
should be able to acheive both goals by changing my syntax to SET
tblInvData.JobNum = INT(LTrim(RTrim(Right(Left(tblInvData.JobNum,10),
5)))), but it doesn't change the field to number integer format.Does
anyone know why that doesn't work?
Thanks in advance to all who might help me with this.
Rich
Click to expand...
Click to expand...

If I use Val, I risk picking up other numbers in the random (operator
generated) text that follows my critical number. Since I can count on
the first (system generated) 10 charactors ALWAYS being "From
#####....." , I tried using
Val(Left(tblInvData.JobNum,10) but that returned all zeros . What am
I missing here??
Click to expand...
 
Try
   Val(Mid(tblInvData.JobNum,6,5))

Val looks at all the characters in a string until it hits a non-numeric
character and then it stops.  So if your field start with "FROM ...", Val is
going to see the F and stop processing and return 0.

You could still run into a problem, if the next characters after the number
are D and a number or E and a number.  Val will treat that combination as if
it were scientific notation.

Also, be aware that VAL will strip off leading zeroes.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County


Rich -
See John Spencer's function at:http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/39325/conv...
I tested it against your example and it worked as advertised.
Bob
Rich wrote:
Hi,
I'm running an update query on tblInv that, among other things,
extracts an important number from within a string in the text field
[JobNum] .  In it's original form, [JobNum]is as follows: From 11965
OFG       The critical number I'm extracting always starts in the 6th
position, and is 4 or 5 charactors in length with random text
afterwards.
I use SET tblInvData.JobNum =
LTrim(RTrim(Right(Left(tblInvData.JobNum,10),5))) to extract the
number, and this works fine. Then I manually change the format of the
field to "number" in the table's design.  It occured to me that I
should be able to acheive both goals by changing my syntax to SET
tblInvData.JobNum = INT(LTrim(RTrim(Right(Left(tblInvData.JobNum,10),
5)))), but it doesn't change the field to number integer format.Does
anyone know why that doesn't work?
Thanks in advance to all who might help me with this.
Rich
Click to expand...
Click to expand...
If I use Val, I risk picking up other numbers in the random (operator
generated) text that follows my critical number.  Since I can count on
the first (system generated) 10 charactors ALWAYS being "From
#####....." ,  I tried using
Val(Left(tblInvData.JobNum,10)  but that returned all zeros . What am
I missing here??- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

thanks John, that does work.....but even if I use
INT(Val(Mid(tblInvData.JobNum,6,5))), the result of the update is
still a text field. Does that make sense to you?
 
No, that makes no sense at all, since VAL should return a numeric value and
not a text value. There must be something else going on to force things back
to a text value.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Try
Val(Mid(tblInvData.JobNum,6,5))

Val looks at all the characters in a string until it hits a non-numeric
character and then it stops. So if your field start with "FROM ...", Val is
going to see the F and stop processing and return 0.

You could still run into a problem, if the next characters after the number
are D and a number or E and a number. Val will treat that combination as if
it were scientific notation.

Also, be aware that VAL will strip off leading zeroes.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County


Rich -
See John Spencer's function at:http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/39325/conv...
I tested it against your example and it worked as advertised.
Bob
Rich wrote:
Hi,
I'm running an update query on tblInv that, among other things,
extracts an important number from within a string in the text field
[JobNum] . In it's original form, [JobNum]is as follows: From 11965
OFG The critical number I'm extracting always starts in the 6th
position, and is 4 or 5 charactors in length with random text
afterwards.
I use SET tblInvData.JobNum =
LTrim(RTrim(Right(Left(tblInvData.JobNum,10),5))) to extract the
number, and this works fine. Then I manually change the format of the
field to "number" in the table's design. It occured to me that I
should be able to acheive both goals by changing my syntax to SET
tblInvData.JobNum = INT(LTrim(RTrim(Right(Left(tblInvData.JobNum,10),
5)))), but it doesn't change the field to number integer format.Does
anyone know why that doesn't work?
Thanks in advance to all who might help me with this.
Rich
--
Message posted viahttp://www.accessmonster.com-Hide quoted text -
- Show quoted text -
If I use Val, I risk picking up other numbers in the random (operator
generated) text that follows my critical number. Since I can count on
the first (system generated) 10 charactors ALWAYS being "From
#####....." , I tried using
Val(Left(tblInvData.JobNum,10) but that returned all zeros . What am
I missing here??- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

thanks John, that does work.....but even if I use
INT(Val(Mid(tblInvData.JobNum,6,5))), the result of the update is
still a text field. Does that make sense to you?
Click to expand...
 
No, that makes no sense at all, since VAL should return a numeric value and
not a text value.  There must be something else going on to force thingsback
to a text value.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County


Try
   Val(Mid(tblInvData.JobNum,6,5))
Val looks at all the characters in a string until it hits a non-numeric
character and then it stops.  So if your field start with "FROM ...",Val is
going to see the F and stop processing and return 0.
You could still run into a problem, if the next characters after the number
are D and a number or E and a number.  Val will treat that combination as if
it were scientific notation.
Also, be aware that VAL will strip off leading zeroes.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Rich wrote:
Rich -
See John Spencer's function at:http://www.accessmonster.com/Uwe/Forum..aspx/access-queries/39325/conv...
I tested it against your example and it worked as advertised.
Bob
Rich wrote:
Hi,
I'm running an update query on tblInv that, among other things,
extracts an important number from within a string in the text field
[JobNum] .  In it's original form, [JobNum]is as follows: From 11965
OFG       The critical number I'm extracting always starts in the 6th
position, and is 4 or 5 charactors in length with random text
afterwards.
I use SET tblInvData.JobNum =
LTrim(RTrim(Right(Left(tblInvData.JobNum,10),5))) to extract the
number, and this works fine. Then I manually change the format of the
field to "number" in the table's design.  It occured to me that I
should be able to acheive both goals by changing my syntax to SET
tblInvData.JobNum = INT(LTrim(RTrim(Right(Left(tblInvData.JobNum,10),
5)))), but it doesn't change the field to number integer format.Does
anyone know why that doesn't work?
Thanks in advance to all who might help me with this.
Rich
--
Message posted viahttp://www.accessmonster.com-Hidequoted text -
- Show quoted text -
If I use Val, I risk picking up other numbers in the random (operator
generated) text that follows my critical number.  Since I can count on
the first (system generated) 10 charactors ALWAYS being "From
#####....." ,  I tried using
Val(Left(tblInvData.JobNum,10)  but that returned all zeros . What am
I missing here??- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
thanks John, that does work.....but even if I use
INT(Val(Mid(tblInvData.JobNum,6,5))), the result of the update is
still a text field.  Does that make sense to you?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

OK....thanks again for the assistance
Rich
 
Back
Top