INT conversion

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
 
E

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
 
R

raskew via AccessMonster.com

Rich -

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


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...
 
R

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

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
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...
 
R

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

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 -
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...
 
R

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the proper style field and the numbered paragraph value within the style 0
Finding the right style field in VBA 0
SQL Function not returning same value over ADP 1
Bound Form Search 5
parameter query and making a new record 4
Round up or down to nearest 5, but not 10 2
Calling a function in my SQL-DB from VBA 0
Previous record in an expression 1

Top