Solved

Data Type Missmatch in Criteria Expression - VB [Update] Statement

Posted on 2007-03-27
6
279 Views
Last Modified: 2008-02-01
I've got this piece of code built in my database.  It is supposed to update a field in the DecisionData table, but I'm getting a Data Type Mismatch error.  From what I can tell, all of the data types match, but Access is disagreeing.
Variable AD is a boolean in the code.  I assign it to True.  In the table the field tblAccDec is set to Yes/No type.
Variable INQN comes from a form.  The data type in the form is set to General Number.  The number comes from another piece of code that uses a data type of Variant.  In the table, the field InquiryNumber is set to type Number.  I must be missing something.  Any info or ideas that anyone has would be greatly apprecieated.  Thanks.

Public Sub PreAppAcceptancebtn_Click()

Dim AD As Boolean
Dim INQN As Variant

AD = True
INQN = Forms!VisaOnDemand!frmInquiryNumber
 
    CurrentDb.Execute "UPDATE  [DecisionData] SET tblAccDec =  '" & AD & "' WHERE InquiryNumber =  '" & Forms!VisaOnDemand!frmInquiryNumber & "'"
    CurrentDb.Execute "UPDATE  [DecisionData] SET tblAccAmt =  '" & frmAccAmount & "' WHERE InquiryNumber =  '" & Forms!VisaOnDemand!frmInquiryNumber & "'"

End Sub
0
Comment
Question by:bdbud
6 Comments
 
LVL 75
ID: 18804646
Well,  in code ... the error occurs on:

'" & frmAccAmount & "'

because frmAccAmount >>> 'variable is undefined'

so ... the syntax here appears not to be correct ..

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18804729
Separate the two  CurrentDB.Execute actions. Is the first one just a long string on the same line or does it extend to two lines.  If the latter:

CurrentDb.Execute "UPDATE  [DecisionData] SET tblAccDec =  '" & AD & "' WHERE " _
& "InquiryNumber =  '" & Forms!VisaOnDemand!frmInquiryNumber & "';"
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 18805241
>field tblAccDec is set to Yes/No type
Your data type mismatch is due to the quotes you are using to delimit this field.
For booleans, the syntax is fldBool = True (as opposed to fldBool = "true")
Treat it like a numeric... no single quotes.

>In the table, the field InquiryNumber is set to type Number.
Numeric types have no delimiters.  The single quotes are used to delimit text.

CurrentDb.Execute "UPDATE  [DecisionData] SET tblAccDec =  " & AD & " WHERE InquiryNumber =  " & Forms!VisaOnDemand!frmInquiryNumber


Remove the single quotes from this one as well (I'm treating tblAccAmt as numeric and dropped the quotes around it too):

CurrentDb.Execute "UPDATE  [DecisionData] SET tblAccAmt = " & frmAccAmount & " WHERE InquiryNumber =  " & Forms!VisaOnDemand!frmInquiryNumber

 The syntax for  frmAccAmount  would be okay if this were referring to a control on your form (Me.frmAccAmount) would be better.  The standard prefix "frm" is used for forms (txt for textboxes, cbo for combos, etc).  In your code frmAccAmount and Forms!VisaOnDemand!frmInquiryNumber look like they reallyshould be referring to textboxes, combos etc -- but the naming convention is implying forms.  As DatabaseMX noted, this doesn't look quite right.

Please double-check the names of those controls (or variables).
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 61

Expert Comment

by:mbizup
ID: 18805270
This URL shows the Reddick VBA naming convention, which is very popular and commonly used at this site with individual variations:
http://www.xoc.net/standards/rvbanc.asp
0
 

Author Comment

by:bdbud
ID: 18810006
Thanks!! Removing the single quotes from both of the statements resolved the issue.  Thanks for the link to the naming convention also.  I'm still fairly new, so I'm still working on 'best practices'.  Thanks for your help.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18810014
Glad to help :-)
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

832 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