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
|
|||
|
|||
InStr Function not finding space
I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like
this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#2
|
|||
|
|||
InStr Function not finding space
Strange. Are you sure that it's not something like an underscore _ that
look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#3
|
|||
|
|||
InStr Function not finding space
No, not an underscore. The source data is an HTML file. Opening the HTML
file in Word Pad reveals that numbers are represented like this: (USD 65.00) I'm trying to find the first occurance of a space, and then replace the 3 characters prior to the space with a zero length string. Obviously the trouble I'm having is finding the space! "Jerry Whittle" wrote: Strange. Are you sure that it's not something like an underscore _ that look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#4
|
|||
|
|||
InStr Function not finding space
Numbers are represented like this in the source file:
(USD 65.00) "Kirk P." wrote: No, not an underscore. The source data is an HTML file. Opening the HTML file in Word Pad reveals that numbers are represented like this: (USD 65.00) I'm trying to find the first occurance of a space, and then replace the 3 characters prior to the space with a zero length string. Obviously the trouble I'm having is finding the space! "Jerry Whittle" wrote: Strange. Are you sure that it's not something like an underscore _ that look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#5
|
|||
|
|||
InStr Function not finding space
I can't even type it in because it is correctly interpreting it as a
non-breaking space and removes the nbsp between the USD and the 65.00! It seems when I import the data into an Access table, this "nbsp" designator isn't being interpreted as a " " (space), but it certainly appears in the table to look like a space. "Kirk P." wrote: Numbers are represented like this in the source file: (USD 65.00) "Kirk P." wrote: No, not an underscore. The source data is an HTML file. Opening the HTML file in Word Pad reveals that numbers are represented like this: (USD 65.00) I'm trying to find the first occurance of a space, and then replace the 3 characters prior to the space with a zero length string. Obviously the trouble I'm having is finding the space! "Jerry Whittle" wrote: Strange. Are you sure that it's not something like an underscore _ that look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#6
|
|||
|
|||
InStr Function not finding space
It's still possible that it's some other character than a space.
For the sample you show (where you think the space is in the fifth position), what's returned by Asc(Mid([MyVariable], 5, 1))? If it's anything other than 32, then it's not a space. Fortunately, once you know what the character is, you'll be able to search for it. Let's assume that the expression above returned 142. You'd then be able to use InStr(Trim([NET_SALES]),Chr(142)) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Kirk P." wrote in message ... No, not an underscore. The source data is an HTML file. Opening the HTML file in Word Pad reveals that numbers are represented like this: (USD 65.00) I'm trying to find the first occurance of a space, and then replace the 3 characters prior to the space with a zero length string. Obviously the trouble I'm having is finding the space! "Jerry Whittle" wrote: Strange. Are you sure that it's not something like an underscore _ that look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#7
|
|||
|
|||
InStr Function not finding space
You should verify what Jerry Whittle ask by doing this --
Expr1: Asc(Right(Left([YourField], 4),1)) A space will result in 32 as the output. If you get some other value then it is not a space. -- Build a little, test a little. "Kirk P." wrote: Numbers are represented like this in the source file: (USD 65.00) "Kirk P." wrote: No, not an underscore. The source data is an HTML file. Opening the HTML file in Word Pad reveals that numbers are represented like this: (USD 65.00) I'm trying to find the first occurance of a space, and then replace the 3 characters prior to the space with a zero length string. Obviously the trouble I'm having is finding the space! "Jerry Whittle" wrote: Strange. Are you sure that it's not something like an underscore _ that look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#8
|
|||
|
|||
InStr Function not finding space
This HTML non-breaking space (nbsp) is Chr(160), so this works:
InStr(1,Trim([NET_SALES]),Chr(160)) but this doesn't InStr(1,Trim([NET_SALES])," ") "KARL DEWEY" wrote: You should verify what Jerry Whittle ask by doing this -- Expr1: Asc(Right(Left([YourField], 4),1)) A space will result in 32 as the output. If you get some other value then it is not a space. -- Build a little, test a little. "Kirk P." wrote: Numbers are represented like this in the source file: (USD 65.00) "Kirk P." wrote: No, not an underscore. The source data is an HTML file. Opening the HTML file in Word Pad reveals that numbers are represented like this: (USD 65.00) I'm trying to find the first occurance of a space, and then replace the 3 characters prior to the space with a zero length string. Obviously the trouble I'm having is finding the space! "Jerry Whittle" wrote: Strange. Are you sure that it's not something like an underscore _ that look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
#9
|
|||
|
|||
InStr Function not finding space
A space and a non-breaking space are two different things, so use
InStr(1,Trim([NET_SALES]),Chr(160)). -- Build a little, test a little. "Kirk P." wrote: This HTML non-breaking space (nbsp) is Chr(160), so this works: InStr(1,Trim([NET_SALES]),Chr(160)) but this doesn't InStr(1,Trim([NET_SALES])," ") "KARL DEWEY" wrote: You should verify what Jerry Whittle ask by doing this -- Expr1: Asc(Right(Left([YourField], 4),1)) A space will result in 32 as the output. If you get some other value then it is not a space. -- Build a little, test a little. "Kirk P." wrote: Numbers are represented like this in the source file: (USD 65.00) "Kirk P." wrote: No, not an underscore. The source data is an HTML file. Opening the HTML file in Word Pad reveals that numbers are represented like this: (USD 65.00) I'm trying to find the first occurance of a space, and then replace the 3 characters prior to the space with a zero length string. Obviously the trouble I'm having is finding the space! "Jerry Whittle" wrote: Strange. Are you sure that it's not something like an underscore _ that look's like a space? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kirk P." wrote: I'm using this function InStr(Trim([NET_SALES])," ") on data that looks like this: (USD 65.00) and it is returning a zero. I'm trying to find the first occurance of a space, however it seems what appears to be a space really isn't. When I edit the field to delete the "space" and re-enter it, it returns the correct position 5. I have tried InStr([NET_SALES]," ") as well with no luck. |
Thread Tools | |
Display Modes | |
|
|