UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Format Text String In Code, Any Version    
 
   
as2solutions
post Oct 18 2019, 07:33 AM
Post#1



Posts: 113
Joined: 8-June 04



Hoping someone can assist.

I need to be able to format a text string on a form with the "after update" or "on change" event.

The field name is "MyNumber" and the format is fixed and will always be the same 00-000000000.

If the user enters 1234 in the filed, then I need to have it store 00-000001234.
If the user enters 777777 in the filed, then I need to have it store 00-000777777.
If the user enters 84123456789 in the filed, then I need to have it store 84-123456789.

Any assistance is appreciated!
Go to the top of the page
 
Jeff B.
post Oct 18 2019, 07:40 AM
Post#2


UtterAccess VIP
Posts: 10,323
Joined: 30-April 10
From: Pacific NorthWet


Please keep in mind the difference between how a value is stored and how it is displayed. Are you saying that you want to see the value formatted as "00-00..."? If so, you do not need to store it that way.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
as2solutions
post Oct 18 2019, 07:42 AM
Post#3



Posts: 113
Joined: 8-June 04



Yes.
Go to the top of the page
 
Daniel_Stokley
post Oct 18 2019, 08:18 AM
Post#4



Posts: 329
Joined: 22-December 14
From: Grand Junction, CO, USA


Give this a shot in the immediate window: Format(84123456789,"00-000000000")
Go to the top of the page
 
GroverParkGeorge
post Oct 18 2019, 09:07 AM
Post#5


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


Adding on to the previous comments.

Access allows us to DISPLAY values in more than one way, despite what STORED in a field.

With Dates, it is probably easier to see the difference, as illustrated in this YouTube video on formatting.

Here, where the difference is more subtle, it may not be so clear that FORMATTING is really only an element of an interface, not tied to the underlying data value.

As Jeff pointed out, there's no reason to store the formatted version of a value because you can always apply formatting when it's needed to show that value to the user.

Moreover, storing formatting as part of a field value can have unfortunate negative side effects if you need to query on that field.

All in all, just apply the formatting in the interface (forms and reports) and store only the actual value.
This post has been edited by GroverParkGeorge: Oct 18 2019, 11:33 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Oct 18 2019, 09:48 AM
Post#6


UtterAccess VIP
Posts: 11,178
Joined: 10-February 04
From: South Charleston, WV


The question is: is it acceptable to present the user with a mask where they must enter leading zeroes themselves or does the number have to be enterable like you say as "1234" and the extra zeroes put in for them?

--------------------
Robert Crouser
Go to the top of the page
 
as2solutions
post Oct 19 2019, 07:24 AM
Post#7



Posts: 113
Joined: 8-June 04



I was able to scrap together most of what I need by using a"pad" function on "after update" and it stores the padded result in the table, which is what I need.

When user keys 1234 in field MyNum, then the following code runs:

MyNum= Right("00000000000" & [MyNum], 11)

Resulting in 00000001234.

However, one final step is that I also need the dash to always be inserted after the 2nd value from the left so that the final result would store 00-00000001234.

Any further ideas ..... I'm so close.
Go to the top of the page
 
arnelgp
post Oct 19 2019, 07:54 AM
Post#8



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


see Post #4, no need to pad the text.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Oct 19 2019, 08:54 AM
Post#9


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


Let's review a bit.

There is no need to STORE the dash in the value.

And there is no need to PAD the value.

All you need to do is DISPLAY the actual value stored, using the FORMAT() function Arnelgp provided when you need to SHOW the value in a report, or on a form.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Oct 19 2019, 11:06 AM
Post#10


UtterAccess VIP
Posts: 11,178
Joined: 10-February 04
From: South Charleston, WV


Post #4 works.

When I saw that I thought it would left-justify. But it does right-justify.

My idea was to present the user with a mask. I think that's more advanced, more user-friendly.

But if you don't want a mask, go with Format:

If Len(Me.controlname) > 11 Then
MsgBox "You have exceeded the maximum 11 characters"
Exit Sub
End If
Me.controlname = Format(Me.controlname, "00-000000000")


--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Oct 19 2019, 11:16 AM
Post#11


UtterAccess VIP
Posts: 11,178
Joined: 10-February 04
From: South Charleston, WV


As some have said, the preferred way is not to store the formatted version. But if you store the formatted version it will save you the trouble of having to format it every time you print it out. The unformatted version saves the space of one hyphen. (Not much, obviously.) As far as saving the leading zeroes, you won't save anything if the space is allocated on the disk anyway. Space on modern disks is way cheap.

To answer your last question you would concatenate a left function, a hyphen, and a right funtion.

--------------------
Robert Crouser
Go to the top of the page
 
as2solutions
post Oct 19 2019, 02:32 PM
Post#12



Posts: 113
Joined: 8-June 04



Thanks for all of the help!
Go to the top of the page
 
projecttoday
post Oct 19 2019, 03:33 PM
Post#13


UtterAccess VIP
Posts: 11,178
Joined: 10-February 04
From: South Charleston, WV


thumbup.gif

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2019 - 07:51 AM