Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

Avatar of Lee W, MVP
Lee W, MVP
Flag of United States of America image

Not sure... If field "ID" is a numeric field, try

strID = Trim(Cstr(strID)) 'trim off invisible spaces
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.
Avatar of Norie
Norie

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

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.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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.
Avatar of Murray Brown

ASKER

Thank you  Anders. Pity I had accepted answers because that is very interesting