?
Solved

Converting Varchar Value to DataType Int

Posted on 2011-09-27
4
Medium Priority
?
316 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
  • 3
4 Comments
 
LVL 35

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 35

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 35

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

840 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