Solved

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

Posted on 2007-03-27
6
283 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
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!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

679 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