InStr looking for spaces does not give the expected result

I am getting old but somehow the following code does not yield the desired result:

xName = "Steven Murray Thomas"
MsgBox InStr(1,xName," ")

gives zero and not the expected 7. Any help why?
Thank you so much Michael
mpimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
No. It works fine for me - with a direct copy-paste - returns 7.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
When any type of string operation fails, two things should come to mind:

1. Your not dealing with spaces.  This usually happens when text is the result of a cut and paste.

2. Your option compare statement in the module.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-compare-statement

 Did you type that or cut and paste?

 Also, do you have an Option Explicit at the top of your module (you may have gotten the variable name wrong).

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
as Jim mentioned these might not be spaces.

create a function which you can pass a value to which then prints out the ascii value associated with each character in the string.
Private Function TextToAscii(varValue as variant) as String

    Dim intLoop
    Dim strAscii as string

    if isnull(varValue) then 
        TextToAscii = "NULL"
    else
        for intLoop = 1 to len(varValue)
            strAscii = ", " & asc(mid(varValue, intLoop, 1))
        next
        TextToAscii = Mid(strAscii, 3)
    end if
end function

Open in new window


Then you can call this function from the immediate window, something like:

Call TextToAscii(forms!yourFormName.txtName)

or, if you put a break in your code, you could then call it like:

Call TextToAscii(xName)


if you
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or simply put this statement and see what number it returns. If it doesn't return 32, that means the white space is not the space character.

MsgBox Asc(Mid(xName, 7, 1))

Open in new window

Gustav BrockCIOCommented:
Or just delete your old code and copy-paste your own code here at top … that was what I did.
mpimAuthor Commented:
Thank you so much to all of you. It was indeed as Jim said. It as not a space but a ASCI 160 - I now simply replaced it with CHR(32) and it works fine! Thank you.. Cheers Michael
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.