Finding the Data Type in a control (1 Viewer)

Robert.Finch

New member
Local time
Today, 22:19
Joined
Dec 17, 2008
Messages
7
Hi

I am trying to find the data type in the current control, is there something like

screen.activecontrol.datatype ? Does anybody have a function to recover this attribute?

I need to test whether the current control is a number, text, date/time ,yes/no, memo or something else.

Thanks
 

Trevor G

Registered User.
Local time
Today, 22:19
Joined
Oct 1, 2009
Messages
2,341
Take a look at this code, it will look at a table and based on the datatype I have been looking for it will print the list to the debug window, so you would need to add a Module in the VBA screen and also display the immediate window

Sub findAttribute()
Dim intNumberofFields As Integer, intFieldType As Integer, strTypeName As String

Dim fld As Field, intCounter As Integer, strFieldName As String

intNumberofFields = CurrentDb.TableDefs("tblEmployee").Fields.Count

For intCounter = 0 To intNumberofFields - 1
strFieldName = CurrentDb.TableDefs("tblEmployee").Fields(intCounter).Name
intFieldType = CurrentDb.TableDefs("tblEmployee").Fields(intCounter).Type
Select Case intFieldType
Case 2 'Byte
strTypeName = "Byte"
Case 3 'Integer
strTypeName = "Integer"
Case 4 'Long
strTypeName = "Long"
Case 6 'Single
strTypeName = "Single"
Case 7 'Double
strTypeName = "Double"
Case Else 'Not a Number
strTypeName = "N/A"
End Select
Debug.Print Format(intCounter + 1, "00") & ") " & strFieldName & " - " & strTypeName
Next intCounter
End Sub
 

Robert.Finch

New member
Local time
Today, 22:19
Joined
Dec 17, 2008
Messages
7
Trevor

Thanks for the ideas but not really what I want. I want to glean the datatype info from any control on any form, this is part of a bigger project.

I don't need to differentiate between numeric types, just number, text, date/time and yes/no.

Thanks


Take a look at this code, it will look at a table and based on the datatype I have been looking for it will print the list to the debug window, so you would need to add a Module in the VBA screen and also display the immediate window
 

Trevor G

Registered User.
Local time
Today, 22:19
Joined
Oct 1, 2009
Messages
2,341
Try this out then, add a command button to one of the forms then add this code, it will give you the control name and controlType information being printed to the debug window, you should be able to take this further to fullfil your task.

Also there is a Database Documenter feature in Access this will produce a report based on the forms you select, it gives way to much detail (or I believe so) that you may require.

Private Sub cmdList_Click()
Dim ctl As Control
For Each ctl In Me.Controls
Debug.Print ctl.Name & ":" & ctl.ControlType
Next ctl
End Sub

I think this will be more appropriate though

Dim ctl As Control
For Each ctl In Me.Controls
Debug.Print ctl.Name & ":" & TypeName(ctl)
Next ctl
 

Robert.Finch

New member
Local time
Today, 22:19
Joined
Dec 17, 2008
Messages
7
Trevor

Thanks for the guide but it is not giving the results I need, it is listing the control type rather than the data type of the underlying field. So for most controls it is listing Textbox where I need date/time, number, text etc.



Try this out then, add a command button to one of the forms then add this code, it will give you the control name and controlType information being printed to the debug window, you should be able to take this further to fullfil your task.

Also there is a Database Documenter feature in Access this will produce a report based on the forms you select, it gives way to much detail (or I believe so) that you may require.



I think this will be more appropriate though
 

Trevor G

Registered User.
Local time
Today, 22:19
Joined
Oct 1, 2009
Messages
2,341
I think you have to do this at the table side as the forms have a lot more properties.

Are the forms specific to a table or more than one?
 

Trevor G

Registered User.
Local time
Today, 22:19
Joined
Oct 1, 2009
Messages
2,341
I have been searching the net to help you and have finally found the correct solution with a twist. To achieve what you need you have to create several Functions and I am listing them below:

First Function to go into a Module Sheet

Function FieldTypeName(fld As DAO.Field) As String
'Purpose: Converts the numeric results of DAO Field.Type to text.
Dim strReturn As String 'Name to return
Select Case CLng(fld.Type) 'fld.Type is Integer, but constants are Long.
Case dbBoolean: strReturn = "Yes/No" ' 1
Case dbByte: strReturn = "Byte" ' 2
Case dbInteger: strReturn = "Integer" ' 3
Case dbLong ' 4
If (fld.Attributes And dbAutoIncrField) = 0& Then
strReturn = "Long Integer"
Else
strReturn = "AutoNumber"
End If
Case dbCurrency: strReturn = "Currency" ' 5
Case dbSingle: strReturn = "Single" ' 6
Case dbDouble: strReturn = "Double" ' 7
Case dbDate: strReturn = "Date/Time" ' 8
Case dbBinary: strReturn = "Binary" ' 9 (no interface)
Case dbText '10
If (fld.Attributes And dbFixedField) = 0& Then
strReturn = "Text"
Else
strReturn = "Text (fixed width)" '(no interface)
End If
Case dbLongBinary: strReturn = "OLE Object" '11
Case dbMemo '12
If (fld.Attributes And dbHyperlinkField) = 0& Then
strReturn = "Memo"
Else
strReturn = "Hyperlink"
End If
Case dbGUID: strReturn = "GUID" '15
'Attached tables only: cannot create these in JET.
Case dbBigInt: strReturn = "Big Integer" '16
Case dbVarBinary: strReturn = "VarBinary" '17
Case dbChar: strReturn = "Char" '18
Case dbNumeric: strReturn = "Numeric" '19
Case dbDecimal: strReturn = "Decimal" '20
Case dbFloat: strReturn = "Float" '21
Case dbTime: strReturn = "Time" '22
Case dbTimeStamp: strReturn = "Time Stamp" '23
'Constants for complex types don't work prior to Access 2007 and later.
Case 101&: strReturn = "Attachment" 'dbAttachment
Case 102&: strReturn = "Complex Byte" 'dbComplexByte
Case 103&: strReturn = "Complex Integer" 'dbComplexInteger
Case 104&: strReturn = "Complex Long" 'dbComplexLong
Case 105&: strReturn = "Complex Single" 'dbComplexSingle
Case 106&: strReturn = "Complex Double" 'dbComplexDouble
Case 107&: strReturn = "Complex GUID" 'dbComplexGUID
Case 108&: strReturn = "Complex Decimal" 'dbComplexDecimal
Case 109&: strReturn = "Complex Text" 'dbComplexText
Case Else: strReturn = "Field type " & fld.Type & " unknown"
End Select
FieldTypeName = strReturn
End Function

Next Function in the same module sheet

Function GetDescrip(obj As Object) As String
On Error Resume Next
GetDescrip = obj.Properties("Description")
End Function

Then the final Function to get the information into the immediate window. The table name will need to be changed.

I have test this and it works, I had to adjust the code a little.

Function TableInfo()
On Error GoTo TableInfoErr
' Purpose: Display the field names, types, sizes and descriptions for a table.
' Argument: Name of a table in the current database.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs("tblEmployee")
Debug.Print "FIELD NAME", "FIELD TYPE", "SIZE", "DESCRIPTION"
Debug.Print "==========", "==========", "====", "==========="
For Each fld In tdf.Fields
Debug.Print fld.Name,
Debug.Print FieldTypeName(fld),
Debug.Print fld.Size,
Debug.Print GetDescrip(fld)
Next
Debug.Print "==========", "==========", "====", "==========="
TableInfoExit:
Set db = Nothing
Exit Function
TableInfoErr:
Select Case Err
Case 3265& 'Table name invalid
MsgBox strTableName & " table doesn't exist"
Case Else
Debug.Print "TableInfo() Error " & Err & ": " & Error
End Select
Resume TableInfoExit
End Function

The source came from Allen Brownes website

http://allenbrowne.com/func-06.html
 

Robert.Finch

New member
Local time
Today, 22:19
Joined
Dec 17, 2008
Messages
7
Trevor

Some forms are fed from tables, some from queries and some from SQL statements., I am now beginning to realise that this might be a bigger task than I thought.

Is there a way to test the contents or a control and derive the data type?
 

DCrake

Remembered
Local time
Today, 22:19
Joined
Jun 8, 2005
Messages
8,632
If you want to know the data type after the control has been populate then you can use

If IsNumeric(Me.TextBox") Then

or

If IsDate(Me.TextBox) then
 

Trevor G

Registered User.
Local time
Today, 22:19
Joined
Oct 1, 2009
Messages
2,341
Trevor

Some forms are fed from tables, some from queries and some from SQL statements., I am now beginning to realise that this might be a bigger task than I thought.

Is there a way to test the contents or a control and derive the data type?

If you note David's response this would mean you need a routine to open every form before determining IsNumeric, IsText, IsDate you would need to writer the code to goto something like a table, word document, excel spreadsheet or something like to hold the list, so you can maintain a complete record. but there maybe issues to determin Memo Fields Types or Number Fields etc
 

DCrake

Remembered
Local time
Today, 22:19
Joined
Jun 8, 2005
Messages
8,632
Trevor

My option was to examine the contents of a control to decipher what type of value is contained within it. A text box is a text box is a textbox. It's the data conained within it that needs evaluating. That's what I can read from the origninal question. So having populated the control the code can enumerate though the contents of each control and make a decision as to the contents. This is limiting as a boolean value is also numeric and also you cannot distinguish between text and memo.

The main issue is why? and once evaluated what is the next step? In 90% of cases you would know what type of data is being populated, 99% in the case of bound controls.
 

Trevor G

Registered User.
Local time
Today, 22:19
Joined
Oct 1, 2009
Messages
2,341
Hi David,

I was only looking to help Robert achieve his goal, I know the pitfalls of the form elements and agree with you entirely.

I try not to ask the reasons why someone wants to do what they have asked to be helped with unless it is necessary.

I think I have gone as far as I can with this thread.
 

Guillem

Member
Local time
Today, 23:19
Joined
Mar 28, 2020
Messages
32
I make a variation of AllenBrowne routine GetDLLTypeName(FieldType) ( http://allenbrowne.com/ser-49.html) for DLL DataTypes
, adapted for VBA DataTypes
This returns almost consistent results applied to all From control types

Public Function GetVBATypeName(FieldType As VBA.VbVarType) As String
Dim rtnStr As String
If FieldType > 8192 Then 'Handle compound Array DataTypes (8192)
rtnStr = GetVBATypeName2(8192) & "-" & GetVBATypeName2(FieldType - 8192)
Else ' Normal DataTypes
rtnStr = GetVBATypeName2(FieldType)
End If
GetVBATypeName = rtnStr
End Function

Public Function GetVBATypeName2(FieldType As VBA.VbVarType) As String
Dim rtnStr As String
Select Case FieldType
Case vbEmpty: rtnStr = "EMPTY"
Case vbNull: rtnStr = "NULL"
Case vbInteger: rtnStr = "INTEGER"
Case vbLong: rtnStr = "LONG"
Case vbSingle: rtnStr = "SINGLE"
Case vbDouble: rtnStr = "DOUBLE"
Case vbCurrency: rtnStr = "CURRENCY"
Case vbDate: rtnStr = "DATE"
Case vbString: rtnStr = "STRING"
Case vbObject: rtnStr = "OBJECT"
Case vbError: rtnStr = "ERROR"
Case vbBoolean: rtnStr = "BOOLEAN"
Case vbVariant: rtnStr = "VARIANT"
Case vbDataObject: rtnStr = "DATAOBJECT"
Case vbDecimal: rtnStr = "DECIMAL"
Case vbByte: rtnStr = "BYTE"
Case 20: rtnStr = "LONGLONG" 'vbLongLong ...
Case vbUserDefinedType: rtnStr = "USERDEFINED"
Case vbArray: rtnStr = "ARRAY"
Case Else: rtnStr = "Error DataType"
End Select
GetVBATypeName2 = rtnStr
End Function

to note that Case 20, correspond to vbLongLong, but not recognized by VBA (¿?) in Access2019
' Case vbLongLong: rtnStr = "LONGLONG" '(=20)
Described here :
https://docs.microsoft.com/en-us/of...interface-help/vartype-function#return-values.
 
Last edited:

Guillem

Member
Local time
Today, 23:19
Joined
Mar 28, 2020
Messages
32
Trevor

Thanks for the ideas but not really what I want. I want to glean the datatype info from any control on any form, this is part of a bigger project.

I don't need to differentiate between numeric types, just number, text, date/time and yes/no.

Thanks

You tried the TypeName() function?.
it returns the type based on current value.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:19
Joined
Feb 19, 2013
Messages
16,553
still working on it after 10 years?:)
 

Users who are viewing this thread

Top Bottom