Formatting Problem 2

[TBOB]

I’m trying to format the value in a text field of a form. The underlying table has the field as text. Let’s say the input is 12345. I want to the resultant format result to look like this, ABCD-E-12345. I ‘m not using the format in the properties window of the text box because this is eventually going to be formatted in different ways based on the users choice. I’m doing this in VBA with a case statement. That’s not the problem. The task of formatting the data is stumping me. I’ve tried using the following:

mystr = Format("18989", "ABCD-E-#####") and get 1212/27/1951CD-E-#####

mystr = Format(18989, " ABCD-E-" & "#####") and I get 1212/27/1951CD-E-#####

mystr = Format("18989", " ABCD-E-" & "#####") and get 1212/27/1951CD-E-#####

I’ve tried a lot of combinations of different things, and always get the above type results.

Tried this approach:

Me.txtTEXTBOX.Format('ABCD-E-#####') & Me.txtTEXTBOX.Format ("ABCD-E-#####") gets compile errors.

Me.txtTEXTBOX.Format = ("ABCD-E-#####") gives me “”AB”cd”-E-#####””.

Me.txtTEXTBOX.Format = ("'ABCD-E-'#####") gives me “AB8/10/192410-E-'#####"

I would appreciate any insight any one could give me into this.

Tanks,

Tony

 

[Remou]

Hi
Maybe:
Format("18989", "\A\B\C\D-\E-#####")

 

[TBOB]

Remou,

That did it. I'm plenty happy about that, but could you tell me why?

Thanks,

Tony

 

[Remou]

It is to do with the meaning of some of the letters. For example:
c Same as the General Date predefined format.
d Day of the month in one or two numeric digits, as needed (1 to 31).

Then this:
\ Display the next character as a literal character. You can also display literal characters by placing quotation marks around them.

 

[TBOB]

Thank you.

Tony