INT conversion

[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

 

[Evi]

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]

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

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??

 

[John Spencer]

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

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??

 

[Rich]

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

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 -

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?

 

[John Spencer]

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 -

- Show quoted text -

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?

 

[Rich]

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 -

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 -

- Show quoted text -

OK....thanks again for the assistance
Rich