Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Chaning many field types at once from long integer to decimal

Posted on 2011-05-09
12
Medium Priority
?
287 Views
Last Modified: 2012-05-11
Is it possible to change multiple field types in a table at the same time?  Right now I made the mistake to store an input mask series of fields 999.9;0;# as long integer which truncates the decimal values.  Please let me know if I can fix this other than the cumbersome 1 by 1 way.

Thanks,
Bevo
0
Comment
Question by:Bevos
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35720483
Bevos,

One could write VBA code to do it, but unless you have scores of fields to change, I suspect that in the time required to write and test the code you could accomplish the change manaually.

:)

Patrick
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 856 total points
ID: 35720577
If you know the number of the field in the Fields() collection of the tabledef object, you could cycle through the fields and change their type from dbLong (4) to dbDouble(7).  If you need additional help I need to know the structure of the table and which of the fields have to change.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35720611
Bevos:  You realize that all the data has to be re-entered because the decimal portion has been stripped.  Changing the datatype will not recover that portion.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:Bevos
ID: 35720921
Oh there isn't any data yet, just tables.  Why would I use dbDouble rather than Decimal?

Bevo
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35722275
Your call.  Double uses 8 bytes per number - Decimal uses 12.  Press Alt+F11 to get to the VB Editor window and in Help type both Decimal datatype and Double datatype -  have a good read - normally people use Double unless you need over 15 decimal places.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 1144 total points
ID: 35723512
In mocking something up on the weekend, I tried to use decimal for currency.
On the forms, it just wouldn't display the fractions.  It rounded them down, even with it supposed to save 4 decimal places.
Double worked perfectly in Access 2010

YMMV

How many fields and how many tables Bevos?
Are ALL the fields with input mask 999.9;0;# and long integer bad?

If they all are they can be looped and changed.
Haul out the code you got from us for putting field captions in a listbox.
Input mask and Type are properties of the field.

Give it a whack, and if you can't make it go, post your efforts up, and we'll refine them
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 1144 total points
ID: 35723742
'Pass in a tablename

Public Function ChangeFieldDataTypes(TableName As String)
On Error GoTo myerror
Dim db As Database
Dim rs As Recordset
Dim myfield As Field

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)


For Each myfield In rs.Fields
    If myfield.Type = dbLong And myfield.Properties("InputMask").value = "999.9;0;#" Then
        MsgBox myfield.Type
        MsgBox myfield.Name & " " & myfield.Properties("InputMask").value
        'myfield.Type = dbDouble
        'myfield.Properties("InputMask").value = ""
        Next x
'End If
   
Next myfield


Exit Function

myerror:
If Err.Number = 3270 Then 'no existing input mask
    Resume Next
End If

End Function
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 856 total points
ID: 35723977
OK Nick, I have to ask - why are you doing this with a Recordset object rather than the TableDef object?  
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 1144 total points
ID: 35724089
The simple answer is:  I know how, and it works.

The more complex answer is they are equivalent.
This site uses the tabledef method of snagging the fields collection
http://social.msdn.microsoft.com/Forums/en/isvvba/thread/088bc5f9-83b8-402a-9992-3894dc6d950c
and also pointed out the exact synatx I needed for the input mask

The most complex answer is that I had already created a stub db for the author on an earlier question that snagged Caption properties.
So this code is an easy adaptation of that code.

The real answer <grin> is since I rarely do DDE in code, the tabledefs collection is something I've only used twice in 7 years.
I know the recordset stuff will work, and I won't have to debug the code by working with something unfamilar.

There isn't a lot of difference between
CurrentDb.TableDefs("Table1").Fields("FirstName").Properties(27).Name

and

set rs = currentdb.opensrecordset(tablename0
rs.Fields("FirstName").Properties(27).Name
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 856 total points
ID: 35730034
Thanks for the 'whole' answer.  For me, if I want to get at the data using code, I'll go recordset.  If I want to modify a table using code, I'll go tabledef.  

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 1144 total points
ID: 35730244
If I have a boatload of DDE to do--especially creation--then tabledef is the way to go.
You can't, after all, pull up a recordset for a table that doesn't exist yet.
But it isn't something I have to do very often
Coding up table creation would probably be more time consuming than just doing it.
These days, my tables are all on SSEE 2005 anyway.

There are the two thouroughly equivalent methods of getting at the fields collection of an existing table.
TableDef or Recordset, either one will get you access to the collection.

In a previous question, the author wanted a listbox populated with all the captions from the fields in a table.
This was provided.  The author is very new to Access.
Creating similar code that reinforces how to work with the fields collection was a good thing.

Areas where I have used similar code to good effect is in creating Excel exports.
You pull up the recordset.
You loop through the fields collection putting each field name in a cell in Row A
You then have the recordset already open to knock into the sheet
oSheet.Range("A2").CopyFromRecordset rs
In that scenario, using the TableDef is redundant because you can get what you need from a recordset you're going to open anyway.

That's the end I've come from when using the fields collection for its properties.
0
 

Author Closing Comment

by:Bevos
ID: 35831872
Thank you so much for all of the advice everyone.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question