InStr Function not finding space

  • Thread starter Thread starter Kirk P.
  • Start date Start date
K

Kirk P.

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.
 
Strange. Are you sure that it's not something like an underscore _ that
look's like a 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 said:
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. said:
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.
Click to expand...
Click to expand...
 
Numbers are represented like this in the source file:

(USD 65.00)

Kirk P. said:
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 said:
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. said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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. said:
Numbers are represented like this in the source file:

(USD 65.00)

Kirk P. said:
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 said:
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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
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

(no private e-mails, please)


Kirk P. said:
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 said:
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. said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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. said:
Numbers are represented like this in the source file:

(USD 65.00)

Kirk P. said:
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 said:
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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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. said:
Numbers are represented like this in the source file:

(USD 65.00)

Kirk P. said:
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!



:

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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
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. said:
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 said:
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. said:
Numbers are represented like this in the source file:

(USD 65.00)

:

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!



:

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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top