Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-03-27
6
Medium Priority
?
287 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
[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
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 1000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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