Solved Format() String thru VBA (1 Viewer)

Ksabai

Registered User.
Local time
Today, 13:42
Joined
Jul 31, 2017
Messages
104
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

AWF VIP
Local time
Today, 12:42
Joined
Mar 9, 2014
Messages
2,954
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.
 
Last edited:

Ksabai

Registered User.
Local time
Today, 13:42
Joined
Jul 31, 2017
Messages
104
i tried in VBA Me.Text.Format = "CHN\GO" 000. This is what was typed in the Form Control
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 15:42
Joined
Feb 28, 2001
Messages
16,796
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.



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

AWF VIP
Local time
Today, 15:42
Joined
Apr 3, 2020
Messages
482
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

AWF VIP
Local time
Today, 15:42
Joined
Apr 3, 2020
Messages
482
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

Registered User.
Local time
Today, 13:42
Joined
Jul 31, 2017
Messages
104
Finally Wrote =IIf(IsNull([Text])=False,"CHN/GO/" & Format([Text],"000") in the Expression and Found the Solution.
 

moke123

AWF VIP
Local time
Today, 16:42
Joined
Jan 11, 2013
Messages
1,398
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

AWF VIP
Local time
Today, 15:42
Joined
Apr 3, 2020
Messages
482
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)
 
Last edited:

Ksabai

Registered User.
Local time
Today, 13:42
Joined
Jul 31, 2017
Messages
104
Ok Will Look at them today, thanks for your guide. will let u know how it works.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom