Add Leading 0's to FileName Based on Variable Length

[hamidxa]

I have a piece of code that is used to generate PDF files.
The problem however is that the file names need a 3 digit prefix, based on the variable District.

So for example, it will generate a file named:
1_PM.pdf
Whereas I need it to generate a file named:
001_PM.pdf

This is not a problem for values 100-999, as they by default will have a 3-dig number prefix, but for numbers 1-99, this is definitely an issue.

Not sure how to code for this, but here is what I have presently...thanks in advance for any help! :

[/COLOR][COLOR=#333333]Private Sub Command82_Click() 'Mercator[/COLOR]
[COLOR=#333333]Dim rst As DAO.Recordset[/COLOR]
[COLOR=#333333]Dim strSQL As String[/COLOR]
[COLOR=#333333]Dim strPath As String[/COLOR]

[COLOR=#333333]strSQL = "SELECT [qryDISTRIBUTION_DMList].District FROM [qryDISTRIBUTION_DMList]"[/COLOR]

[COLOR=#333333]Set rst = CurrentDb.OpenRecordset(strSQL)[/COLOR]

[COLOR=#333333]strTo = ""[/COLOR]
[COLOR=#333333]strCC = ""[/COLOR]
[COLOR=#333333]strBCC = ""[/COLOR]
[COLOR=#333333]strFrom = ""[/COLOR]
[COLOR=#333333]strSubject = ""[/COLOR]
[COLOR=#333333]strBody = ""[/COLOR]

[COLOR=#333333]strPath = Me.tbxFolderLocation1[/COLOR]

[COLOR=#333333]With rst[/COLOR]
[COLOR=#333333]If Not .EOF And Not .BOF Then[/COLOR]
[COLOR=#333333].MoveFirst[/COLOR]
[COLOR=#333333]Do While Not .EOF[/COLOR]
[COLOR=#333333]strGlobalWhere = "District=" & !District[/COLOR]

[B][COLOR=#333333]DoCmd.OutputTo acOutputReport, "rptDistrict_Combine_Mercator", acFormatPDF, strPath & "SOPM." & !District & "_PM" & ".pdf", False[/COLOR][/B]

[COLOR=#333333].MoveNext[/COLOR]
[COLOR=#333333]Loop[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]MsgBox "No Region records found", vbOKOnly[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333].Close[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]Set rst = Nothing[/COLOR]
[COLOR=#333333]MsgBox "Done!"[/COLOR]

[COLOR=#333333]End Sub

[/COLOR][COLOR=#333333]

 

[Joe4]

You can format any numeric variable to exactly three digits with leading zeroes like this:
FORMAT(VariableName,"000")

 

[hamidxa]

Joe,
Thanks for that tip!

I will give it a shot asap, but just to be clear, would this work:

DoCmd.OutputTo acOutputReport, "rptDistrict_Combine_Mercator", acFormatPDF, strPath & FORMAT(!District,"000") & "_PM" & ".pdf", False

 

[xenou]

That's fine. Except the SOPM. part of the string concatenation from the original code example is no longer there (whether that was intentional or not I don't know).

 

[hamidxa]

Sorry for any confusion, but yes, it would need to be stripped out.
Just wanted to confirm that this worked, thanks so much again for your support!