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

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
bdbudAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well,  in code ... the error occurs on:

'" & frmAccAmount & "'

because frmAccAmount >>> 'variable is undefined'

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

mx
0
GRayLCommented:
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
mbizupCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mbizupCommented:
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
bdbudAuthor Commented:
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
mbizupCommented:
Glad to help :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.