?
Solved

Converting Varchar Value to DataType Int

Posted on 2011-09-27
4
Medium Priority
?
313 Views
Last Modified: 2012-05-12
I am getting the following Microsoft Visual Basic RunTime Error '245': Conversion failed when converting the varchar value '[Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family]' to data type int. When running the following code.  Its driving me nuts as the value being passed is 108 and the field Family is data type INT as well as I have changed the form to reflect that it is a numeric field.

strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"
    strSQL = strSQL & " SELECT Convert(int,'[Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family]'), "
    strSQL = strSQL & "'[Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate]';"

    DoCmd.RunSQL (strSQL)

Open in new window

0
Comment
Question by:JasBrad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 36712598
Try this.
strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"    

strSQL = strSQL & " SELECT Convert(int,' & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & '), "    
strSQL = strSQL & "'" & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] & "';"

DoCmd.RunSQL (strSQL)

Open in new window

0
 
LVL 34

Accepted Solution

by:
Norie earned 1000 total points
ID: 36712608
Oops, missing a quote mark.

strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"    
strSQL = strSQL & " SELECT Convert(int,'" & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & "'), "   
strSQL = strSQL & "'" & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] & "';"
DoCmd.RunSQL (strSQL)
0
 

Author Comment

by:JasBrad
ID: 36712714
Phew! that solved my problem with Family now its giving me a problem with AdmissionDate but that might be a database issue.  Runtime error 206:  Operand Type Clash: int is incompatible with date.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36713366
The problem is probably due to the difference in the way dates are stored in Access and SQL Server.

That's assuming the AdmissionDate is a datetime field.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question