Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 16th, 2003, 06:25 PM
Authorized User
 
Join Date: Oct 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Alter FIeld Size And Scale

How do I modify the following SQL statement to not only change the field type to number but to also change the field size to decimal and the scale to 2?

ALTER TABLE Table1 ALTER COLUMN Field1 Number
 
Old December 16th, 2003, 07:41 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What do you mean by "scale" - do you mean the number of decimal places? If so, then that's not really an SQL thing.

The statement you've got there turns the field into a Double - in other words, it can store up to 15 decimal places. The only other numeric fields that can handle decimal places are Single (7 decimal places) and Decimal (28 decimal places).
Regardless of how many decimal places show up, it will still store down to that precision.
So it's not actually a SQL thing, rather a VBA thing.
Before I go worrying about whether to write some code that will change it - is this what you're talking about - i.e. the number of decimal places displayed?
Steven

I am a loud man with a very large hat. This means I am in charge
 
Old December 16th, 2003, 07:50 PM
Authorized User
 
Join Date: Oct 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I am talking about the number of decimal places. The SQL statement as it is sets the decimal places to zero. I have to go into the database in Access 2000 where the data type is correctly now "Number" but I have to change the field type to "Decimal" and the scale to "2" in order for the field to allow decimal places.
 
Old December 17th, 2003, 06:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm guessing some settings you have must be different to me, as I can't replicate the problem.
I created a table called Table1, with a field called Field1 (type=Text), and ran that statement, which turned the field into a double with Auto decimal places.
But regardless..
Try this:
Code:
Function thins()
Dim tdf As TableDef
Dim prp
Dim fld
With CurrentDb
For Each tdf In .TableDefs
If tdf.Name = "Table1" Then

For Each fld In tdf.Fields
    If fld.Name = "Field1" Then
       fld.Properties("DecimalPlaces") = 2
    End If
Next fld
End If
Next tdf

End With
End Function
Steven

I am a loud man with a very large hat. This means I am in charge





Similar Threads
Thread Thread Starter Forum Replies Last Post
Uplad txt field of size greater than 12 mb rapraj PHP Databases 3 October 28th, 2007 09:36 AM
How create a DBF db and set size of a field Dormarth VB Databases Basics 2 May 30th, 2006 06:06 AM
maximum size of HTML form field crmpicco HTML Code Clinic 4 April 24th, 2006 05:14 AM
Crystal Report in >net environment and field size janet_35 Crystal Reports 3 February 17th, 2004 06:19 AM
Getting Field Size in DataSet kasie ADO.NET 1 June 30th, 2003 02:39 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.