Solved

Converting Varchar Value to DataType Int

Posted on 2011-09-27
4
304 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 33

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 33

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 33

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now