Avatar of Todd MacPherson
Todd MacPherson
Flag for Canada asked on

Testing for and Verifying Field Types and Widths in a linked DBF

I have some code that is supposed to look through the fields in selected dbf table and test to make sure:

- the field name is right
- the field type is right
- the field width is right

I used the following and it worked when I just tested for field names but the type and fieldsize failed. I tired putting the field size in without quotes and it failed with an error:

field.fieldsize <invalid field data type>

I also had the "Char" type set to "Text" and that did not help.

The numeric I am not sure about either because the options can be float, double - any numeric type as long as it not integer or long integer. How can I test properly?

I do not know if it matters but the dbf comes from a GIS program that use dbf as its default file type. In the GIS software the text fields show up as Text in the properties after being created,  but when being created Text is not an available type, it is called string.

Numeric has the following types in it:

Short Integer
Long Integer
Double
Float

With either float or double acceptable.

Thanks

PBLack

   
 For Each fld In .Fields
        If fld.Name = "A" And fld.Type = "Char" And fld.FieldSize = "8" Then
            boolA = True
        ElseIf fld.Name = "B" And fld.Type = "Char" And fld.FieldSize = 2 Then
            boolB= True
        ElseIf fld.Name = "C" And fld.Type = "Numeric" Then
            boolC = True
        ElseIf fld.Name = "D" And fld.Type = "Char" And fld.FieldSize = 7 Then
            boolD = True
        ElseIf fld.Name = "E" And fld.Type = "Char" And fld.FieldSize = 20 Then
            boolE = True
        ElseIf fld.Name = "F" And fld.Type = "Char" And fld.FieldSize = 4 Then
            boolF = True
        ElseIf fld.Name = "G" And fld.Type = "Char" And fld.FieldSize = 1 Then
            boolG= True
        ElseIf fld.Name = "H" And fld.Type = "Char" And fld.FieldSize = 13 Then
            boolH= True
        ElseIf fld.Name = "I" And fld.Type = "Char" And fld.FieldSize = 200 Then
            boolI= True
        End If
    Next

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Todd MacPherson

8/22/2022 - Mon
Todd MacPherson

ASKER
FYI I am using

Dim fld As DAO.Field
Todd MacPherson

ASKER
I did this:

With recFld
    For Each fld In .Fields
        MsgBox fld.Name & " " & fld.Type & " " & fld.Size
    Next
    .Close
End With

All fields of type text give 10

All fields of type date give 8

All fields of type double give 7

All fields of type long give 4

All fields of type short give 3

All fields of type decimal give 20

I will try that and see what happens
ASKER CERTIFIED SOLUTION
als315

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Todd MacPherson

ASKER
Nope that doesn't work

I tried:

If fld.Name = "A" And fld.Type = 10 And fld.Width = 8 Then

but that yields an error:

Method not supported
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Todd MacPherson

ASKER
@als315 I am going to try that. Please stand by.
als315

You can also import table (structure only) and check fields in imported table
Todd MacPherson

ASKER
I used your first suggestion and it works perfectly.  I set some Boolean operators to trigger  warning messages if a required field is not present or is not formatted properly.

I like it.

Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.