[RESOLVED] VBA automatically adds a leading zero to a number stored as text

I have an Excel spreadsheet where the user enters a code and then clicks a button. The macro takes the code and looks it up in the database to return data. The issue I am having is that the code being entered is .16225 and VBA is adding a leading zero so the query is returning "Not Found". I don't understand why it is adding the leading zero. The cell is formatted as Text and when using in my query I am using Format(). Below is the snippet of code along with a visual of how it is being interpreted.

Code:

---

   
    Call rsInit(rsData)
    rsData.Source = ("SELECT * FROM bank WHERE scode = '" & Format(Cells(xRow, xCol)) & "'")
    rsData.Open

    If rsData.EOF Then
        MsgBox Format(Cells(xRow, xCol)) & " is not a valid bank code in Yardi.", vbOKOnly, "Invalid Bank Code"
        GoTo WrongCode
    End If

---

Image showing the cell is formatted as "Text"

Attachment 191304



Image showing the error message and how it is being interpreted by VBA.

Attachment 191305

How do I get rid of the leading zero? And it's not as easy as coding to remove a leading zero. There could in fact be valid codes that do begin with a zero.

Thank you for any help you can offer!
Chrissy

I am answering my own question. Apparently if I use Cstr instead of Format, that works. Strange... never had this issue before!

Quote:

---

Originally Posted by Chrissy

Strange... never had this issue before!

---

It's always the best to know how things work, usually means to find the root cause of any problem you meet.

What you have here is a String as a result of Cells(xRow, xCol) invocation. Then you call Format with this String as a parameter but Format expects a number so your String (in a Variant parameter) is cast to Double at some point inside Format code, then this Double is formatted and the result returned by Format is a String.

You "fixed" your code by substituting Format for CStr so now you call CStr on a String just to get a String. This is called no-op from "no operation" like "nothing happens here" and usually is completely redundant and dead code should be quickly removed during code reviews.

The moral of the story: Don't call Format *on Strings* if you don't want to format anything as it might change your input into so called "canonical representation" of the number.

cheers,
</wqw>

Quote:

---

Originally Posted by wqweto

It's always the best to know how things work, usually means to find the root cause of any problem you meet.

What you have here is a String as a result of Cells(xRow, xCol) invocation. Then you call Format with this String as a parameter but Format expects a number so your String (in a Variant parameter) is cast to Double at some point inside Format code, then this Double is formatted and the result returned by Format is a String.

You "fixed" your code by substituting Format for CStr so now you call CStr on a String just to get a String. This is called no-op from "no operation" like "nothing happens here" and usually is completely redundant and dead code should be quickly removed during code reviews.

The moral of the story: Don't call Format *on Strings* if you don't want to format anything as it might change your input into so called "canonical representation" of the number.

cheers,
</wqw>

---

That explains why it was adding the leading zero, thank you for the explanation! I guess I don't need Cstr() either. :)