Results 1 to 6 of 6
  1. #1
    deguza is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    9

    var() does not resolve type mismatch error

    Hello all,

    How do I join the two tables I describe below?

    Thank you!

    Inserted the following italicized part into an existing query:

    dbo_Table_Clients
    INNER JOIN
    dbo_List_Suffixes
    ON
    dbo_Table_Clients.Suffix = dbo_List_Suffixes.ID_Suffixes


    The tables are on an SQL DB. On the two tables that I am joining I see the following types for the two:

    ID_Suffixes : float
    Suffix : nvarchar (255)

    When I look from access, they appear as number and text.

    The "ON" part gave me "Type mismatch" error.

    I then wrapped ID_Suffixes in var(). I got "The multi-valued field var(dbo_List_Suffixes.ID_Suffixes is not valid in the specified JOIN clause.



    BTW, dbo_List_Suffixes.ID_Suffixes is NOT a multi-valued field.
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this (you won't be able to edit in design view):

    dbo_Table_Clients.Suffix = CStr(dbo_List_Suffixes.ID_Suffixes)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Check the relation and data on your SQL backend. You're probably connecting the 2 wrong fields, or you have to review the structure of your back-end tables. Numeric fields can't be joined to character fields and a float is an approximate numeric data type, meaning it doesn't provide exact values, but very close approximations. So it is really not a good idea to use them in equal joins. If you have to convert them to characters, using a function in a join, you're not only getting very slow queries but also are not sure you're getting the correct values.
    Check out the primary key fields and foreign constraints in your SQL tables.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    It looks like field dbo_List_Suffixes.ID_Suffixes is ID for Suffix (but it having a float dataype is weird at least). Anyway, it is obvious fields ID_Suffixes and Suffix don't have any common values, and as follows, can't be used in ON clause of join.

    I see possible design variations here:
    1 - In SQL table List_Suffixes you have fields ID_Suffixes and Suffix (this table is a registry of all suffixes).
    1a - in Sql table Table_Clients you have only field Suffix.
    You join those tables on field Suffix;
    1b - in
    Sql table Table_Clients you have only field ID_Suffixes (or field with different name, but with same content), or both ID_Suffixes and Suffix.
    You join those tables on field ID_Suffixes (or whatever the names are);
    2 - In SQL table List_Suffixes you have field ID_Suffixes only, and you have an additional table where all suffixes are registered (e.g. Table_Suffixes: ID_Suffixes, Suffix.
    2a - in Sql table Table_Clients you have only field Suffix.
    You join tables ListSuffixes and Table_Suffixes on ID_Suddixes, and Table_Clients and Table_Suffixes on Suffix;
    2b - in Sql table Table_Clients you have both fields ID_Suffixes and Suffix.
    You join tables ListSuffixes and Table_Suffixes on ID_Suddixes, and Table_Clients and Table_Suffixes on ID_Suffix.

    Btw., in Access FrontEnd, you can edit linked file names - I advice to remove all "dbo_" parts Access puts there automatically - so all linked tables/queries will have same names as in SQL Database. And in case you really have the table like Table_Registry there, use registry ID in Table_Clients instead of Registry.

  5. #5
    deguza is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    9
    Thank you for your suggestions.

  6. #6
    deguza is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    9
    Arvil, detailed and valuable insights.

    Thank you!

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

Similar Threads

  1. Intermittant #Type! type mismatch error.
    By Ranger351w in forum Programming
    Replies: 4
    Last Post: 09-27-2020, 07:53 PM
  2. Type Mismatch Error
    By jo15765 in forum Modules
    Replies: 5
    Last Post: 06-10-2019, 11:13 AM
  3. Type 13 (Type Mismatch) error
    By sdel_nevo in forum Programming
    Replies: 5
    Last Post: 01-22-2016, 10:01 AM
  4. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  5. Replies: 1
    Last Post: 05-11-2012, 10:59 AM

Tags for this Thread

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