Solved Can I run FORMAT function using VBA? (1 Viewer)

[iCyrius]

Hello. My code reads a number from a table and needs to be converted to string. In Excel's VBA you can use application.worksheetFunction to trigger any Excel Formula, but I cannot find that same type of code in Access.

My line would look somethin like this,

StrIDLocator = FORMAT(me.Number, "000000")

Thanks

 

[Minty]

I would always advise against converting a number to a string unless it is for display purposes.
Your code looks fine assuming this is to display leading zero's e.g.
100 > 000100
2063 > 002063
etc. etc.

 

[plog]

Works for me:

Private Sub test()

    Dim i As Integer
    Dim s As String
   
    i = 7
    s = Format(i, "000000")
    MsgBox (s)

End Sub

When I run the above it shows me a dialog with 000007

Can you post more of your code? Are you sure Me.Number contains data?

 

[theDBguy]

Hi. Welcome to AWF!

What happens if you tried that formula in Access?

 

[arnelgp]

you need to create a Form for you table.
add code to AfterUpdate event of Number field:

Private Sub Number_AfterUpdate()
StrIDLocator = FORMAT(me.Number, "000000")
End sub

 

[iCyrius]

Thank you for your help. It did not occur to me to write it like I posted it. I was trying something like,

StrIDLocator = application.function("FORMAT(me.Number,"0000")

It works now. Perfect!

 

[CJ_London]

You can also use the format property for a field or control if you want to retain the underlying numeric value