Solved

Runtime Error 3464 Data Mismatch Type

Posted on 2008-10-14
6
821 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 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

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.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

730 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