Access VBA Len function returning 2 as length of single digit number

Hi
I am using the following code to get an ID number from an Access record and use it to create an invoice number.
So far the variable stride has only contained a single digit number but Len(strID) seems to think there are two digits.
Why would this be?

    strID = CurrentDb.OpenRecordset(F).OpenRecordset.Fields("ID")
    strID = Trim(strID) 'trim off invisible spaces
        
    Dim oInvSuffix As String
    If Len(strID) = 1 Then
         oInvSuffix = "00" & strID
    ElseIf Len(strID) = 2 Then
         oInvSuffix = "0" & strID
    ElseIf Len(strID) = 3 Then
         oInvSuffix = strID
    End If

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Lee W, MVPTechnology and Business Process AdvisorCommented:
Not sure... If field "ID" is a numeric field, try

strID = Trim(Cstr(strID)) 'trim off invisible spaces
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Why are you opening a recordset twice? I've never seen that syntax before.

I would recommend against storing your ID as a string. Instead set the format property of the control in the form/report to "0000" in order to achieve what you are looking for, with no at all, and while still maintaining a numeric ID.
NorieAnalyst Assistant Commented:
What data type is the field ID and how is it populated?

What do you see in arrASCII if you run this code?
Dim arrASCII() As Byte

    arrASCII = StrConv(strID, vbFromUnicode)

Open in new window

The array should contain the ASCII codes for all the characters in ID.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeOwner, Developing Solutions LLCCommented:
Have to admit that I have never seen this syntax before either:

strID = CurrentDb.OpenRecordset(F).OpenRecordset.Fields("ID")

Normally you would have something like:

Dim rs as DAO.REcordset
set rs = currentdb.Openrecordset(F)

strID = rs!ID

Open in new window

But if your ID field is a string, is there a chance that you could have some unicode characters in there which are not displaying?

Try:
For intLoop = 1 to Len(strID)
    debug.print mid(strID, intLoop, 1), ASC(mid(strID, intLoop, 1))
Next

Open in new window

PatHartmanCommented:
If ID is numeric, you are converting it to a string and that could be the problem.  However, there is a very simple method to turn a number into a string with leading zeros.  use the  Format() function.  You need only one line of code.

oInvSuffix = Format(strID, "000")

Use as many zeros as you need for the desired length of the result.  You want to end up with three characters so the string contains 3 zeros.  If you wanted a 6 character result, then you would use 6 zeros.   So 1 would be 001, 42 would be 042 and 876 would be 876.
aikimarkCommented:
Len() will return 2 for Integer expressions/variables and 4 for Long Integer expressions/variables

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
PatHartmanCommented:
Makes sense.  Instinctively I would never have used Len() against a number so I've never even thought about it.  Len() is a string expression.  Only strings are variable in length.  ALL numeric data types are fixed.  So, len() against a numeric data type will return the defined length of that data type.

Anyway, Format(xxx,"00000000000") solves the problem.
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
@aikimark
Nice catch.
I never would have suspected that. The help function does state it quite clearly though. Now this issue actually gets more confusing due to the naming of variables used in the initial post:
strID = CurrentDb.OpenRecordset(F).OpenRecordset.Fields("ID")
    strID = Trim(strID) 'trim off invisible spaces

Open in new window

But based on a bit of testing, its clear that strID is NOT declared as a string, because if it was, then the code would work as originally intended. It must be declared as an integer (Which btw, I wouldn't recommend. Use long for any ID, to avoid integer overflow)

For the original issue, use either the format function if you need to do more string manipulation in code, or use the format property of the control in the form/report.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you  Anders. Pity I had accepted answers because that is very interesting
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.