Solved

Converting Varchar Value to DataType Int

Posted on 2011-09-27
4
311 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 250 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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