Altering a column to REAL and setting the format + the decimal in SQL.

[John Marble]

I looked around for a way to change the format and the decimal setting
of a REAL type variable, but I can't seem to be able to find the
synthax anywhere.

Here is an exemple of what I am doing:

ALTER TABLE MAIN ALTER COLUMN sup_visee REAL;

It works, it changes the column "sup_visee" which was a String into a
REAL. What I want to do too is to be able to set the REAL format to
"standard" and the decimals to 4. Anyone have an idea of what the
synthax could be in SQL?

[Allen Browne]

You cannot set properties such as Format using a DDL statement.

These properties can only be set using DAO, the object library that was
designed for Access. The functions below let you set those properties. For
example,
Call SetPropertyDAO(dbEngine(0)(0).TableDefs("Table1"). Fields("Field1"),
_
"Format", dbText, "Currency")

Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As
Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Marble" <fr******@gmail.com> wrote in message
news:28**************************@posting.google.c om...

I looked around for a way to change the format and the decimal setting
of a REAL type variable, but I can't seem to be able to find the
synthax anywhere.

Here is an exemple of what I am doing:

ALTER TABLE MAIN ALTER COLUMN sup_visee REAL;

It works, it changes the column "sup_visee" which was a String into a
REAL. What I want to do too is to be able to set the REAL format to
"standard" and the decimals to 4. Anyone have an idea of what the
synthax could be in SQL?