VBA Format Append Leading Zeros

[Jester0001]

I have a subform with a text field that I am trying to append leading zeros.



Table = [tblSampleLogIn]
Table = [tblMycoData]
Form = [frmSampleLogIn01]
Form = [subMycoData] ; Field = [SampleID] Text

Here is my code so far:

Private Sub SampleID_AfterUpdate()
Dim srtSampleID As String

Set srtSampleID = Forms![frmSampleLogIn01]![subMycoData].Form![SampleID].Value

Forms!frmSampleLogIn01!subMycoData!SampleID.Value = Format(Forms!frmSampleLogIn01!subMycoData!SampleID.Value, "000000")
End Sub

The text values will be less than 6 characters in length. I can get the immediate window to return the value in the field by entering the following into the immediate window:

Forms![frmSampleLogIn01]![subMycoData].Form![SampleID].Value

But then, nothing else happens when the code runs.

Ultimately, I need to have leading zeros in front of the values to sort the text values. The reason is because sometimes a numerical character is unknown, and will be substituted with a "?". Having leading zeros will help to properly sort the text values.

[June7]

SampleID control event is on the main form and trying to set value of subform? Form/subform master/child linking should handle saving value to subform record. SampleID on main form is not Autonumber?

[ssanfu]

Format only affects the way data is displayed.

If you want to change the text data to have 6 characters, you might think about concatenating 6 zeros to the value , then taking the right 6 using the Right() function.

Forms!frmSampleLogIn01!subMycoData!SampleID = Right( "000000" & Forms!frmSampleLogIn01!subMycoData!SampleID, 6)

FYI, you don't need to add ".Value"..... it is the default property. (and saves on typing )

[June7]

ssanfu,

It is correct that Format only affects display of value in a SELECT query or textbox ControlSource but when used in an equation to set a text value, the leading zeros will be retained. The expression can be in VBA code as shown or in an UPDATE action.

[ssanfu]

Thanks June. I didn't know this. I've always used the concatenate and Right() function; I'll have to remember this and try it.

[Jester0001]

I haven't tried the code yet, but was just wondering:
Might it be better for me to store the data in the table AS IS, and then create a query to concatenate the leading zeros when it comes time to sort?

[Jester0001]

Just tried ssanfu's code, and it works! Thank you!

Forms!frmSampleLogIn01!subMycoData!SampleID = Right( "000000" & Forms!frmSampleLogIn01!subMycoData!SampleID, 6)

I am still wondering though, could the same thing be achieved with a query?

[Jester0001]

Answered my own question: Yes, the same thing can be achieved using a query. And, I believe that I will use the query to concatenate the leading zeros, as opposed to having VBA add the leading zeros. Thank you so much!

[June7]

The expression can be in query or textbox ControlSource or in VBA. Calculate whenever needed or run UPDATE action to actually modify the value in table.

[raytackettsells]

Hey there, I searched all throughout the forums and this was the only one. I was wondering what would be the format in a query in the field which would calculate and place leading zeros in front making the field a specific number of bytes (i.e. whether the number is 4 or 6 digits, it would lead and keep it 7 bytes...

So if it's 1234 and 56789 the number should be converted to 0001234 and 0056789. Just a question and was looking. That would be awesome and thanks ahead of time for any questions you help me with.

[June7]

Use as many zeros in the expression as you want. Try it.