Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

InStr Function not finding space

236 views
Skip to first unread message

Kirk P.

unread,
Dec 2, 2009, 4:29:02 PM12/2/09
to
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.


Jerry Whittle

unread,
Dec 2, 2009, 4:45:01 PM12/2/09
to
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.

unread,
Dec 2, 2009, 4:58:01 PM12/2/09
to
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!

Kirk P.

unread,
Dec 2, 2009, 5:02:01 PM12/2/09
to
Numbers are represented like this in the source file:

(USD 65.00)

Kirk P.

unread,
Dec 2, 2009, 5:07:01 PM12/2/09
to
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.

Douglas J. Steele

unread,
Dec 2, 2009, 5:07:40 PM12/2/09
to
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." <Ki...@discussions.microsoft.com> wrote in message
news:20E79768-165F-4A96...@microsoft.com...

KARL DEWEY

unread,
Dec 2, 2009, 5:13:01 PM12/2/09
to
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.

unread,
Dec 2, 2009, 5:20:03 PM12/2/09
to
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

unread,
Dec 2, 2009, 5:36:01 PM12/2/09
to
A space and a non-breaking space are two different things, so use
InStr(1,Trim([NET_SALES]),Chr(160)).
0 new messages