Conversion Failed

[jerem]

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.:

 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

 

[SQL_Hell]

Hi,

There's nothing in that code example which shows a convert or cast to int, the problem must be somewhere else.

Difficult to help any further with the lack of information

 

[jerem]

Here is the entire SQL for the view. Access connects to that view directly:

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

 

[kevlray]

I took that line, replaced the data values with my best guesses of the types of data you are using. I did not experience any issues, so I can only assume that there is an issue at the form level.

 

[jerem]

I see. I will have a look. thank you.