Passing a field to be deleted - contains Special Chars - function doesn't acknowledge

[dowlingm815]

There is a field that contains special character when it is passed to a function, it doesn't acknowledge that the field exist in the table. When I remove the special chars, it passes the field and performs a delete on the field.

The field name is [Sales Order Date/Time Created] from table SO_Data.

Thank you in advance for your assistance.

The sub that calls the functions:

If ifFieldExists("[Sales Order Date/Time Created]", "SO_Data") Then
        'Call the Function
            DeleteField "SO_Data", "[Sales Order Date/Time Created]"
    Else
        MsgBox "Field Does not exist", vbInformation, "Field - [Sales Order Date/Time Created], Table: SO_Data"
    End If

the functions:

Option Compare Database
Option Explicit

Function ifFieldExists(FieldName As String, TableName As String) As Boolean
   Dim rs As Recordset, Db As Database ' DAO Vars

   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   'Checks if Field exists.
   'USAGE: ifFieldExists "FIELDNAME", "TABLENAME"

   On Error GoTo NoField 'If there is no Field capture the error.

   Set Db = CurrentDb()

   'If Field is there open it
   Set rs = Db.OpenRecordset("Select " & FieldName & " from " & TableName & ";")
   ifFieldExists = True
   rs.Close

ExitHere:

   Set rs = Nothing
   Db.Close
   Set Db = Nothing
   Exit Function

NoField:

   'If Field is not present set function to false

   ifFieldExists = False

   With Err
      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "ifFieldExists"
   End With
   Resume ExitHere
   Exit Function
End Function


Function DeleteField(tblName As String, strField As String) As Boolean

   Dim Db As DAO.Database
   Dim tdf As DAO.TableDef

   On Error GoTo errhandler
   ' References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   ' Set references by Clicking Tools and Then References in the Code View window
   ' Returns True on Success, False otherwise.
   ' Accepts
   ' tblName: Name of Table that the Field is located
   ' strField: Name of  Field to delete
   ' Deletes Field strField in Table tblName.
   ' USAGE: DeleteField "tblName", "strField"

   Set Db = CurrentDb()
   
   'Set tblName to your table mine is 1A
   Set tdf = Db.TableDefs(tblName)
   
   'Execute the delete
   tdf.Fields.Delete (strField)
   DeleteField = True
   MsgBox "Delete Field " & strField & " Complete."
   
   Db.Close
   
ExitHere:

   Set Db = Nothing
   Set tdf = Nothing

   Exit Function

errhandler: 'There is an error return as False

   DeleteField = False
   With Err

      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "deletefield"
   End With

   Resume ExitHere
End Function

[zmbd]

OK,
I haven't read thru your code yet; however, here is the number one advise I can give you:
When naming fields, files, folders, or what have you
use only alphanumeric and the underscore do NOT use spaces, do NOT use any special charactors.
Find a list of reserved word for SQL and VBA - Do NOT use these words when naming fields, files, folders, or what have you

You have already ran into why this is the strongest advise I can give you based on almost three decades of programing (if you'll allow me to back to my earliest CBM-Pet it is certainly 30 years)

Please save yourself and others that follow alot of heartburn and change those field names.


OK... now I'll take a closer look at your code.

[dowlingm815]

I totally agree; however, this is data that is extracted from an external system. I have no control over the import field.

[Rabbit]

You may not have control over the fields being exported, but you have control over the field it gets imported to.

[dowlingm815]

When altering, the parameter without the brackets, the function acknowledges the field.

The tables in the db, get uploaded to a SQL db. Applications reference the SQL table that use the same field names from the imported file.

[zmbd]

try this...

Option Compare Database
Option Explicit

Public Function poc20121022(ztablein As String, zinfield As String) As Boolean
'
'Based on code located at
'http://bytes.com/topic/access/answers/943863-passing-field-deleted-contains-special-chars-function-doesnt-acknowledge#post3731091
'by z
'
'Usage: if you were looking for field [Table2].[Jump/Here]
'then you would enter: POC20121022("Table2", "jump/here")
'
Dim zdb As DAO.Database
Dim ztdefs As DAO.TableDefs
Dim ztable As DAO.TableDef
Dim zfield As DAO.Field
'
On Error GoTo z_error_trap
'
Set zdb = CurrentDb
Set ztdefs = zdb.TableDefs
Set ztable = ztdefs(ztablein)
For Each zfield In ztable.Fields
Debug.Print "Compairing: " & UCase(zfield.Name) & " to: " & UCase(zinfield)
    If UCase(zfield.Name) = UCase(zinfield) Then
        poc20121022 = True
    End If
Next zfield
'
z_return_from_error:
'
Set zdb = Nothing
Set ztdefs = Nothing
Set ztable = Nothing
Exit Function
z_error_trap:
MsgBox Err.Number & vbCrLf & Err.Description & _
    vbCrLf & ztablein & "." & zinfield & _
    vbCrLf & "search resulted in an error." & _
    vbCrLf & "function will return a value of FALSE", vbCritical, "An Error Occured"
'
poc20121022 = False
Resume z_return_from_error
End Function

This looks at the current database table collection.
If the table is found, then it starts looking at the fields within the table.
If the table is not found it will error and tell you so then return false.
If the table is found and the field is found then it returns true
etc...
You can rename as needed
You can remove the debug print as needed.

[zmbd]

actually... we could remove the field in the same poc20121022 function by using the delete field method with the for loop.
I'll leave that as an exercise.