Find Field Data Type in VBA

[bhcob1]

I have a list which is populated with Fields from a table, I want to be able to determine if the data type of the value selected in the list is Number. Any ideas, i thought there would be something along the lines of

Me!lstQueryCriteria.Datatype, but my results turned up no dice.

The reason I wish to do this is, because I am creating a query in VBA and in the Where clause I use IN, and it is giving me a datatype mismatch whenever it is sent a Number data type.

The SQL statement created produces something along the lines of
IN('R10001','R10003')
But for a Number datatype
IN('1','3')
doesnot work, i need to remove the ' for this datatype
IN(1,3) and then it works

Excuse the babble, i think i have re-explained myself at least twice over.

[ADezii]

I have a list which is populated with Fields from a table, I want to be able to determine if the data type of the value selected in the list is Number. Any ideas, i thought there would be something along the lines of

Me!lstQueryCriteria.Datatype, but my results turned up no dice.

The reason I wish to do this is, because I am creating a query in VBA and in the Where clause I use IN, and it is giving me a datatype mismatch whenever it is sent a Number data type.

The SQL statement created produces something along the lines of
IN('R10001','R10003')
But for a Number datatype
IN('1','3')
doesnot work, i need to remove the ' for this datatype
IN(1,3) and then it works

Excuse the babble, i think i have re-explained myself at least twice over.

Here is a little Routine that will number all the Fields in a Table, list their Name, and if it is Numeric, list the Data Type. If not, N/A. Ideally, this could be refined and contained within a Function/Sub Procedure. You can adapt it to your own specifc needs, but if you further help just ask. The important point here is that the Type Property of a Field Object returns a Numeric Value indicating its Data Type.

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

Sample Output:
01) Counter - Long
02) PayrollNumber - Long
03) Order Date - N/A
04) Required_Date - N/A
05) Received_Date - N/A
06) Rank - N/A
07) LastName - N/A
08) FirstName - N/A
09) MI - N/A
10) Designation - N/A
11) MiscCode - N/A
12) Company - N/A
13) Platoon - N/A
14) VacationCode - N/A
15) Title - N/A
16) Address - N/A
17) ZipCode - N/A
18) Sex - N/A
19) BloodType - N/A
20) PhoneNumber - N/A
21) BirthDate - N/A
22) AppointmentDate - N/A
23) AssignmentDate - N/A
24) LongevityDate - N/A
25) Badge - N/A
26) SSN - N/A
27) DriverLicenseNumber - N/A
28) DriverLicExpDate - N/A
29) TransferredOut - N/A
30) Backup - N/A
31) FullName - N/A
32) Years - Integer
33) L - N/A
34) NoDays - Long
35) InvDate - N/A
36) EndDate - N/A
37) SSAN - N/A
38) Date - N/A
39) Integer - Integer
40) Long - Long
41) Single - Single
42) Double - Double
43) Byte - Byte

[bhcob1]

Thanks for that, very helpful

[ADezii]

Thanks for that, very helpful

No Problemo.