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
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