Number datatype format to 'Standard'

[JA]

I need a routine to enumerate through all tables and if the field type is
'Number', I would like to change the format to 'Standard'.

 

[John W. Vinson]

I need a routine to enumerate through all tables and if the field type is
'Number', I would like to change the format to 'Standard'.

Why?

Table datasheets aren't appropriate for viewing or printing data, and you can
set the format of a textbox in a Form or Report to default to Standard (if
indeed it doesn't do so already).

However if you want...

Dim tdf As DAO.Tabledef
Dim fld As DAO.Field
Dim db As DAO.Database
Set db = CurrentDb
For Each tdf In db.Tabledefs
' exclude system tables
If Left(tdf.Name, 4) <> "MSYS" Then
For Each fld In tdf.Fields
Select Case fld.Type
Case dbInteger, dbLong, dbSingle, dbDouble, dbDecimal
fld.Format = "Standard"
Case Else
' do nothing
End Select
End If
Next tdf

Untested air code, back up your database first of course!!!

 

[pietlinden]

I need a routine to enumerate through all tables and if the field type is
'Number', I would like to change the format to 'Standard'.

What is "standard"? Been using Access since 2.0 or so and have never
heard of it. Example?

 

[fredg]

What is "standard"? Been using Access since 2.0 or so and have never
heard of it. Example?

Taken directly from a control's Format property drop-down:
3,456.78

From Format Help
Standard Use the thousand separator; follow the settings specified
in Regional Settings in Windows Control Panel for negative amounts,
decimal symbols, and decimal places.

 

[DaveT]

Love this air code stuff
My air code would add one line:

End Select

Next fld

End If

--
Dave Thompson
Allen, TX
US

 

[John W. Vinson]

Love this air code stuff
My air code would add one line:


Next fld

--

That would run a LOT faster.... thanks Dave!

(JA: my untested code had an infinite loop that would never complete)

 

[DaveT]

My specialty is DO loops w/o Movenext ....

 

[Tony Toews [MVP]]

My specialty is DO loops w/o Movenext ....

Been there, done that, got the Tshirt.

Tony (why is Access taking so long?)
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/