Is there a way to determine a field 1

[Dustman]

Is there a way to determine a field type from VBA? I've made a smart query form that lets the user build a query using combo boxes. Right now I'm pulling the colum list in a combo box using FieldList on the query that they are searching through. I need a way to determine the field type without hard coding each field name into my VBA Select Case statement.

For example, I have to catch the colums that are dates and surround their input with # signs. I also need to determine if the field is numeric or not because I'll have to use " marks on strings. I've got this working already but I don't want to leave my special fields hard coded. I'm leaving this project and somebody else will probably come back later and decide to change field names... I don't want to be called back to fix it.

I can post the code and some more info if needed. Don't worry about being too specific.. I'm pretty fluent in technical programming terms. -Dustin
Rom 8:28

 

[smedvid]

Just a shot in the dark... Not real clear what u are asking for. Try using ISNumeric and ISDate to validate fields. Additionally, use Input/Format of input fields on form. If you are attempting to determine what a database field type is, ... I've used the Analyze Documentor to get a list of Objects, Type and Size previously and validated against that. The assumption was that the data model would not change... Not sure if you can scan system tables (i.e. MSysObjects) for structure info... htwh,

Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Dustman]

Well.. here's the layout. I'm building a query with a form. I have rows of combo boxes, each row has a colum box, an operator box, and a value box. Since access doesn't allow arrays of controls, I have them named c0,o0,t0 and c1,o1,t1 and so on.. then I use a loop to move through them using Controls("c" & i)..

I get the values for the colum box from a FieldList of my query. The operators are pulled from another table. They type their criteria in the third box.

Using a loop I go through each row and build a select statement.

'*** c = Column       o = Operator      t = Text
    Dim i As Integer
    i = 0
    Dim Continue As Boolean
    Continue = False
    For i = 0 To 5 Step 1
        If Not Nz(Controls("c" & i), "") = "" And Not Nz(Controls("o" & i), "") = "" And Not Nz(Controls("t" & i), "") = "" Then
            If Continue = True Then
                strSqlWhere = strSqlWhere + " AND"
            End If
            Continue = True
            
            Dim LookFor As String
            'Look for special fields
            Select Case UCase(Controls("c" & i))
                Case "DDATE"
                    LookFor = "#" + Controls("t" & i) + "#"
                Case "DONTSENDMAIL"
                    If UCase(Controls("t" & i)) = "TRUE" Then
                        LookFor = "true"
                    ElseIf UCase(Controls("t" & i)) = "FALSE" Then
                        LookFor = "false"
                    Else
                        MsgBox "ERROR:" & vbCrLf & "DontSendMail must be TRUE or FALSE", vbCritical, "SYNTAX ERROR"
                        Controls("t" & i).SetFocus
                        Exit Sub
                    End If
                
                Case Else
                    LookFor = Controls("t" & i)
                    'Look for special operators
                    Select Case UCase(Controls("o" & i).Column(2))
                        Case "LIKE"  'Add the * to the end
                            If UCase(Controls("o" & i).Column(1)) = "CONTAINS" Then
                                LookFor = " '*" + LookFor + "*'"
                            Else
                                LookFor = " '" + LookFor + "*'"
                            End If
                        Case Else
                            LookFor = " '" + LookFor + "'"
                    End Select
            End Select
            
            strSqlWhere = Nz(strSqlWhere, "") + " [" + Controls("c" & i) + "] " + Controls("o" & i).Column(2) + LookFor
        End If
    Next i

    If Continue = False Then
        MsgBox "You must make at least one filter.", vbCritical
        Exit Sub
    End If
    strSql = "SELECT * FROM DonationsQuery WHERE" + strSqlWhere

    txtSQL = strSql
    lstResults.RowSource = strSql

Note that the DDATE and DONTSENDMAIL in the first case statement are colum names pulled from the query.

What I need is code that will replace my case statment. Each time through the loop I need to check the field type of the column (c#) and determine what characters (" # % ...) need to be appended to the value (t#) for that datatype.

I hope thats clear.. -Dustin
Rom 8:28

 

[chuck001]

Hi Dustman,

I have listed code below I think will get you going in what you are trying to do in extracting the field type information.

***Code***

Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to table1 table.
Set tdf = dbs.TableDefs!table1 ' table1 is a sample recordset I am looping through

For Each fld In tdf.Fields
Debug.Print fld.Name
Debug.Print fld.Type
Next fld

Set dbs = Nothing


Good luck!

 

[chuck001]

Dustman,

Also the type property returns numeric values so here is a listing of the string associated with the numeric value.

AutoNumber
4
Text
10
DateTime
8
Number
4
memo
12
currency
5
YesNo
1
OleObject
11
Hyperlink
12

 

[Dustman]

Thanks! I knew it was something like that. It's been a long time since I messed with Micro$oft stuff.. I just couldn't get my mind going that direction. I should be able to get it from here.. I'm using 2000 though so I'll have to use DAO.Database and so on but that should work! -Dustin
Rom 8:28

 

[Dustman]

Also, I need a way to populate another combo box with a listing of all my queries in my DB. You know of a simple way to do that? -Dustin
Rom 8:28

 

[chuck001]

Yeah,

Below is some code that will loop through query collection of the DB and give the query names excluding the system objects. I did not know if you combo box is bound to a table or not but you can write the information however you want.

***Code***

Dim dbs As Database
Dim qrydef As QueryDefs
Dim qry As QueryDef


' Return reference to current database.
Set dbs = CurrentDb
'Loop through the query's collection of the DB and gives you all non-system query names
For Each qry In dbs.QueryDefs
If Not qry.Name Like "MSys*" Then
Debug.Print qry.Name
End If
Next qry

Good luck!

 

[Dustman]

That will work great. Doing it that way, I should be able to limit the available queries by prefixing them and then checking only for the prefixed queries. Thanks! -Dustin
Rom 8:28

 

[Dustman]

Okay, another question on the field types. I need to put it in a loop like so..
[tt]
For i = 0 To 5 Step 1

Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim tdf As DAO.QueryDef
Dim fld As DAO.field

Set tdf = dbs.QueryDefs!DonationsQuery

Set fld = tdf.Fields!Controls("c" & i)
Select Case fld.Type
Case 8 'Date/Time - Surround search val with #'s
LookFor = "#" + Controls("t" & i) + "#"
Case 1 'True/False
blah blah blah
End Select

Next i
[/tt]

[tt]Controls("c" & i)[/tt] returns my field name (ex: "DonationDate&quot.
Obviously it won't work that way.. thats just the logical way I know how to explain it. How do I use a variable name to get a specific field into my field variable.

ex: Transforms what I have here into
[tt]tdf.Fields!DonationDate[/tt] -Dustin
Rom 8:28

 

[chuck001]

Hmmm...

I am having a little trouble passing a variable the fields property (tdf.fields!variablename) but I notice that since you are setting the query recordset, instead of using a select case statement try something like below:

***Code***

'loop through each field in the recordset instead of the controls of the form

For Each fld In tdf.Fields

if fld.type = 8 then 'Date/Time data type
lookfor = "#" + fld.name + "#"
End if
if fld.type = 10 then 'text data type
lookfor = "#" + fld.name + "#"
End if

Next fld

This might work for you but I am not sure how many data types you are trying to validate. I will still look into the above issue, Let me know if that works for you.

 

[Dustman]

Well, that is a good idea and I hadn't thought about trying it that way. It won't work in my situation though. I've got to go through the fields in a certain order. The user selects the order and then the query is built. Without knowing the order ahead of time, I would have to run through every field each time through the loop. Not only would it be a coding nightmare, it would really eat up time because I'm dealing with some rather large queries and lots of data (over 10000 records).

Surely there is a way to do this.. I'll try looking over in the VB forums too. -Dustin
Rom 8:28

 

[Dustman]

Yay! I'm kicking myself now for not noticing this before someone in the VB forums showed me (thread222-443509). It's so obvious and logical when looking at how I'm using the Controls() function. The solution is simple:

[tt]Set fld = tdf.Fields(Controls("c" & i))[/tt]

Final solution is:
[tt]
For i = 0 To 5 Step 1

Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim tdf As DAO.QueryDef
Dim fld As DAO.field

Set fld = tdf.Fields(Controls("c" & i))

Select Case fld.Type
Case 8 'Date/Time - Surround search val with #'s
LookFor = "#" + Controls("t" & i) + "#"
Case 1 'True/False
blah blah blah
End Select

Next i
[/tt]

Thanks for all your help and if anybody ever needs a nice "Smart Query form", send me an email.. this thing should plug right into any db when I'm done with it. -Dustin
Rom 8:28

 

[chuck001]

Yeah,

your "Smart Query form" sounds it might be pretty interesting and useful, I would like to see it. My email is charles@databindinc.com