|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 6th, 2008, 09:31 PM
posted to microsoft.public.access.queries
|
|||
|
|||
INT conversion
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 
|
|
#2
May 6th, 2008, 09:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
INT conversion
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" wrote in message ... 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
|
|
#3
May 6th, 2008, 10:02 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
INT conversion
Rich -
See John Spencer's function at: http://www.accessmonster.com/Uwe/For...text-to-number 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 via http://www.accessmonster.com
|
|
#4
May 7th, 2008, 03:20 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
INT conversion
On May 6, 5:02*pm, "raskew via AccessMonster.com" u28575@uwe wrote:
Rich - See John Spencer's function at:http://www.accessmonster.com/Uwe/For...ies/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??
|
|
#5
May 7th, 2008, 05:42 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
INT conversion
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: On May 6, 5:02 pm, "raskew via AccessMonster.com" u28575@uwe wrote: Rich - See John Spencer's function at:http://www.accessmonster.com/Uwe/For...ies/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??
|
|
#6
May 12th, 2008, 01:36 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
INT conversion
On May 7, 12:42*pm, John Spencer wrote:
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: On May 6, 5:02 pm, "raskew via AccessMonster.com" u28575@uwe wrote: Rich - See John Spencer's function at:http://www.accessmonster.com/Uwe/For...ies/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?
|
|
#7
May 12th, 2008, 08:05 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
INT conversion
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 Rich wrote: On May 7, 12:42 pm, John Spencer wrote: 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: On May 6, 5:02 pm, "raskew via AccessMonster.com" u28575@uwe wrote: Rich - See John Spencer's function at:http://www.accessmonster.com/Uwe/For...ies/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?
|
|
#8
May 13th, 2008, 08:42 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
INT conversion
On May 12, 3:05*pm, John Spencer wrote:
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 Rich wrote: On May 7, 12:42 pm, John Spencer wrote: 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: On May 6, 5:02 pm, "raskew via AccessMonster.com" u28575@uwe wrote: Rich - See John Spencer's function at:http://www.accessmonster.com/Uwe/For...ies/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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
