Determining the Data Type of a Field

[Mr. JYC]

Hello,

How can one determine the data type of a field programmatically?

 

[Allen Browne]

In what context?

If this is in a form, examine the ControlSource of the text box to get the
field name. Then examine:
Me.Recordset.Fields("SomeFieldName").Type

The result will be one of the dbDataTypeEnum constants. To convert it to
English text, see the FieldTypeName() function here:
http://allenbrowne.com/func-06.html

If you need to read the data type of the field directly from the table, the
TableInfo() function in the link above will do that.

 

[Mr. JYC]

Thank you for your response Allen. The information will come in handy soon.
I was confused. I guess that is why the question did not make sense. I
actually wanted to find out the data type of a text box control. Is there a
way to do this?

--
Thank you for your help!
JYC

In what context?

If this is in a form, examine the ControlSource of the text box to get the
field name. Then examine:
Me.Recordset.Fields("SomeFieldName").Type

The result will be one of the dbDataTypeEnum constants. To convert it to
English text, see the FieldTypeName() function here:
http://allenbrowne.com/func-06.html

If you need to read the data type of the field directly from the table, the
TableInfo() function in the link above will do that.

 

[Allen Browne]

A control doesn't have a data type.

If it is bound to a field, the field determines the data type.

If is is unbound, you can use the Format property to give Access the clue
about what data type you intend it to be.

For an example, see:
http://allenbrowne.com/AppFindAsUTypeCode.html
The code loops through the controls in a form, and itentifies the text boxes
and combos. It skips the unbound controls and those bound to an expression,
and identifies the name of the field the control is bound to. It than
examines the Type of this field in the form's RecordsetClone, with this
line:
Select Case rs(strControlSource).Type

Ultimately the example loads the information into the columns of an unbound
combo you can use to filter the form, with the data type in the last
(hidden) column. That's going further than you need, but that's the process.

 

[Mr. JYC]

Come to think of it, how do you programatically determine the data type of a
variable that may contain any one of the data types supported in Access?
--
Thank you for your help!
JYC

In what context?

If this is in a form, examine the ControlSource of the text box to get the
field name. Then examine:
Me.Recordset.Fields("SomeFieldName").Type

The result will be one of the dbDataTypeEnum constants. To convert it to
English text, see the FieldTypeName() function here:
http://allenbrowne.com/func-06.html

If you need to read the data type of the field directly from the table, the
TableInfo() function in the link above will do that.

 

[Allen Browne]

Come to think of it, how do you programatically determine the data type of
a
variable that may contain any one of the data types supported in Access?

VarType()

 

[Mr. JYC]

Thank you Allen!