Solved

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

Posted on 2007-03-27
6
277 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
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)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

920 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

15 Experts available now in Live!

Get 1:1 Help Now