Solved

Runtime Error 3464 Data Mismatch Type

Posted on 2008-10-14
6
810 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

947 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

20 Experts available now in Live!

Get 1:1 Help Now