dbAutoIncrField. Cause of Problems??? 1

[RamziSaab]

I have a code that tries to set the format to "standard" and decimal places to 0 for each field in my tables. howerve i can only seem to get the format to work not the decimalplaces...wrote a previous thread about this. Now i am wondering if the following could be the cause of the problem found it in the help file!

DataUpdatable Property


Returns a value that indicates whether the data in the field represented by a Field object is updatable.

Return Values

The return value is a Boolean data type that returns True if the data in the field is updatable.

Remarks

Use this property to determine whether you can change the Value property setting of a Field object. This property is always False on a Field object whose Attributes property is dbAutoIncrField.

You can use the DataUpdatable property on Field objects that are appended to the Fields collection of QueryDef, Recordset, and Relation objects, but not the Fields collection of Index or TableDef objects.

The decimalplaces field is set to Auto when i import the file from excel

Thanks for any help

 

[RamziSaab]

Actaulyl i am pretty sure this is the cause because if i manually change these tables decimalplaces field i can use my code to change them to wahtever i want...

therefore i guess my question is how to i get around this...

 

[BillPower]

Dim dbs As Database, fld As DAO.Field, tdf As DAO.TableDef
Hi RamziSaab,

I'm not exactly sure what you're after, but the code below will format a field named "ANumberField" to 0 decimal places in a table called "Table1".

Dim prp As Property, strTableName As String
On Error Resume Next
strTableName = "Table1"
Set dbs = CurrentDb()
Set tdf = dbs.TableDefs(strTableName)
Set fld = tdf.Fields("ANumberField&quot
fld.Properties.Delete "Format"
fld.Properties.Delete "DecimalPlaces"
Set prp = fld.CreateProperty("Format", dbText, "Fixed&quot
fld.Properties.Append prp
Set prp = fld.CreateProperty("DecimalPlaces", dbByte, 0)
fld.Properties.Append prp
Set dbs = Nothing

Note. You must use the On Error resume Next statement, because if the properties don't exist when trying to delete them an error will be generated.

Bill

 

[RamziSaab]

thanks will give it a go...but can u tell me how come when i export the file to excel i get two decimal places again...

All i want to do it have no decimals and a comma in (e.g 1,000) why is this so hard to do with access...

 

[RamziSaab]

Seriously i have been trying to find a code that worked for ages and all of them would change all the properties except the decimalplaces...anyway thanks..

Now about the excel problem, it there a way to make a code that will atleast format tthe exported file to zero deciaml places in exel and put the decimal... i.e. is there a way to tell access to do stuff in excel?? and how

 

[BillPower]

The code I gave you should be sufficent to export to excel with no decimal places.

Bill

 

[RamziSaab]

Well depending which export command i use..

if i use: Docmd.output ....
it will give decimal places and commas:

if i use docmd.transferspreadsheet....
it will give me no decimal and no commas, kind of need the commas to be there though

 

[RamziSaab]

its okay figured it out thanks a lot for your help with the code

did this:
Set objExcel = objExcelApp.Workbooks.Open(strDocName)
objExcelApp.Cells.Select
objExcelApp.Cells.EntireColumn.AutoFit
objExcelApp.Selection.NumberFormat = "#,##0"
objExcelApp.Range("A1&quot.Select

 

[BillPower]

Hi RamziSaab,

I was working on this for you. Might as well post it.

Dim xlobj As Object, strExcelFile As String
strExcelFile = "c:\my documents\Data.xls"
Set xlobj = CreateObject("excel.application&quot
xlobj.Application.Visible = True
With xlobj
.Workbooks.Open FileName:=strExcelFile
.Worksheets("Sheet1&quot.Select
.ActiveSheet.Columns("A:A&quot.NumberFormat = "#,##0"
.ActiveSheet.Columns("A:A&quot.ColumnWidth = 12.71
.ActiveSheet.Columns("B:B&quot.NumberFormat = "#,##0.00"
.ActiveSheet.Columns("B:B&quot.ColumnWidth = 11.29
.ActiveSheet.Columns("C:C&quot.NumberFormat = "d-mmm-yy"
.ActiveSheet.Columns("C:C&quot.ColumnWidth = 10.71
End With
Set xlobj = Nothing

Thanks for the star, much appreciated.

Bill

 

[janeatwork]

Hi BillPower again,

Thanx, thanx, thanx for sharing your knowledge. This is another so helful to me answer.

Thanx

Jane