Solved

Runtime Error 3464 Data Mismatch Type

Posted on 2008-10-14
6
820 Views
Last Modified: 2013-11-28
I have a MS Access file that runs reports from a .CSV file.  I keep getting an error when I put the Date Range from 1/1/08 to anthing past 9/30/08.  When the date is 10/1/08 or beyond, I get the Runtime Error.  Here is the code.  Any help is greatly appreciated.  Thank you.
SQL = "Insert into 15A_YTD (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (Data.Amount>0) AND (Data.Itemized='x')And (Data.[Close Date])<=[basic info].[Current Report Through Date] And (Data.[Close Date])>= ('1/1/200' + Mid([basic info].[Current Report Through Date],InStr([basic info].[Current Report Through Date],'200')+3,1));"

Open in new window

0
Comment
Question by:hulent
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 22713409
You look for '1/1/200', but the dates you give for an example are all '10/1/08' - the years do not match.
0
 
LVL 1

Author Comment

by:hulent
ID: 22713677
I am sorry, I didn't realize I only posted the line in question.  Here is the whole thing:

Option Compare Database

'Notes:  Can't use a sum() function in an update, so you must use insert.  Insert for some reason won't span lines.



Function createSummaryTable()
Dim SQL As String

'Turn Warnings Off
DoCmd.SetWarnings False
SQL = "Drop table SummaryReport"

DoCmd.RunSQL SQL

SQL = "Create table SummaryReport (1_Committee text, 2_Acronym text, 3_Telephone text, 4_Street text, 5_CityStateZip text, 6_CommitteeParty text, 7_Candidate text, 8_CandidateParty text, 9_OfficeSought text, 10_County text, 12_PreviousFrom Date, 12_PreviousThrough Date, 12_CurrentFrom Date, 12_CurrentThrough Date, 13_COHBegPeriod currency, 14_COHJan1 currency, 15A_ItemContPeriod currency, 15A_ItemContYTD currency, 15B_UnItemContPeriod currency, 15B_UnItemContYTD currency, 17A_ItemDisbPeriod currency, 17A_ItemDisbYTD currency, 17B_UnItemDisbPeriod currency, 17B_UnItemDisbYTD currency, 15c_Period currency, 15c_YTD currency, 16_Period currency, 16_YTD currency, 17c_Period currency, 17c_YTD currency, 18_Period currency, 18_YTD currency, 19_DebtOwedBy currency, 20_DebtOwedTo currency, TotalPages number, PP text, PE text, A text, F text, O text, PostCon text, PreCon text)"
       
DoCmd.RunSQL SQL

'insert basic information into summaryreport table
'note there can only be one row in the basic info table for this to work
SQL = "Insert into SummaryReport (1_committee, 3_Telephone, 4_Street, 5_CityStateZip, 6_CommitteeParty, 7_Candidate, 8_CandidateParty, 9_OfficeSought, 10_County, 12_PreviousFrom, 12_PreviousThrough, 12_CurrentFrom, 12_CurrentThrough) select [Full  Name of Committee (1)], [Commitee Telephone Number (3)], [Mailing Address (4)], [City, State, Zip Code (5)], [Party Affiliation (6)], [Full Name of Candidate (7)], [Party Affiliation (6)], [Office Sought (9)], [County of Residence (10)], [Last Report From Date], [Last Report ThroughDate], [Current Report From Date],[Current Report Through Date] from [Basic Info]"

DoCmd.RunSQL SQL

'---------------------------------------------------
'Update Cash on Hand, Beginning of Period
SQL = "Update SummaryReport, [basic info] SET SummaryReport.13_COHBegPeriod = [basic info].[Current (13) Reported Cash on Hand Beginning of Period];"

DoCmd.RunSQL SQL

'SQL = "Create Table 13_ThisPeriod (Sum currency, type text)"
'DoCmd.RunSQL SQL
'SQL = "Insert into 13_ThisPeriod (sum, type) select Sum(amount), 'SumAmount' FROM Data, [basic info]WHERE (Data.[Close Date]>=[basic info].[Last Report From Date]) And (Data.[Close Date]<=[basic info].[Last Report ThroughDate]);"
'DoCmd.RunSQL SQL
'SQL = "Insert into 13_ThisPeriod (sum, type) select [Previous (13) Reported Cash on Hand Beginning of Period], 'COH_BegPeriod' FROM [basic info];"
'DoCmd.RunSQL SQL
'SQL = "Insert into 13_ThisPeriod (sum, type) select sum([sum]), 'COH_ClosePeriod' from 13_ThisPeriod;"
'DoCmd.RunSQL SQL
'SQL = "    Delete from 13_ThisPeriod where type <> 'COH_ClosePeriod'   "
'DoCmd.RunSQL SQL
'SQL = "Update SummaryReport, 13_ThisPeriod SET SummaryReport.13_COHBegPeriod = [13_ThisPeriod].sum;"
'DoCmd.RunSQL SQL
'SQL = "Drop table 13_ThisPeriod"
'DoCmd.RunSQL SQL

'---------------------------------------------------
'Update Cash on Hand, Jan 1
SQL = "UPDATE [Basic Info], SummaryReport SET SummaryReport.14_COHJan1 = [Basic Info].[current 14];"

DoCmd.RunSQL SQL

'----------------------------------------------------

'15a This Period

SQL = "Create Table 15A_ThisPeriod (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 15A_ThisPeriod (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (((Data.Amount)>0) AND ((Data.Itemized)='x') AND ((Data.[Close Date])>=[basic info].[Current Report From Date] And (Data.[Close Date])<=[basic info].[Current Report Through Date]));"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 15A_ThisPeriod SET SummaryReport.15A_ItemContPeriod = [15A_ThisPeriod].sum;"

DoCmd.RunSQL SQL

SQL = "Drop table 15A_ThisPeriod"

DoCmd.RunSQL SQL
'------------------------------------------------------

'15a YTD

SQL = "Create Table 15A_YTD (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 15A_YTD (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (Data.Amount>0) AND (Data.Itemized='x')And (Data.[Close Date])<=[basic info].[Current Report Through Date] And (Data.[Close Date])>= ('1/1/200' + Mid([basic info].[Current Report Through Date],InStr([basic info].[Current Report Through Date],'200')+3,1));"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 15A_YTD SET SummaryReport.15A_ItemContYTD = [15A_YTD].sum;"

DoCmd.RunSQL SQL

SQL = "Drop table 15A_YTD"

DoCmd.RunSQL SQL



'----------------------------------------------------

'15b This Period

SQL = "Create Table 15B_ThisPeriod (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 15B_ThisPeriod (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (((Data.Amount)>0) AND ((Data.Itemized) is null) AND ((Data.[Close Date])>=[basic info].[Current Report From Date] And (Data.[Close Date])<=[basic info].[Current Report Through Date]));"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 15B_ThisPeriod SET SummaryReport.15B_UnItemContPeriod = [15B_ThisPeriod].sum;"

DoCmd.RunSQL SQL

SQL = "Drop table 15B_ThisPeriod"

DoCmd.RunSQL SQL
'------------------------------------------------------

'15b YTD

SQL = "Create Table 15B_YTD (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 15B_YTD (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (Data.Amount>0) AND (Data.Itemized is null) And (Data.[Close Date])<=[basic info].[Current Report Through Date] And (Data.[Close Date])>= ('1/1/200' + Mid([basic info].[Current Report Through Date],InStr([basic info].[Current Report Through Date],'200')+3,1));"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 15B_YTD SET SummaryReport.15B_UnItemContYTD = [15B_YTD].sum;"

DoCmd.RunSQL SQL

SQL = "Drop table 15B_YTD"

DoCmd.RunSQL SQL

'----------------------------------------------------

'17a This Period

SQL = "Create Table 17A_ThisPeriod (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 17A_ThisPeriod (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (((Data.Amount)<0) AND ((Data.Itemized)='x') AND ((Data.[Close Date])>=[basic info].[Current Report From Date] And (Data.[Close Date])<=[basic info].[Current Report Through Date]));"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 17A_ThisPeriod SET SummaryReport.17A_ItemDisbPeriod = abs([17A_ThisPeriod].sum);"

DoCmd.RunSQL SQL

SQL = "Drop table 17A_ThisPeriod"

DoCmd.RunSQL SQL
'------------------------------------------------------

'17a YTD

SQL = "Create Table 17A_YTD (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 17A_YTD (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (Data.Amount<0) AND (Data.Itemized='x') And (Data.[Close Date])<=[basic info].[Current Report Through Date] And (Data.[Close Date])>= ('1/1/200' + Mid([basic info].[Current Report Through Date],InStr([basic info].[Current Report Through Date],'200')+3,1));"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 17A_YTD SET SummaryReport.17A_ItemDisbYTD = abs([17A_YTD].sum);"

DoCmd.RunSQL SQL

SQL = "Drop table 17A_YTD"

DoCmd.RunSQL SQL


'----------------------------------------------------

'17b This Period

SQL = "Create Table 17B_ThisPeriod (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 17B_ThisPeriod (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (((Data.Amount)<0) AND ((Data.Itemized) is null) AND ((Data.[Close Date])>=[basic info].[Current Report From Date] And (Data.[Close Date])<=[basic info].[Current Report Through Date]));"
                                           
DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 17B_ThisPeriod SET SummaryReport.17B_UnItemDisbPeriod = abs([17B_ThisPeriod].sum);"

DoCmd.RunSQL SQL

SQL = "Drop table 17B_ThisPeriod"

DoCmd.RunSQL SQL
'------------------------------------------------------

'17b YTD

SQL = "Create Table 17B_YTD (Sum currency)"

DoCmd.RunSQL SQL

SQL = "Insert into 17B_YTD (sum) select iif(Sum(amount) is null, 0, Sum(Amount)) FROM Data, [basic info]WHERE (Data.Amount<0) AND (Data.Itemized is null) And (Data.[Close Date])<=[basic info].[Current Report Through Date] And (Data.[Close Date])>= ('1/1/200' + Mid([basic info].[Current Report Through Date],InStr([basic info].[Current Report Through Date],'200')+3,1));"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport, 17B_YTD SET SummaryReport.17B_UnItemDisbYTD = abs([17B_YTD].sum);"

DoCmd.RunSQL SQL

SQL = "Drop table 17B_YTD"

DoCmd.RunSQL SQL
'------------------------------------------------------

SQL = "Update SummaryReport set 15c_Period = SummaryReport.[15A_ItemContPeriod] + SummaryReport.[15B_UnItemContPeriod];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 15c_YTD = SummaryReport.[15A_ItemContYTD] + SummaryReport.[15B_UnItemContYTD];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 16_Period = SummaryReport.[15c_Period] + SummaryReport.[13_COHBegPeriod];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 16_YTD = SummaryReport.[15c_YTD] + SummaryReport.[14_COHJan1];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 17c_Period = SummaryReport.[17a_ItemDisbPeriod] + SummaryReport.[17B_UnItemDisbPeriod];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 17c_YTD = SummaryReport.[17a_ItemDisbYTD] + SummaryReport.[17B_UnItemDisbYTD];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 18_Period = SummaryReport.[16_Period] - SummaryReport.[17c_Period];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 18_YTD = SummaryReport.[16_YTD] - SummaryReport.[17c_YTD];"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 19_DebtOwedBy = 0;"

DoCmd.RunSQL SQL

SQL = "Update SummaryReport set 20_DebtOwedTo = 0;"

DoCmd.RunSQL SQL


'------------------do report type
Dim stDocName As String

stDocName = "Annual"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "FinalDisbanding"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "OutgoingTreasurer"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "PreElection"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "PrePrimary"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "PreCon"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "PostCon"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'SQL = "Update SummaryReport, [basic info] set SummaryReport.A = iif([basic info].[Report Type] = 'Annual', 'x', "");"

'DoCmd.RunSQL SQL

End Function


0
 
LVL 29

Accepted Solution

by:
Badotz earned 250 total points
ID: 22713812
Not much help if we can't see the value of the parameters to the query.

If this an Access DB, then you could use

FORMAT([date],"yyyymmdd") >= Format([@PARM_DATE],"yyyymmdd")

or something along those lines.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:hulent
ID: 22713905
That is correct.  I was able to change the date format from MM/dd/YY to MM/dd/YYYY in Regional and Language Settings and the report ran just fine.  I will accept your solution because both of those will work.  Thank you very much for the help!
0
 
LVL 1

Author Closing Comment

by:hulent
ID: 31505977
Thanks again for helping realize the problem.  I would specifiy a little more where to put the FORMAT function in the coding because I am a DB noob.  Thanks Man!
0
 
LVL 29

Expert Comment

by:Badotz
ID: 22713983
No worries - 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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

856 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