How to set property 'Notation' of an MS Access field from within Delphi?


To update the MS Access database of my application I use following statement.
      QSQLDB.SQL.Text:='ALTER TABLE FacturenUIT ADD COLUMN IsCorrect   Bit   DEFAULT True';
This will add field IsCorrect to the database table.
I also can set the default value for the field by adding 'Default True' as you can see above.

For boolean values I can set the notation (F6) in MS Access (like: 'Yes/No', 'True/False' or 'On/Off')
How can I set the notation from within Delphi?

Stef MerlijnDeveloperAsked:
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
The Format property is a property of the Field and can't be set with straight SQL. Matter of fact, if you try to set this property and is doesn't exist already, then you'll receive an error.

I'm not familiar with Delphi, but in VB we'd do this after creating the field:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = DAO.OpenDatabase("Full path to db")
Set tdf = dbs.TableDefs("NameOfYourTable")
Set fld = tdf.Fields("NameOfYourField")

fld.Fomat = "xxxx"

Of course, the last line may fail if the Format property doesn't exist. You can handle this various ways, but the easiest may be to use the two function below, which will create the property if it doesn't already exist:

SetPropertyDAO fld, "Format", dbText, "Currency", strErrMsg

So you'd change the code sample above to this:

Set dbs = DAO.OpenDatabase("Full path to db")
Set tdf = dbs.TableDefs("NameOfYourTable")
Set fld = tdf.Fields("NameOfYourField")

SetPropertyDAO fld, "Format", dbText, "Currency", strErrMsg

Of course, if you're going to do this, it's just as easy to add the tables and fields with DAO as it is with SQL ... if you'd like to do that, take a look at this website:

(and it's even Delphi-specific ;)

'///////////// CODE BELOW

'/these two function courtesy of Allen Browne
Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
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
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
End If
SetPropertyDAO = True

Exit Function

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

I don't know about Delphi, but in SQL Server, no matter what notation I use in access, the SQL Bit data type only stores 1 or 0. If I wanted to store "On/Off", "True/False", "Yes/No" I would have to create a varchar field to hold that value. Don't know if this helps you any, but I thought I would give it a try.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're asking how to set the Datatype for a boolean field, use "Yes/No" with your ALTER TABLE statement ...
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Stef MerlijnDeveloperAuthor Commented:
Basically I don't mind how to update the datafield, as long as it get's updated.
So it doesn't have to be done with a SQL statement.
Stef MerlijnDeveloperAuthor Commented:
How would that statement look like (please keep in mind that I use a MS Access database).
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try this:


You can also use LOGICAL or BIT for the Boolean Datatype.

Here's a link to Jet DDL language:
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, that should be ADD COLUMN ..
Stef MerlijnDeveloperAuthor Commented:
Hi, first of all a small correction.
In the Dutch version of MS Access there is a fieldproperty called 'Notatie'.  This propertyname I translated into 'Notation', but it appears to be called 'Format' in the english version of MS Access.

When you open a table in MS Access (in designview) and add a boolean field, it will have as it's property:
Fieldname = 'Test1'
Fieldtype = 'Yes/No'
Format = 'Yes/No'      (= automatically filled)
Format can be changed into Yes/No, True/False and On/Off

The statement below is not adding the Format-property of the boolean field.
Fieldname = 'Test1'
Fieldtype = 'Yes/No'
Format = <empty>

So: How can I access the fieldproperty Format?
Stef MerlijnDeveloperAuthor Commented:
If I would import the type library "Microsoft DAO 3.6 Object Library (version 5.0)"
Could I do it somehow with that? And how would the code for Delphi look like?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, you would somehow need to make use of the DAO library. As I mentioned earlier, I'm not at all familiar with Delphi so I can't advise you of how to do that, nor could I advise you of how to make the necessary code changes, but the code at the link I provided shows how to create a table with Delphi; it appears from that code that once you actually get the database open, you would use code as described in the lower section of my last comment to add your field and/or table.
Stef MerlijnDeveloperAuthor Commented:
Thank you LSMConsulting.
The site that you mentioned describes the way it would work with their DAO-componentset, which differs from native DAO from the type library.

I will look further for DAO <-> Delphi information.
All Courses

From novice to tech pro — start learning today.