Greetings!
I've been using Access for a couple of years, on and off... no expert. A friend (who knows nothing about Access) asked me to look at a program that was written in the past by some employee who has long since disappeared. I started looking at it, and it's a mess, so I decided that I would take it on as a personal project, and see how much I could also do in VBA, since I know nothing about VBA.
I've gotten some stuff working, but have gotten stuck. I'm trying to delete records that have no data in the UID field. I tried it with UID = Null, and that ran but did nothing to the table. Ok, so I'm trying it now with UID = '', but get a "data type mismatch" and don't know enough yet to understand why. I've also tried with UID = "", but that got the same mismatch error. Any help at illuminating the situation would be very appreciated.
All of the commented out lines work, I commented them out so I wouldn't have to constantly restore the table to run the code again.
Code:
Option Compare Database
Private Sub ModImpData_Click()
Dim DB As DAO.Database
Dim td As TableDef
Set DB = CurrentDb
'DB.TableDefs("MailFile").Fields("Contract #").Name = "SubNo"
'DB.TableDefs("MailFile").Fields("Ship to Party Name").Name = "CompanyName"
'DB.TableDefs("MailFile").Fields("Postal Code").Name = "PostalCode"
'DB.TableDefs("MailFile").Fields("Address 1").Name = "Address1"
'DB.TableDefs("MailFile").Fields("Address 2").Name = "Address2"
'DB.TableDefs("MailFile").Fields("Renewal Contract").Name = "Field3"
'DB.TableDefs("MailFile").Fields("Printer Issue ID").Name = "Field4"
'DB.TableDefs("MailFile").Fields("Contract Start Date").Name = "Field5"
'DB.TableDefs("MailFile").Fields("Item Number").Name = "Ite"
'DB.TableDefs("MailFile").Fields("Person Type").Name = "ImprintCode"
'DB.TableDefs("MailFile").Fields("Business Category").Name = "Number"
'DB.Execute "UPDATE MailFile SET MailFile.Field3 = '';"
'DB.Execute "UPDATE MailFile SET MailFile.Field4 = '';"
'DB.Execute "UPDATE MailFile SET MailFile.ImprintCode = '';"
'DB.Execute "UPDATE MailFile SET MailFile.Number = '';"
DB.Execute "DELETE FROM MailFile WHERE UID = '';"
End Sub
Many thanks for any help,
Joe
aka Netopia