Determine the data type of a field - Arghhh!

[scoobey]

Hi!

I am trying to create a 'search form' for querying one of my databases. First off, I have a combo box which selects the fields I need to search on. I then have a second combo box which runs a DISTINCT query on this field. For example, the table is called 'tbl_Desktops' and contains information about desktop PC's at our company. The first combo box displays the fields for this table. For example, if you select 'Model' from the first combo, the second combo box is automatically populated with DISTINCT model numbers from the model field. When a user selects a model number from the second combo, the results for all the 360 machines are displayed.
My problem is this:
I have text, date/time and yes/no fields in the table. I need to determine what the field data type is before I return the results so I can generate the SQL properly. For example, if I'm searching for all models (which is a text data type), I have to enclose it in speech marks. If is is a yes/no field or a date/time field, I need to remove the speech marks otherwise a data type mismatch error occurs.
I know this is a tricky one to understand (?), but I would be grateful for any feedback.

Thanks, Scoobey....

 

[LittleSmudge]

I have this code in a form of mine that populates a combobox with the field names and field types of a specific table

Dim cat As ADOX.Catalog
Dim col As Column
Dim intC As Integer
Dim tbl As Table
On Error GoTo Err_Form_Load
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
DoCmd.Echo True, "Researching Owner Table definition "
DoCmd.Hourglass True
Set tbl = cat.Tables("tblOwner")
DoCmd.Hourglass False
cboOwnerF1.RowSource = "OwnerId; Number"
For Each col In tbl.Columns
    If col.Type = 202 Or col.Type = 203 Then    ' 202 = Text, 203 = Memo
        cboOwnerF1.RowSource = fnStringCombiner(cboOwnerF1.RowSource, col.Name, "; ")
        Select Case col.Type
        Case Is = 202
            cboOwnerF1.RowSource = fnStringCombiner(cboOwnerF1.RowSource, "text", "; ")
        Case Is = 203
            cboOwnerF1.RowSource = fnStringCombiner(cboOwnerF1.RowSource, "memo", "; ")
        End Select
    End If
Next

I think you should be able to adapt that to your needs.


fnStringCombiner is a custom function that does a lot of Null checking stuff - but for your needs it just returns the three input parameters re-ordered
so
fnStringCombiner(a,b,c) will return
a & c & b



'ope-it-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[scoobey]

Hmm. Think I may have cracked it by using 'LIKE' in the SQL statement. This seems to return all data type sets correctly.