I am trying to build a database browser that will allow for the user
to select an MDB file and list the available tables, fields, and field
values and then generate a query on the fly.
I have no problems putting the table names, field names, or values
into listboxes. I can even build a query based on the value selected
in the values list...IF that field is of the STRING type.
In the SQL statement I am building, I am concatenating the single
quotes into the statement. This is fine for STRING data types, but if
the user selects a field of another data type, the SQL statement is
invalid. I understand this but can't seem to find a way to return the
data type of the selected field.

Example:
List1 contains the table names
List2 contains the field names
List3 contains the field values
I am using the variables Listx.List(Listx.ListIndex) for selections.

Sub cmdListTables_Click()
' Fill List1 with the table names
        If Not Left(DB.TableDefs(i).Name, 4) = "MSys" Then
                List1.AddItem DB.TableDefs(i).Name
        Next
End Sub

Sub cmdListFields_Click()
' Put the field names into List2

        ' Make sure we have selected a table
        If Not List1.ListIndex = -1 Then

            Set TD = DB.TableDefs(List1.List(List1.ListIndex))

            For i = 0 To TD.Fields.Count - 1
                List2.AddItem TD.Fields(i).Name
            Next
            End If
End Sub

Sub cmdListValues_Click()
' Make sure we have a field selected
If Not List2.ListIndex = -1 Then

SQL = "SELECT [" & List2.List(List2.ListIndex) & "] FROM [" & TD.Name
& "]

Set RS = DB.OpenRecordset(SQL, dbOpenDynaset)

Do While Not RS.EOF
' Check for null values before trying to add them to the list
        If Not IsNull(RS(0)) Then
            List3.AddItem RS(0)
        End If
        RS.MoveNext
Loop
End Sub

Ok, now that I have the values in List3, I want to be able to select
one of them, build an SQL statement to create a recordset, and show
the details of that record. Doing this directly is not a problem, but
I need to check the TYPE of the field first so I will know whether or
not to use the single quotes in the SQL statement.

Currently I have this to display the record in a message box:

SQL = "SELECT * FROM [" & List1.List(List1.ListIndex) & "]"
SQL = SQL & " WHERE [" & List2.List(List2.ListIndex) & "]"
SQL = SQL & " = '" & List3.List(List3.ListIndex) & "'"
Set RS = DB.OpenRecordset(SQL)
Do Until RS.EOF
    For i = 0 To RS.Fields.Count - 1
        Msg = Msg & RS.Fields(i) & vbCrLf
    Next
    RS.MoveNext
    MsgBox Msg
    Msg = ""
Loop

If the selected field is not of the STRING datatype, it crashes.
So, I thought I would store the field name in a variable and check
it's type, but for some reason, I can't do it. I have checked the
cariable assignment in the debug windows and it is fine, but I can't
use it in the code for checking the datatype!

' This prints the field name in the debug window
TheField = List2.List(List2.ListIndex)
Debug.Print TheField

But, this will not work unless I hardcode the fieldname into it:
Select Case TD.Fields(" & TheField & ").Type
Nor will this:
Select Case TD.Fields![" & TheField & "].Type
But this will:
Select Case TD.Fields("LastName").Type
and this will:
Select Case TD.Fields![LastName].Type

Please, if anyone has a solution to this, let me in on it. I am losing
my mind !!

Thanks in advance,
Ed Phillippe

********************************************************
**  Southern Indiana Bass Fishing / Outdoor Software  **
**     http://www.*-*-*.com/ ~dlion/mypage.htm    **
********************************************************

<stuff deleted>

   Select Case TD.Fields(" & TheField & ").Type

Your database probably doesn't have a field named " & TheField & ".  You want
to have this instead:

   Select Case TD.Fields(TheField).Type

Bob Rossney