Results 1 to 4 of 4
  1. #1
    hehrerh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    35

    Data type mismatch in criteria expression

    Hello! I have a query in which I am joining many autonumber fields. I converted all autonumbers to long integer using CLng. e.g. AccountingDivisionID: CLng([BudgetLevel07].[AccountingDivisionID])



    Here is the SQL. The query was working fine, but now suddenly I am getting this mismatch error.

    Thoughts?

    SELECT [2000-000 - PROJECT].[2000-000-030 - PROJECT NAME], [OPTIONS - DEVELOPER].DEVELOPER, CLng([BudgetLevel07].[2000-000-010 - ID]) AS [2000-000-010 - ID], CLng([2700-000 - BUDGETS].[BudgetID]) AS BudgetID, BudgetLevel07.BudgetName, BudgetLevel07.AnticipatedChangeinLLC_MV_PerAcre, BudgetLevel07.ConstructionDurationMonths, BudgetLevel07.ConstructionInterestRate, BudgetLevel07.SellOutMonths, BudgetLevel07.DiscountToHomeCompany, BudgetLevel07.BudgetLots, BudgetLevel07.BudgetStreetLength, BudgetLevel07.BudgetAcres, BudgetLevel07.BudgetAverageLotWidth, BudgetLevel07.BudgetCalcType, BudgetLevel07.IsTrailingCost, CLng([BudgetLevel07].[BudgetLineItemID]) AS BudgetLineItemID, CLng([BudgetLevel07].[AccountingCodeID]) AS AccountingCodeID, BudgetLevel07.OriginalEstimateValue, BudgetLevel07.AdjustmentAmount, BudgetLevel07.LinealFeet, BudgetLevel07.Notes, CLng([BudgetLevel07].[BudgetCalcTypeID]) AS BudgetCalcTypeID, CLng([BudgetLevel07].[AccountingDivisionID]) AS AccountingDivisionID, BudgetLevel07.AccountingCode, BudgetLevel07.AccountingCodeDescription, BudgetLevel07.BudInflowOutflow, BudgetLevel07.BudIsContractItem, BudgetLevel07.BudIncludeInInterestCalc, BudgetLevel07.BudDevelopmentDirectCost, BudgetLevel07.BudDevelopmentDirectAndOffsiteCost, BudgetLevel07.BudDevelopmentCostNoInt, BudgetLevel07.AccountingDivisionDescription, BudgetLevel07.OEI1, BudgetLevel07.ContractCostExcContingency, BudgetLevel07.OEI2, BudgetLevel07.OEI3, BudgetLevel07.ContractCost, BudgetLevel07.OEI4, BudgetLevel07.OEI5, BudgetLevel07.DevelopmentDirectAndOffsiteCost, BudgetLevel07.DevelopmentDirectCost, BudgetLevel07.DevelopmentCostNoInt, BudgetLevel07.OEI6, BudgetLevel07.OriginalTotalCost, BudgetLevel08A.OriginalTotalCostEntireProject, BudgetLevel07.OriginalCostPerLot, BudgetLevel07.OriginalCostPerAcre, BudgetLevel07.OriginalCostPerFtOfStreet, [OriginalTotalCost]/[OriginalTotalCostEntireProject] AS OriginalPercentTotalContract, BudgetLevel07.AdjustedTotalCost, BudgetLevel08B.AdjustedTotalCostEntireProject, BudgetLevel07.AdjustedCostPerLot, BudgetLevel07.AdjustedCostPerAcre, BudgetLevel07.AdjustedCostPerFtOfStreet, [AdjustedTotalCost]/[AdjustedTotalCostEntireProject] AS AdjustedPercentTotalContract
    FROM ([OPTIONS - DEVELOPER] INNER JOIN (((BudgetLevel07 INNER JOIN BudgetLevel08A ON BudgetLevel07.BudgetID = BudgetLevel08A.BudgetID) INNER JOIN BudgetLevel08B ON BudgetLevel07.BudgetID = BudgetLevel08B.BudgetID) INNER JOIN [2000-000 - PROJECT] ON BudgetLevel07.[2000-000-010 - ID] = [2000-000 - PROJECT].[2000-000-010 - ID]) ON [OPTIONS - DEVELOPER].[DEVELOPER ID] = [2000-000 - PROJECT].[2000-000-060 - DEVELOPER]) INNER JOIN [2700-000 - BUDGETS] ON BudgetLevel07.BudgetID = [2700-000 - BUDGETS].BudgetID;

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Autonumbers are already long integer, remove that added complexity.

    The mismatch is usually a numeric field being used incorrectly, either in a JOIN (with a text field) or in a calculation. Could it be
    [OPTIONS - DEVELOPER].[DEVELOPER ID] = [2000-000 - PROJECT].[2000-000-060 - DEVELOPER])

  3. #3
    hehrerh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    35
    I need to perform that conversion to avoid the 'resulting table cannot have more than one auto-number field' error.

    However, you were correct. I was joining Developer ID with the Developer Name instead of with Developer ID. Thanks!

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Should not get that error with normalized tables.... to me it looks like a spreadsheet design. (but then again, I know nothing about your business)


    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names


    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp


    Good luck with your project........

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. Replies: 5
    Last Post: 02-25-2015, 08:42 AM
  3. Data Type mismatch in criteria expression
    By elb1999 in forum Queries
    Replies: 2
    Last Post: 01-20-2012, 02:38 PM
  4. Data type mismatch in criteria expression
    By buienxg in forum Access
    Replies: 2
    Last Post: 11-22-2011, 10:29 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