Text as a number with commas (1 Viewer)

Eljefegeneo

Still trying to learn
Local time
Today, 12:12
Joined
Jan 10, 2011
Messages
904
I have several fields that were set up as text fields since the numbers would never be used for calculation. But now my users want to see 100,000 instead of 100000. I have looked everywhere for an answer but do not know if it is possible without retyping the data. The text or rather the numbers that are text can be from anywhere of 1 to 1,000,000 plus. They are just used to show population figures.

Any suggestions on how to do this with formatting?
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:12
Joined
Jan 23, 2006
Messages
15,364
Not elegant, but try this ith various values of x
Code:
Sub test1()
Dim x As String
x = "1000000"
Debug.Print Mid(Format(x, "currency"), 2)
End Sub
1,000,00.00

You could add a line to remove the decimal and trailing 0s.
Code:
Sub test1()
Dim x As String, y As String
x = "1000000"
y = Mid(Format(x, "currency"), 2)
Debug.Print Left(y, Len(y) - 3)
End Sub
1,000,000
 
Last edited:

Eljefegeneo

Still trying to learn
Local time
Today, 12:12
Joined
Jan 10, 2011
Messages
904
I tried the code and it works in the immediate window nicely, but how do I use it in the form? I tried to set x as the text field, called the sub and then set the text field as y but nothing happens. I tried this on the on focus event since there was data already in the field.

Private Sub NumberText_GotFocus()
Dim x As String
Dim y As String
x = Me.NumberText
Call test1
Me.NumberText = y
End Sub

How can I use this function?
 

Eljefegeneo

Still trying to learn
Local time
Today, 12:12
Joined
Jan 10, 2011
Messages
904
I spoke too soon. I put this code in the OnFocus event of the control:

Private Sub NumberText_GotFocus()
Dim x As String
Dim y As String
Me.NumberText = y
y = Mid(Format(x, "currency"), 2)
Me.NumberText = Left(y, Len(y) - 3)

End Sub

And, so far it seems to work OK.

Is there a better event to use this. And would it be possible to use it in a query so I can update all the field at once?
 

MarkK

bit cruncher
Local time
Today, 12:12
Joined
Mar 17, 2004
Messages
8,178
You don't need to do this in code. You can just set the Format property of the displaying Textbox to "#,##0". Textbox.Format is the first property on the Format tab of the control's property sheet in design view.

This the same as running the Format() function in the immediate pane like . . .
Code:
? Format("100000", "#,##0")

Hope this helps,
 

Eljefegeneo

Still trying to learn
Local time
Today, 12:12
Joined
Jan 10, 2011
Messages
904
Markk:
I tried that but it doesn't seem to work. I put in both the table and form field format. First I put the code in with the quotation marks and then without. Similar result. So I added an new text field and tried it again, same result.
 

Eljefegeneo

Still trying to learn
Local time
Today, 12:12
Joined
Jan 10, 2011
Messages
904
But this works in the OnFocus event:
Me.NNN = Format(Me.NNN, "#,##0")
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:12
Joined
Jan 20, 2009
Messages
12,849
I have several fields that were set up as text fields since the numbers would never be used for calculation.


The Format property of a control or field needs numbers or dates. It won't recognise a string. The Format function will implicitly cast to a number which is why it works.

Bite the bullet and change the field to a Long Integer which is what it should have been in the first place. Population is a number and should be stored as a number.

(Use V_tools Total Deep Search to find places where you need to drop the string delimiter quotes in code and queries.)

The notion that numbers should be stored as text if they are not calculated is one of the most misleading suggestions ever made by Microsoft.

They meant it for fields like phone numbers and serial numbers where calculation is meaningless rather than simply not having calculations applied.

Microsoft's advice is plain wrong anyway. Unfortunately it is parrotted widely online by people who have no comprehension of the issues.

If something can be stored as an Integer (or Long Integer) then it should be stored as such regardless of whether calculations wouold be meaningless. Numbers are much more efficient than text to store and index.

I got banned for saying this at another site because it conflicted with the doctrine of their mods.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:12
Joined
Sep 12, 2006
Messages
15,614
Wow. A Ban is a bit harsh. Permanent?

I agree. I would only store a number as text if it wasn't really a number - as G says, a telephone number or a serial number. For me, a good test for text, is if it might have leading zeroes.
 

Eljefegeneo

Still trying to learn
Local time
Today, 12:12
Joined
Jan 10, 2011
Messages
904
Yes, I have learned my lesson. I learned one a while back about multi-choice fields. Maybe someone should post a list of dos and don'ts regarding MS advice and the real world.

Thanks to all. Will change the fields to a long integer.
 

Users who are viewing this thread

Top Bottom