How set custom format property via VBA

[mscertified]

hI've tried everyting and have just about given up. Have printed out help but
it does not say how to program this.
This is what I have tried, I'm getting really weird results like part of a
date/time field and sometimes a literal string in the result, If I code a
normal format it works ok but not with a custom format:

' Set format of number
Dim strQ1 As String, strQ2 As String, strF As String
strQ1 = """"
strQ2 = """" & """"
Select Case Me!Format
Case "C"
strF = "Currency;(Currency);" & strQ2 & ";" & strQ2
Me!PimaDual.Format = strF
Me!PinalDual.Format = strF
Me!YavapaiDual.Format = strF
Me!MaricopaDual.Format = strF
Me!PimaNonDual.Format = strF
Me!PinalNonDual.Format = strF
Me!YavapaiNonDual.Format = strF
Me!MaricopaNonDual.Format = strF
Case "P"
strF = "Percent;(Percent);" & strQ2 & ";" & strQ2
Me!PimaDual.Format = strF
Me!PinalDual.Format = strF
Me!YavapaiDual.Format = strF
Me!MaricopaDual.Format = strF
Me!PimaNonDual.Format = strF
Me!PinalNonDual.Format = strF
Me!YavapaiNonDual.Format = strF
Me!MaricopaNonDual.Format = strF
Case "N"
strF = "Standard;(Standard);" & strQ2 & ";" & strQ2
Me!PimaDual.Format = strF
Me!PinalDual.Format = strF
Me!YavapaiDual.Format = strF
Me!MaricopaDual.Format = strF
Me!PimaNonDual.Format = strF
Me!PinalNonDual.Format = strF
Me!YavapaiNonDual.Format = strF
Me!MaricopaNonDual.Format = strF
End Select
' Set number decimal places
Me!PimaDual.DecimalPlaces = Me!DP
Me!PinalDual.DecimalPlaces = Me!DP
Me!YavapaiDual.DecimalPlaces = Me!DP
Me!MaricopaDual.DecimalPlaces = Me!DP
Me!PimaNonDual.DecimalPlaces = Me!DP
Me!PinalNonDual.DecimalPlaces = Me!DP
Me!YavapaiNonDual.DecimalPlaces = Me!DP
Me!MaricopaNonDual.DecimalPlaces = Me!DP

 

[Marshall Barton]

hI've tried everyting and have just about given up. Have printed out help but
it does not say how to program this.
This is what I have tried, I'm getting really weird results like part of a
date/time field and sometimes a literal string in the result, If I code a
normal format it works ok but not with a custom format:

' Set format of number
Dim strQ1 As String, strQ2 As String, strF As String
strQ1 = """"
strQ2 = """" & """"
Select Case Me!Format
Case "C"
strF = "Currency;(Currency);" & strQ2 & ";" & strQ2
Me!PimaDual.Format = strF [snip]
Case "P"
strF = "Percent;(Percent);" & strQ2 & ";" & strQ2
Me!PimaDual.Format = strF [snip]
Case "N"
strF = "Standard;(Standard);" & strQ2 & ";" & strQ2
Me!PimaDual.Format = strF

[snip]


I don't think the named formats can be used that way. To
put together a custon format you need to use the format
codes specified in Help = Format Property following the link
to the type of value you want to format. Then skip down
past the predefined formats to see a list of the code
letters you can use to create a custom format.

Your attempt a a currency format looks like the builtin
format except you want a blank instead of zero. If so, then
it would be:
$#,##0.00;($#,##0.00);"";""
the percent format:
0.00%;-0.00%;"";""
and the number format with two decimal places:
0.00;-0.00;"";""