Results 1 to 6 of 6
  1. #1
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16

    Total NEWB Doing His Best; Data type mismatch in criteria expression

    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try
    Code:
    DB.Execute "DELETE FROM MailFile WHERE UID IS NULL;", dbFailOnError
    What is the datatype of UID in table MailFile?

  3. #3
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    What format is the UID field?

  4. #4
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    THANK YOU!

    The "DB.Execute "DELETE FROM MailFile WHERE UID IS NULL;", dbFailOnError" worked.

    UID is Long Integer. Any idea why "" , '', and =NULL didn't work?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad it worked for you.

    Quotes are used with text fields.
    Nothing is =NULL

    NULL is unknown

    From Google:
    A common misconception is that a null value is simply an empty field or no value at all. That's not true. A null value indicates that the data is missing or unknown. Occasionally, a null value does mean that the data doesn't exist or isn't valid for that particular record, but the concepts aren't interchangeable.

  6. #6
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Thanks for the info! Hopefully this will all make it's way into the storage area of my brain!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data type mismatch in criteria expression.
    By KERRYDEE in forum Access
    Replies: 2
    Last Post: 05-06-2016, 11:56 AM
  2. Data Type Mismatch in criteria expression
    By gaker10 in forum Queries
    Replies: 3
    Last Post: 03-05-2015, 01:54 PM
  3. Replies: 5
    Last Post: 02-25-2015, 08:42 AM
  4. Data Type Mismatch in Criteria Expression
    By dehdahdoh in forum Queries
    Replies: 13
    Last Post: 05-01-2013, 11:41 AM
  5. Data type mismatch in criteria expression
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 11-23-2010, 10:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums