Need Data Types!! (1 Viewer)

NewbieUK

Registered User.
Local time
Today, 11:18
Joined
Aug 17, 2010
Messages
29
Hi There,

Hoping someone can help as I've got a bit stuck!

I am dynamically constructing an SQL query within Access VBA to allow a user to define what columns they want to include in a query and then also define the criteria.

My VBA then constructs the SQl code from what they have entered into forms and creates a new query to run. This works fine. However, I have constructed my SQL code to run only when the data type of the field is set to Text! I now need a way of identifying the data type of the field they have selected, and then I can apply different SQL for different data type. i.e. on my where clause, I am using " " around my criteria. But this will not work for numbers or date columns.

My data types will be either Text, Number or Date.

I found this:

Public Function FindDataType()
Dim intNumberofFields As Integer, intFieldType As Integer, strTypeName As String

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

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

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

End Function


Which loops through my whole table and gives me a full column index and the data type for each column.

What I want to do, is pass through one column which is defined in a form elsewhere, and to return the data type of my selected column.

Any ideas how I can use the above and pass through my own column name?

Thanks
 

Beetle

Duly Registered Boozer
Local time
Today, 05:18
Joined
Apr 30, 2011
Messages
1,808
Below is a modification of that function where you pass the table name and column names as arguments. If it finds the field name in the table it returns the data type. Something like this may work for you. You can just pass the table and field names from form controls.


Code:
Public Function FindDataType(strTable As String, strField As String)

Dim db As Database
Dim i As Integer
Dim strTypeName As String

Set db = CurrentDb

For i = 0 To db.TableDefs(strTable).Fields.Count - 1
    If db.TableDefs(strTable).Fields(i).Name = strField Then
        Select Case db.TableDefs(strTable).Fields(i).Type
            Case 2, 3, 4, 6, 7
                strTypeName = "Number"
            Case 8
                strTypeName = "Date"
            Case 10
                strTypeName = "Text"
            Case Else
                strTypeName = "Other"
        End Select
        MsgBox strField & vbCrLf & strTypeName
        Exit For
    End If
Next

End Function
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:18
Joined
Jan 20, 2009
Messages
12,849
The loop in Sean's code is not required.
Refer directly to the field and return the Type with:

db.TableDefs(strTable).Fields(strField).Type

BTW, In a loop it is considerably more efficient to save (Count -1) as a variable and use that variable in the loop than read the count property each time and subtract one from it.
 

Users who are viewing this thread

Top Bottom