Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

field number type in access tabledefs fields

I am using a for loop to rotate around the fields in a table. Is there a way to distinguish if the field is a numeric field because those fields need a slightly different action on them.


I am working in access vba in the latest version of access.

For Each fld In db.TableDefs!tClient.Fields
'    MsgBox fld.Name

 
    If fld.Name = "abiCode" Or fld.Name = "Batch" Then
    'do nothing
    
    Else
     'insert data 
      '**  determine if field is numeric type. i.e long int double etc
        Debug.Print fld.Type
        Debug.Print fld.Name
        tempstr = "INSERT INTO [Tbl" & fld.Name & "](" & ClientCodeName & ", [" & fld.Name & "Prev], [" & fld.Name & "Change], ChangeYearMonth, VehicleCategory, Matched)" _
        & " SELECT QryClientDifferencesLogic." & ClientCodeName & ", " & TableNamePrevious & ".[" & fld.Name & "], " & TableNameCurrent & ".[" & fld.Name & "],'" & GetMaxBatchDate("repository") & "' AS ChangeYearMonth, QryClientDifferencesLogic.VehCat AS VehicleCategory, IsMatched(QryClientDifferencesLogic." & ClientCodeName & ",'" & ClientName & "') AS Matched" _
        & " FROM (QryClientDifferencesLogic LEFT JOIN " & TableNamePrevious & " ON QryClientDifferencesLogic." & ClientCodeName & "= " & TableNamePrevious & "." & ClientCodeName & ") LEFT JOIN " & TableNameCurrent & " " _
        & " ON QryClientDifferencesLogic." & ClientCodeName & "=" & TableNameCurrent & "." & ClientCodeName & " WHERE (((QryClientDifferencesLogic.[" & fld.Name & "Diff])=-1));"
        
        Debug.Print tempstr
        Debug.Print index
        DoCmd.RunSQL tempstr
    
    End If
index = index + 1
Next

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

i did experiment with that in the form of Debug.Print fld.Type but I couldnt find a list to tell me the numbers to put in the select case
No, I had to browse a little - but I knew it was there - somewhere.

/gustav
thank you
You are welcome!

/gustav