?
Solved

Runtime Error 3464 Data Mismatch Type

Posted on 2008-10-14
6
Medium Priority
?
835 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
[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
  • 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 1000 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

743 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