Results 1 to 7 of 7
  1. #1
    jerem is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    88

    Post Conversion Failed

    Hi,



    I use an SQL view as my Form source. The form opens correctly, however I get an error message when running the report as a subreport (no master / child link):

    "Conversion failed when when converting the nvarchar value "[FullAddress]" to data type int. (#245)"

    Here is the function behind the FullAddress field.:
    Code:
     ISNULL(CAST(Address AS nvarchar(100)) + N' ', N' ') + ISNULL(CAST(City AS nvarchar(100)) + N' ', N' ') + ISNULL(CAST(StateProvince AS nvarchar(100)) + N' ', N' ') + ISNULL(CAST(ZIPPostal AS nvarchar(100)) + N' ', N' ') + ISNULL(CAST(CountryRegion AS nvarchar(100)), N' ')
    Can anybody help?

    Thank you

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    why not perform this concantenation in access instead of your SQL side if it's causing a problem? What happens if you take the 'fulladdress' field out of the subform, does it then cancel the error? or do you get the error regardless of whether the fulladdress field is in the subform or not? What happens when you open the view by itself in access? do you get an error? Try sorting the view by the fulladdress field and see if you get an error. If you do there's something wrong in the formula or you have a bad piece of data that is causing at least 1 record to not evaluate correctly.

  3. #3
    jerem is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    88
    Ok... That's funny. I removed the field [FullAddress] in the view, refreshed links, and the same error occurs. I have always assumed it is the field full address because the text value in the error message is an address...

    Here is what I'm getting:

    [Microsoft][ODBC SQL server][SQL Server]Conversion failed when when converting the nvarchar value "7th Street..." to data type int. (#245)[Microsoft][ODBC SQL server][SQL Server]Warning:Null value is eliminated by an aggregate or other SET operation.(8153)

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    yeah it's not evaluating a null field correctly you'll have to correct for that in your formula, does the query run on the SQL side? Can you post your SQL statement? I'm not sure if you are using a function/stored procedure to get the fulladdress or whether you're calculating it within the query itself. I suspect you are encountering this error on any row where one of the fields in the formula is null.

  5. #5
    jerem is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    88
    That's the SQL for the view. Nothing calculated after that.

    Code:
    SELECT DISTINCT 
                          dbo.tblPersonal.NbEmployee, dbo.tblPersonal.LegalName, dbo.tblPersonal.FullName, dbo.tblPersonal.StartDate, tblPersonal_1.LocationID, 
                          dbo.tblPersonal.WorkEmail, dbo.tblPersonalDetails.HKIDnb, dbo.tblPersonalDetails.VisaNb, dbo.tblPersonalDetails.VisaExpiryDate, dbo.tblPersonalDetails.Gender, 
                          dbo.tblPersonalDetails.BirthDate, dbo.tblPersonalDetails.MartialStatus, dbo.tblNationality.Nationality, ISNULL(CAST(dbo.tblPersonalDetails.Address AS nvarchar(100)) 
                          + N' ', N' ') + ISNULL(CAST(dbo.tblPersonalDetails.City AS nvarchar(100)) + N' ', N' ') + ISNULL(CAST(dbo.tblPersonalDetails.StateProvince AS nvarchar(100)) + N' ', N' ')
                           + ISNULL(CAST(dbo.tblPersonalDetails.ZIPPostal AS nvarchar(100)) + N' ', N' ') + ISNULL(CAST(dbo.tblPersonalDetails.CountryRegion AS nvarchar(100)), N' ') 
                          AS FullAddress, dbo.tblPersonalDetails.HomeNumber, dbo.tblPersonalDetails.MobileNumber, dbo.tblDegree.Degree, dbo.tblUniversity.University, 
                          dbo.tblPersonalDegree.GraduationYear, dbo.tblMembership.Membership, dbo.tblStrategies.Strategies, dbo.tblDepartment.DepartmentName, 
                          dbo.tblPosition.Investment, dbo.tblFunctionalTitle.FunctionalTitle, dbo.tblCorporateTitle.CorporateTitle, tblPersonal_1.FullName AS LineManager, 
                          dbo.tblPosition.EmploymentType, dbo.tblPosition.PositionStartDate, dbo.tblPosition.TransferDate, dbo.tblPosition.PromotionDate, dbo.tblPosition.ContractEndDate, 
                          dbo.tblTerminationReason.Reason, dbo.tblPosition.TerminationDate, dbo.tblPosition.ResignationDate, dbo.tblPersonal.LastDay, dbo.tblPosition.Probabtion, 
                          dbo.tblPosition.Notice, dbo.tblPosition.NonCompete, dbo.tblPosition.CurrentPosition, dbo.tblPersonal.LastDay AS LastDayPerm, dbo.tblTraining.ID AS TrainingNb, 
                          dbo.tblTraining.DateApplied, dbo.tblTrainingType.TypeName, dbo.tblTraining.MCDetails, dbo.tblTrainInstit.InstitName, dbo.tblTraining.DateFrom, 
                          dbo.tblTraining.DateTo, dbo.tblStatus.Status, dbo.tblTraining.DateApproved, dbo.tblTraining.Vested, dbo.tblTraining.DateVesTill, dbo.tblTraining.Notes, 
                          dbo.tblCurrency.Currency, dbo.tblTraining.AmountR
    FROM         dbo.tblTrainingType RIGHT OUTER JOIN
                          dbo.tblTerminationReason RIGHT OUTER JOIN
                          dbo.tblPersonal AS tblPersonal_1 RIGHT OUTER JOIN
                          dbo.tblDepartment RIGHT OUTER JOIN
                          dbo.tblCorporateTitle RIGHT OUTER JOIN
                          dbo.tblFunctionalTitle RIGHT OUTER JOIN
                          dbo.tblNationality RIGHT OUTER JOIN
                          dbo.tblUniversity RIGHT OUTER JOIN
                          dbo.tblStrategies RIGHT OUTER JOIN
                          dbo.tblPersonal LEFT OUTER JOIN
                          dbo.tblDegree RIGHT OUTER JOIN
                          dbo.tblPersonalDegree ON dbo.tblDegree.ID = dbo.tblPersonalDegree.DegreeID ON dbo.tblPersonal.ID = dbo.tblPersonalDegree.PersonalID LEFT OUTER JOIN
                          dbo.tblPersonalDetails ON dbo.tblPersonal.ID = dbo.tblPersonalDetails.PersonalID RIGHT OUTER JOIN
                          dbo.tblPosition ON dbo.tblPersonal.ID = dbo.tblPosition.PersonalID ON dbo.tblStrategies.ID = dbo.tblPosition.StategyID ON 
                          dbo.tblUniversity.ID = dbo.tblPersonalDegree.UniversityID ON dbo.tblNationality.ID = dbo.tblPersonalDetails.NationalityID LEFT OUTER JOIN
                          dbo.tblMembership RIGHT OUTER JOIN
                          dbo.tblPersonalMembership ON dbo.tblMembership.ID = dbo.tblPersonalMembership.MembershipID ON 
                          dbo.tblPersonal.ID = dbo.tblPersonalMembership.PersonalID ON dbo.tblFunctionalTitle.ID = dbo.tblPosition.FunctionalTitleID ON 
                          dbo.tblCorporateTitle.ID = dbo.tblFunctionalTitle.CorpTitleID ON dbo.tblDepartment.ID = dbo.tblPosition.DepartmentID ON 
                          tblPersonal_1.ID = dbo.tblPosition.ReportingToID ON dbo.tblTerminationReason.ID = dbo.tblPosition.TerminationReasonID LEFT OUTER JOIN
                          dbo.tblTraining ON dbo.tblPersonal.ID = dbo.tblTraining.PersonalID ON dbo.tblTrainingType.ID = dbo.tblTraining.Type LEFT OUTER JOIN
                          dbo.tblTrainInstit ON dbo.tblTraining.GovBody = dbo.tblTrainInstit.ID LEFT OUTER JOIN
                          dbo.tblStatus ON dbo.tblTraining.Status = dbo.tblStatus.ID LEFT OUTER JOIN
                          dbo.tblCurrency ON dbo.tblTraining.Currency = dbo.tblCurrency.ID

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok, so does this query produce results when you run it in SQL? With no errors produced?

    Am I right in thinking you are just trying to create one long string with all the address information with this concantenation or is there something I'm missing?

  7. #7
    jerem is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    88
    Exactly. You are correct. Just trying to concatenate all the address information. It runs fine in SQL.

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

Similar Threads

  1. MDE TO MDB conversion
    By cooglerj in forum Import/Export Data
    Replies: 2
    Last Post: 05-05-2014, 10:58 AM
  2. Upload Failed
    By Scott.Pritchard in forum Forum Suggestions
    Replies: 1
    Last Post: 05-16-2013, 07:29 AM
  3. configuration failed
    By PURAN in forum Access
    Replies: 1
    Last Post: 05-15-2012, 03:39 PM
  4. Sql statement WHERE in VBA failed to run
    By access kid in forum Programming
    Replies: 5
    Last Post: 03-13-2012, 02:03 PM
  5. UM Conversion
    By Rawb in forum Database Design
    Replies: 5
    Last Post: 01-24-2011, 04:02 PM

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