Solved Format() String thru VBA

[Ksabai]

Iam trying to change the Format of a Textbox Pragmatically. I need the Text Box to Show "CHN\GO\ "000 but when i use s BackSlash in the Format() i get the Runtime Error 13, Type Mismatch. iam using Format(Me.Text, "CHN\GO" \ 0#)

 

[June7]

I think you meant "programmatically".

That expression makes no sense. Show sample data and what you want it to look like.

Format() function has nothing to do with textbox Format property. You are mixing syntax from both.

 

[Ksabai]

i tried in VBA Me.Text.Format = "CHN\GO" 000. This is what was typed in the Form Control

 

[The_Doc_Man]

The zeros would have to be inside the quotes to be effective, among other things. However, based on your reported error, this will probably cause problems no matter which way you go. I will try to explain.

If you are supplying a format that will contain text, you would use one format sequence. If you are showing digits, you would use a different format sequence.

Format a number or currency field - Microsoft Support

Custom formats for numbers and currency fields in Access can be useful when you want the data displayed in a specific way.

support.office.com

Format a text field - Microsoft Support

Custom formats for text fields in Access can be useful when you want the data displayed in a specific way.

support.microsoft.com

But you are showing two different types of things in the format and further, the implication is that this is a bound field. If the underlying bound field is numeric, forcing it to contain text will lead to a type mismatch error because you cannot store text in a numeric field. Even if you could get it to display that way, the moment you tried to store the record it would fail because the underlying numeric field can't take that.

This "CHN\GO" sounds vaguely like a constant prefix. According to the normal ways of doing this in Access, you never store the constants in the field. Instead, you simply display them in a label when they are needed, and only store the numeric part in the underlying field.

So... tell us WHY you want this and perhaps we can find a way to get you closer to what you want.

 

[strive4peace]

hi Ksabai,

if you want a BackSlash to literally show in the formatted string, in the format code, either use 2 of them, \\, or surround it with quote marks, "\"

 

[strive4peace]

adding on, I see it already is in a quoted string so the problem is Me.Text

Text is a reserved word! It would be best if you'd choose a difference name

 

[Ksabai]

Finally Wrote =IIf(IsNull([Text])=False,"CHN/GO/" & Format([Text],"000") in the Expression and Found the Solution.

 

[Ksabai]

Thanks Everyone

 

[moke123]

Glad you got it sorted however dont ignore Crystals advice in post # 6. You may have a working solution now but it may cause unexpected problems going forward and correcting it then may get complicated.

Good luck with your project!

 

[strive4peace]

thanks, moke

Ksabai, the reason you were able to get it to work is that you surrounded Text with brackets, [Text]. That is because "Text" is a reserved word and shouldn't be used as a name! Reserved words must be surrounded with brackets. This one will continue to bite you. Best would be to change it sooner than later. If you can't change the name of the field, at least change the name of the control.

Here is a wonderful link to lookup words that shouldn't be used. Allen also has a Database Issue Checker Utility you can download and run.

Problem names and reserved words in Access (by Allen Browne)

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

 

[Ksabai]

Ok Will Look at them today, thanks for your guide. will let u know how it works.

 

[strive4peace]

you're welcome, Ksabai ~ happy to help