Return a Percentage

[alecmat]

Afternoon,



I know this may be an easy one...but I am having issues.

I have a "make table" query. Inside that query there is a simple expression that divides two fields by one another to arrive at a %. Example 5/100=5%. However, the data returned is not in a % format.

I know that I can go into table properties and change the fromat to "percentage", but that will only work for the current table that I have created. Once I re-run the "make table" query, I lose the change to the format, and my value again returns to a number, not a %..

Is there any way to always have a percentage returned without having to manually change the format each time that I run the query, or having to make a "delete" and "append query" ?

Thank You,
Alec

[pbaldy]

It's more efficient to delete/append records than to delete/recreate a table. I don't know a way to do what you're asking in any case, but I wouldn't be doing it.

[June7]

Would require VBA code manipulating TableDef collection to modify properties of table fields (assuming Format property can be addressed with code). Agree with Paul, run DELETE/UPDATE/INSERT sql actions instead of repeatedly deleting/creating table.

I do have procedure that creates table and modifies AllowZeroLength property of text type fields. Haven't used it in a long time. Excerpt:

Set cn = New ADODB.Connection
'connect to the backend database
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "Data\LabData.accdb'"
'create the test table
cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"
'set table link
Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
tdf.SourceTableName = Me.tbxTestNum
tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
CurrentDb.TableDefs.Append tdf
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.accdb")
While Not rs.EOF
    If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
        'create field in new table
        cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
                    rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
                    IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
    End If
    If rs!DataType = "Text" Then
        'change the AllowZeroLength default Yes to No
        db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
    End If
    rs.MoveNext
Wend

[alecmat]

June,

Thanks for the code. I will attempt to implement.

Have a good night