Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2018, 10:44 PM   #1
dcmphi
Newly Registered User
 
Join Date: Sep 2016
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
dcmphi is on a distinguished road
runtime error 3464 data type mismatch

I have table with 4 field (1) ID (Primary Auto Increment) (2) TDate (3) BillNO (4) Taka (5) Vat and i have a form with text box "txtID", "txtTDate", "txtBillNo", "txtTaka", "txtVat" an a update button. I want to update record through the from with event procedure. i use the following code to update the record but i got an error "runtime error 3464 data type mismatch" Can anyone help me
<code>
CurrentDb.Execute "UPDATE tblTransaction " & _
" SET [TDate] = '" & Me.txtTDate & "'" & _
", [BillNo] = " & Me.txtBillNo & "" & _
", [TotalTaka] = " & Me.txtTotalTaka & "" & _
", [Vat] = '" & Me.txtVat & "'" & _
" WHERE [ID] = ' " & Me.txtID.Tag & " '"
</code>

dcmphi is offline   Reply With Quote
Old 05-17-2018, 11:29 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,547
Thanks: 55
Thanked 2,084 Times in 1,996 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: runtime error 3464 data type mismatch

If tdate is date/time data type:

CurrentDb.Execute "UPDATE tblTransaction " & _
" SET [TDate] = #" & Format(Me.txtTDate, "mm/dd/yyyy") & "# " & _
", [BillNo] = " & Me.txtBillNo & _
", [TotalTaka] = " & Me.txtTotalTaka & _
", [Vat] = " & Me.txtVat & _
" WHERE [ID] = " & Me.txtID.Tag
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 05-18-2018, 12:34 AM   #3
dcmphi
Newly Registered User
 
Join Date: Sep 2016
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
dcmphi is on a distinguished road
Re: runtime error 3464 data type mismatch

Thank you @arnelgp for reply. I want to format my BillNo as "123/1245" How can i do this format.

dcmphi is offline   Reply With Quote
Old 05-18-2018, 01:58 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,547
Thanks: 55
Thanked 2,084 Times in 1,996 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: runtime error 3464 data type mismatch

Yiu need to edit the table and form put input mask 000\/0000
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 05-18-2018, 03:11 AM   #5
dcmphi
Newly Registered User
 
Join Date: Sep 2016
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
dcmphi is on a distinguished road
Re: runtime error 3464 data type mismatch

Thanks @arnelgp.

How to insert too many fields like
<code>
CurrentDb.Execute "INSERT INTO tblContactData ([RecordType],[FISubjectCode],[FIContractCode],[ContractType],[ContractPhase],[ContractStatus],[CurrencyCode],StartingDate],[RequestDate],[PlannedEndDate],[ActualEndDate],[DefaultStatus],[DateofLastPaynt],[FlagSubsidizedCredit],[FlagPreFinanceofLoan],[CodeReorganizedCredit],[ThirdPartyGuaranteeType],[SecurityType],[AmountGuaranteed],[AmountGuaranteedSecuritTtype],[BasisForClassification],[SanctionLimit],[TotalOutstanding],[DayntDelay],[OverdueAmount],[ReportingPeriod],[CumulativeRecovery],[DateOfLawSuit],[DateOfClassification],[NoTiRescheduling],[DateOfLastReschedul],[EconomicPurposeCode],[SME],[EnterpriseType],[TypeOfLink],[FIPrimaryCode],[FISecondaryCode],VALUES ('" & Me.RecordType& "','" & Me.FISubjectCode& "','" & Me.FIContractCode& "','" & Me.ContractType& "','" & Me.ContractPhase& "','" & Me.ContractStatus& "','" & Me.CurrencyCode& "',StartingDate& "','" & Me.RequestDate& "','" & Me.PlannedEndDate& "','" & Me.ActualEndDate& "','" & Me.DefaultStatus& "','" & Me.DateofLastPaynt& "','" & Me.FlagSubsidizedCredit& "','" & Me.FlagPreFinanceofLoan& "','" & Me.CodeReorganizedCredit& "','" & Me.ThirdPartyGuaranteeType& "','" & Me.SecurityType& "','" & Me.AmountGuaranteed& "','" & Me.AmountGuaranteedSecuritTtype& "','" & Me.BasisForClassification& "','" & Me.SanctionLimit& "','" & Me.TotalOutstanding& "','" & Me.DayntDelay& "','" & Me.OverdueAmount& "','" & Me.ReportingPeriod& "','" & Me.CumulativeRecovery& "','" & Me.DateOfLawSuit& "','" & Me.DateOfClassification& "','" & Me.NoTiRescheduling& "','" & Me.DateOfLastReschedul& "','" & Me.EconomicPurposeCode& "','" & Me.SME& "','" & Me.EnterpriseType& "','" & Me.TypeOfLink& "','" & Me.FIPrimaryCode& "','" & Me.FISecondaryCode& "')"
</code>
dcmphi is offline   Reply With Quote
Old 05-18-2018, 03:28 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,526
Thanks: 132
Thanked 1,492 Times in 1,464 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: runtime error 3464 data type mismatch

This looks like an unbound form. To make this more readable and usable I would use a parameter query this will also remove the need for complicated string formatting - the syntax is along the lines of;

Code:
const SQL_INSERT as string = _
   "INSERT INTO tTable " & _
      "( numField, strField, datField ) " & _
   "VALUES " & _
      "( p0, p1, p2 ) "

with currentdb.createquerydef("", SQL_INSERT)
   .parameters("p0") = 123
   .parameters("p1") = "test"
   .parameters("p2") = #3/7/17#
   .execute dbFailOnError
   .close
end with
Or save the query then use that like this;
Code:
Set db = CurrentDb
Set qdf = db.QueryDefs("SavedQueryName")

' BIND VALUES TO PARAMETERS
qdf.Parameters("Parameter1") = Me.cmbFirstParam
qdf.Parameters("Parameter2") = Me.txtboxSecondParam 
qdf.Parameters("Parameter3") = Me.ListBoxParam3
etc, etc

Set rst = qdf.OpenRecordset
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-18-2018, 03:37 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,547
Thanks: 55
Thanked 2,084 Times in 1,996 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: runtime error 3464 data type mismatch

I wrote a function sometimes ago, same as with minty's code, fnAnySQL().
https://www.access-programmers.co.uk...light=Fnanysql

copy and paste it in a Module:

then you need only to call it:

Call fnAnySQL( _
"INSERT INTO tblContactData ([RecordType],[FISubjectCode],[FIContractCode],[ContractType],[ContractPhase],[ContractStatus],[CurrencyCode],StartingDate],[RequestDate],[PlannedEndDate],[ActualEndDate],[DefaultStatus],[DateofLastPaynt],[FlagSubsidizedCredit],[FlagPreFinanceofLoan],[CodeReorganizedCredit],[ThirdPartyGuaranteeType],[SecurityType],[AmountGuaranteed],[AmountGuaranteedSecuritTtype],[BasisForClassification],[SanctionLimit],[TotalOutstanding],[DayntDelay],[OverdueAmount],[ReportingPeriod],[CumulativeRecovery],[DateOfLawSuit],[DateOfClassification],[NoTiRescheduling],[DateOfLastReschedul],[EconomicPurposeCode],[SME],[EnterpriseType],[TypeOfLink],[FIPrimaryCode],[FISecondaryCode] SELECT @1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15 ,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@ 28,@29,@30,@31,@32,@33,@34,@35,@36,@37", _
Me.RecordType,Me.FISubjectCode,Me.FIContractCode,M e.ContractType,Me.ContractPhase,Me.ContractStatus, Me.CurrencyCode,StartingDate,Me.RequestDate,Me.Pla nnedEndDate,Me.ActualEndDate, Me.DefaultStatus,Me.DateofLastPaynt,Me.FlagSubsidi zedCredit,Me.FlagPreFinanceofLoan,Me.CodeReorganiz edCredit,Me.ThirdPartyGuaranteeType,Me.SecurityTyp e,Me.AmountGuaranteed,Me.AmountGuaranteedSecuritTt ype,Me.BasisForClassification,Me.SanctionLimit,Me. TotalOutstanding,Me.DayntDelay,Me.OverdueAmount,Me .ReportingPeriod,Me.CumulativeRecovery,Me.DateOfLa wSuit,Me.DateOfClassification,Me.NoTiRescheduling, Me.DateOfLastReschedul,Me.EconomicPurposeCode,Me.S ME,Me.EnterpriseType,Me.TypeOfLink,Me.FIPrimaryCod e,Me.FISecondaryCode)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 05-18-2018, 04:13 AM   #8
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,191
Thanks: 11
Thanked 682 Times in 641 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: runtime error 3464 data type mismatch

Quote:
Originally Posted by dcmphi View Post

How to insert too many fields like...
Why are you using an Unbound Form?

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to
  1. Do Data Validation
  2. Prevent Duplicate Records
  3. Do Formatting of Data before it's Saved
  4. Decide whether or not to actually Save a New or Edited Record

which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.
  • You can create an EXE file which gives total protection to your code/design
  • You can distribute the db to PCs without a copy of Access being on board
  • Your data security is far, far better than anything you can do in Access

Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything, has simply made a bad choice in deciding to work in Access.

Linq ;0)>

__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Sort numeric in alpha-numeric text (runtime error 3464) Data type mismatch criteria Muaz Forms 3 10-29-2014 07:42 AM
[SOLVED] Runtime Error 3464 - Data Type Mismatch in Criteria Expression maff811 Modules & VBA 3 05-01-2014 12:10 PM
Odd runtime error 3464 - Data Type mismatch msblane Modules & VBA 9 03-18-2014 11:19 PM
Error 3464 Data type mismatch mistera Queries 10 01-06-2012 06:48 AM
Error 3464 - Data type mismatch kaybowen Modules & VBA 3 05-28-2011 03:22 AM




All times are GMT -8. The time now is 06:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World