Solved

Runtime Error 3464 Data Mismatch Type

Posted on 2008-10-14
6
815 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

815 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

11 Experts available now in Live!

Get 1:1 Help Now