A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

InStr Function not finding space



 
 
Thread Tools Display Modes
  #1  
Old December 2nd, 2009, 10:29 PM posted to microsoft.public.access.queries
Kirk P.
external usenet poster
 
Posts: 84
Default 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  
Old December 2nd, 2009, 10:45 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old December 2nd, 2009, 10:58 PM posted to microsoft.public.access.queries
Kirk P.
external usenet poster
 
Posts: 84
Default 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  
Old December 2nd, 2009, 11:02 PM posted to microsoft.public.access.queries
Kirk P.
external usenet poster
 
Posts: 84
Default 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  
Old December 2nd, 2009, 11:07 PM posted to microsoft.public.access.queries
Kirk P.
external usenet poster
 
Posts: 84
Default 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  
Old December 2nd, 2009, 11:07 PM posted to microsoft.public.access.queries
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old December 2nd, 2009, 11:13 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old December 2nd, 2009, 11:20 PM posted to microsoft.public.access.queries
Kirk P.
external usenet poster
 
Posts: 84
Default 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  
Old December 2nd, 2009, 11:36 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.