hulent
asked on
Runtime Error 3464 Data Mismatch Type
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));"
You look for '1/1/200', but the dates you give for an example are all '10/1/08' - the years do not match.
ASKER
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_COHBegPer iod = [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_COHBegPer iod = [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_ItemCont Period = [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_ItemCont YTD = [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_UnItemCo ntPeriod = [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_UnItemCo ntYTD = [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_ItemDisb Period = 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_ItemDisb YTD = 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_UnItemDi sbPeriod = 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_UnItemDi sbYTD = abs([17B_YTD].sum);"
DoCmd.RunSQL SQL
SQL = "Drop table 17B_YTD"
DoCmd.RunSQL SQL
'------------------------- ---------- ---------- ---------
SQL = "Update SummaryReport set 15c_Period = SummaryReport.[15A_ItemCon tPeriod] + SummaryReport.[15B_UnItemC ontPeriod] ;"
DoCmd.RunSQL SQL
SQL = "Update SummaryReport set 15c_YTD = SummaryReport.[15A_ItemCon tYTD] + SummaryReport.[15B_UnItemC ontYTD];"
DoCmd.RunSQL SQL
SQL = "Update SummaryReport set 16_Period = SummaryReport.[15c_Period] + SummaryReport.[13_COHBegPe riod];"
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_ItemDis bPeriod] + SummaryReport.[17B_UnItemD isbPeriod] ;"
DoCmd.RunSQL SQL
SQL = "Update SummaryReport set 17c_YTD = SummaryReport.[17a_ItemDis bYTD] + SummaryReport.[17B_UnItemD isbYTD];"
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
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_COHBegPer
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_COHBegPer
'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_ItemCont
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_ItemCont
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_UnItemCo
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_UnItemCo
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_ItemDisb
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_ItemDisb
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_UnItemDi
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_UnItemDi
DoCmd.RunSQL SQL
SQL = "Drop table 17B_YTD"
DoCmd.RunSQL SQL
'-------------------------
SQL = "Update SummaryReport set 15c_Period = SummaryReport.[15A_ItemCon
DoCmd.RunSQL SQL
SQL = "Update SummaryReport set 15c_YTD = SummaryReport.[15A_ItemCon
DoCmd.RunSQL SQL
SQL = "Update SummaryReport set 16_Period = SummaryReport.[15c_Period]
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_ItemDis
DoCmd.RunSQL SQL
SQL = "Update SummaryReport set 17c_YTD = SummaryReport.[17a_ItemDis
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER
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!
No worries - glad to help.