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
-
-
-
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.
6 1634 zmbd 5,501
Recognized Expert Moderator Expert
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.
I totally agree; however, this is data that is extracted from an external system. I have no control over the import field.
Rabbit 12,516
Recognized Expert Moderator MVP
You may not have control over the fields being exported, but you have control over the field it gets imported to.
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 5,501
Recognized Expert Moderator Expert
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 5,501
Recognized Expert Moderator Expert
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Andy R. |
last post by:
Hello everyone,
I've spent quite some time now, looking for some information on how to
get this done, sadly none has helped me much, though.
I have a bit of java scrpt on a webpage (.php) to...
|
by: Sarah Tanembaum |
last post by:
Beside its an opensource and supported by community, what's the fundamental
differences between PostgreSQL and those high-price commercial database (and
some are bloated such as Oracle) from...
|
by: Paul Taylor |
last post by:
I want to show a figure based on an numeric entry that more than 20
(or 21and higher).
If Number value is 21 or higher then the amount returned is Number
value * 120 (£) * 10 (%) to give a...
|
by: Christopher Benson-Manica |
last post by:
(Followups set to comp.std.c. Apologies if the crosspost is unwelcome.)
strchr() is to strrchr() as strstr() is to strrstr(), but strrstr()
isn't part of the standard. Why not?
--...
|
by: Dany |
last post by:
Our web service was working fine until we installed .net Framework 1.1 service pack 1. Uninstalling SP1 is not an option because our largest customer says service packs marked as "critical" by...
| |
by: abhi147 |
last post by:
Hi ,
I want to convert an array of bytes like :
{79,104,-37,-66,24,123,30,-26,-99,-8,80,-38,19,14,-127,-3}
into Unicode character with ISO-8859-1 standard.
Can anyone help me .. how should...
|
by: Siam |
last post by:
Hi all...
What's the best way to return some string from a function (without
using the string class). Would the following work:
char* getString( )
{
return "TheString";
}
|
by: Tony |
last post by:
I'm working with GUI messaging and note that MFC encapsulates
the message loop inside of a C++ class member function. Is this
somehow inherently less robust than calling the message loop
functions...
|
by: David Pratt |
last post by:
I recently replied to MC felons post on returning strings and was in error
on what I was trying to do. After running completely thru my code, I did
find that I was misunderstanding how C++ works...
|
by: Eric S. Johansson |
last post by:
Almar Klein wrote:
there's nothing like self interest to drive one's initiative. :-) 14 years with
speech recognition and counting. I'm so looking to my 15th anniversary of being
injured next...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |