date problem with linked spreadsheet

  • Thread starter Thread starter LindaF
  • Start date Start date
L

LindaF

I have a linked spreeadsheet with a date field. I read each record in this
spreadsheet and write to an Access table.
If the date field in the access table is just set as a date the field is
always set as 31/12/1899.
If I change the field in the table to be a short date the value then
displays a different time for each record but no date. When debugging the
code the date in both the spreadsheet and the field used to populate the
table correctly show the valid date.

Have run out of ideas so would really appreciate any help I can get.

The field I use to populate the Access table is set up as Dim Trandate as Date
rst!Date is the date field from the linked spreadsheet
In the code I have TranDate=rst!Date

If I put # around rst!Date I get a Type Conversion error.
 
One thing that can cause problems is using reserved words as object names.
You have

TranDate=rst!Date


"Date" is a reserved word and a function. This can confuse Access.
For a list of reserved words, see
http://allenbrowne.com/AppIssueBadWord.html


Without more info, it is difficult to determine the problem. Would you post
the VBA you are using?

HTH
 
I changed the name of the field to TranDate and still get the same result.
Below is the code I am using.

Function ImportINGTransactions()

Dim db As Database
Dim rst As Recordset
Dim strFolder As String
Dim strFile As String
Dim strFullPath As String
Dim strTargetFolder As String
Dim strTargetPath As String
Dim strSQL As String
Dim TranDate As Date
Dim strAccount As String
Dim strAccType As String
Dim strCategory As String
Dim strSubCategory As String
Dim strDesc As String
Dim Val As Double

Dim TaxRate As Double


On Error GoTo ErrorHandler

Set db = CurrentDb
TaxRate = 0.15

strFolder = "C:\Users\Public\#SMSF\Accounts\"
strTargetFolder = strFolder & "Imported Files\"

' Find any ING files ready for importing then move to the target folder
strFile = Dir(strFolder & "*ING*.csv", vbNormal)

If strFile = "" Then
MsgBox "No ING Transactions to import", , "Import ING Transactions"
GoTo Done
End If

' Turn off warning messages
DoCmd.SetWarnings False

Do While strFile <> ""
strFullPath = strFolder & strFile
strTargetPath = strTargetFolder & strFile

' Link ING Transactions
' DoCmd.TransferText acLinkDelim, "ING Link", strFullPath, True

strSQL = "SELECT TranDate, Amount, Description from [ING Link] "
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
TranDate = rst!TranDate
strAccount = "9"
strCategory = "'Cash'"
strDesc = "'" & rst!Description & "'"
Val = rst!Amount

Select Case True
Case UCase(strDesc) Like "*INTEREST*"
strAccType = "'Income'"
strSubCategory = "'Interest'"
Call WriteTransaction(rst!TranDate, strAccount,
strAccType, strCategory, strSubCategory, strDesc, Val)

strAccType = "'Accrual'"
strSubCategory = "'Tax'"
strDesc = "'Estimated Interest Income Tax'"
Val = -Val * TaxRate
Case Else
strAccType = "'Misc'"
strSubCategory = "'Transfer'"
End Select

Call WriteTransaction(rst!TranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, Val)
rst.MoveNext
Loop

' Name strFullPath As strTargetPath

strFile = Dir
Loop

Done:
' Turn on warning messages
DoCmd.SetWarnings True
Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Import
Transactions Error"
Resume Done

End Function
 
Note that "Val" is also a reserved word.

Also, another good habit to develop is *do not* use spaces in object names.
Use the underscore char (ING_Link) or camel back (IngLink)

In reviewing your code, it looks like you are linking a TEXT file. A .csv is
at test file that has the values (data) seperated by commas. It is not an
Excel file, even though it opens in Excel when you double click on it.

In the Transfer Text line, you are missing a comma in the command. It should
be

DoCmd.TransferText acLinkDelim, , "ING Link", strFullPath, True


The second argument is the import specification. You can create an import
specification that will ensure the data is the correct data type.

Would you paste a couple of lines of data from the csv file?

If the date field in the access table is just set as a date the field is
always set as 31/12/1899.
If I change the field in the table to be a short date the value then
displays a different time for each record but no date. When debugging the
code the date in both the spreadsheet and the field used to populate the
table correctly show the valid date.
Click to expand...

Because the date always set as 31/12/1899, I think the date from the csv
file is imported as a string. During debugging, it would *look* right, but be
the wrong data type.

Where you have a line of code like this:

TranDate = rst!TranDate

try using a function to convert it to a date type.

Try: TranDate = CDate(rst!TranDate)


It looks like you have a custom insert routine (WriteTransaction), so I
would not turn warnings off in the main code.
What does the code for WriteTransaction() look like?

I did a little rewrite on your code....
'---------------------------------------------
Function ImportINGTransactions()

Dim db As Database
Dim rst As Recordset
Dim strFolder As String
Dim strFile As String
Dim strFullPath As String
Dim strTargetFolder As String
Dim strTargetPath As String
Dim strSQL As String
Dim TranDate As Date
Dim strAccount As String
Dim strAccType As String
Dim strCategory As String
Dim strSubCategory As String
Dim strDesc As String
Dim dVal As Double

Dim TaxRate As Double

On Error GoTo ErrorHandler

Set db = CurrentDb
TaxRate = 0.15

strFolder = "C:\Users\Public\#SMSF\Accounts\"
strTargetFolder = strFolder & "Imported Files\"

' Find any ING files ready for importing then move to the target folder
strFile = Dir(strFolder & "*ING*.csv", vbNormal)

'check file name length
If Len(Trim(Nz(strFile, ""))) > 0 Then

' Turn off warning messages - why??
DoCmd.SetWarnings False

Do While strFile <> ""
strFullPath = strFolder & strFile
strTargetPath = strTargetFolder & strFile

' Link ING Transactions -**** added a comma
' DoCmd.TransferText acLinkDelim, ,"ING Link", strFullPath, True

strSQL = "SELECT TranDate, Amount, Description from [ING Link] "
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
TranDate = CDate(rst!TranDate)
strAccount = "9"
strCategory = "'Cash'"
strDesc = "'" & rst!Description & "'"
dVal = rst!Amount

Select Case True
Case UCase(strDesc) Like "*INTEREST*"
strAccType = "'Income'"
strSubCategory = "'Interest'"

' changed rst!TranDate to TranDate
' Call WriteTransaction(rst!TranDate, strAccount,
strAccType, strCategory, strSubCategory, strDesc, dVal)
Call WriteTransaction(TranDate , strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)

strAccType = "'Accrual'"
strSubCategory = "'Tax'"
strDesc = "'Estimated Interest Income Tax'"
dVal = -dVal * TaxRate
Case Else
strAccType = "'Misc'"
strSubCategory = "'Transfer'"
End Select

' changed rst!TranDate to TranDate
' Call WriteTransaction(rst!TranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)
Call WriteTransaction(TranDate , strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)

rst.MoveNext
Loop

' Name strFullPath As strTargetPath

strFile = Dir
Loop
Else
MsgBox "No ING Transactions to import", , "Import ING Transactions"
End If
Done:
' Clean Up
'close recordset
rst.Close
Set rst = Nothing
Set db = Nothing

' Turn on warning messages
DoCmd.SetWarnings True

Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Import
Transactions Error"
Resume Done

End Function
'-----------------------------------------------


Another option: I don't use DoCmd.TransferText. I had to do calculations
and conversions, so I open the .csv and read each line, doing the calcs and
conversions, then inserting the data into the correct tables. When done, I
close the file.


HTH
 
Did a few changes but still getting a problem with the date.

Sample input csv (permanent linked in Access):
Date, Amount, Description
2/06/2009,-500,WITHDRAWAL - Receipt 123456
31/05/2009,171.62,Bonus Interest - Receipt 987654

Sample Output:
TranDate Account Accounting Type Catagory SubCatagory Description Value
12:00:14 AM 9 Misc Cash Transfer WITHDRAWAL - Receipt 123456 -$500.00
12:04:27 AM 9 Accrual Cash Tax Estimated Interest Income Tax -$25.74
12:04:27 AM 9 Income Cash Interest Bonus Interest - Receipt 987654 $171.62

New Code:
Function ImportINGTransactions()

Dim db As Database
Dim rst As Recordset
Dim strFolder As String
Dim strFile As String
Dim strFullPath As String
Dim strTargetFolder As String
Dim strTargetPath As String
Dim strSQL As String
Dim dtTranDate As Date
Dim strAccount As String
Dim strAccType As String
Dim strCategory As String
Dim strSubCategory As String
Dim strDesc As String
Dim dVal As Double
Dim TaxRate As Double

On Error GoTo ErrorHandler

Set db = CurrentDb
TaxRate = 0.15

strFolder = "C:\Users\Public\#SMSF\Accounts\"

' Find any ING files ready for importing
strFile = Dir(strFolder & "ING.csv", vbNormal)

'check file name length to confirm files exist
If Len(Trim(Nz(strFile, ""))) = 0 Then
'If strFile = "" Then
MsgBox "No ING Transactions to import", , "Import ING Transactions"
GoTo Done
End If

strSQL = "SELECT TranDate, Amount, Description from [ING Link] "
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
dtTranDate = CDate(rst!TranDate)
strAccount = "9"
strCategory = "'Cash'"
strDesc = "'" & rst!Description & "'"
dVal = rst!Amount

Select Case True
Case UCase(strDesc) Like "*INTEREST*"
strAccType = "'Income'"
strSubCategory = "'Interest'"
Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)

strAccType = "'Accrual'"
strSubCategory = "'Tax'"
strDesc = "'Estimated Interest Income Tax'"
dVal = -dVal * TaxRate
Case Else
strAccType = "'Misc'"
strSubCategory = "'Transfer'"
End Select

Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)
rst.MoveNext
Loop

Done:
Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Import
Transactions Error"
Resume Done

End Function

Sub WriteTransaction(dtTranDate As Date, strAccount As String, strAccType As
String, strCategory As String, _
strSubCategory As String, strDesc As String, dVal As
Double)

Dim strSQL As String

On Error GoTo ErrorHandler

' Turn off warning messages so I don't get the "You are adding 1 record
...." each time
DoCmd.SetWarnings False

strSQL = "Insert into [Test Transaction History] "
strSQL = strSQL & "( [TranDate], Account, [Accounting Type], Catagory,
SubCatagory, Description, [Value] ) "
strSQL = strSQL & "select " & dtTranDate & "," & strAccount & "," &
strAccType & ","
strSQL = strSQL & strCategory & "," & strSubCategory & "," & strDesc &
"," & dVal & ";"
DoCmd.RunSQL strSQL

Done:

' Turn off warning messages
DoCmd.SetWarnings True

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,
"WriteTransaction Error"
Resume Done
End Sub
 
I haven't figured out why you have strFolder and strFile if "ING Link" is
"... permanent linked in Access)"

In the table"Test Transaction History", "Value" is a reserved word. Maybe
change it to "Amount".

In the code, string variables had single quotes inside of double quotes. I
removed the single quotes.

ie: strAccType = " ' Income ' " (expanded) changed to strAccType = "
Income "

And in Sub WriteTransaction(), I use "p" to indicate parameter.

I think the problem with the Trandate is not being properly delimited in the
SQL Insert statement in Sub WriteTransaction().

There are two forms of the SQL command "INSERT INTO". One uses a sub query
to select one or more records to insert, and the other form of the command
uses "Values" keyword to insert one record

From the site http://www.1keydata.com/sql/sqlinsert.html :

Quote:
The syntax for inserting data into a table one row at a time is as follows:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

Unquote

When building the SQL string, the values must be properly delimited.



So I created a CSV file and linked it as "ING Link". I created a table
"Test Transaction History".

Using the input date:

Date, Amount, Description
2/06/2009,-500,WITHDRAWAL - Receipt 123456
31/05/2009,171.62,Bonus Interest - Receipt 987654

I get the output:

'TranDate Account Accounting Type Catagory SubCatagory Description Value
'6/2/2009 9 Misc Cash Transfer WITHDRAWAL - Receipt 123456 -500
'5/31/2009 9 Income Cash Interest Bonus Interest - Receipt 987654
171.62
'5/31/2009 9 Accrual Cash Tax Estimated Interest Income Tax -25.743

Note that the dates are in "mm/dd/yyyy" format.

Below is the code I used

'-----------------------------------------
Function ImportINGTransactions()

'using DAO
'need reference set for Microsoft 3.x Object Library
Dim db As Database
Dim rst As Recordset
Dim strFolder As String
Dim strFile As String
Dim strFullPath As String
Dim strTargetFolder As String
Dim strTargetPath As String
Dim strSQL As String
Dim dtTranDate As Date
Dim strAccount As String
Dim strAccType As String
Dim strCategory As String
Dim strSubCategory As String
Dim strDesc As String
Dim dVal As Double
Dim TaxRate As Double

On Error GoTo ErrorHandler

Set db = CurrentDb
TaxRate = 0.15

' strFolder = "C:\Users\Public\#SMSF\Accounts\"
'
' ' Find any ING files ready for importing
' strFile = Dir(strFolder & "ING.csv", vbNormal)
'
' 'check file name length to confirm files exist
' If Len(Trim(Nz(strFile, ""))) = 0 Then
' 'If strFile = "" Then
' MsgBox "No ING Transactions to import", , "Import ING
Transactions"
' GoTo Done
' End If

strSQL = "SELECT TranDate, Amount, Description from [ING Link] "
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
dtTranDate = CDate(rst!TranDate)
strAccount = "9"
strCategory = "Cash"
strDesc = rst!Description
dVal = rst!Amount

Select Case True
Case UCase(strDesc) Like "*INTEREST*"
strAccType = "Income"
strSubCategory = "Interest"
Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)

strAccType = "Accrual"
strSubCategory = "Tax"
strDesc = "Estimated Interest Income Tax"
dVal = -dVal * TaxRate
Case Else
strAccType = "Misc"
strSubCategory = "Transfer"
End Select

Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)
rst.MoveNext
Loop

ErrorHandler_Exit:
Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Import
Transactions Error"
Resume ErrorHandler_Exit

End Function

Sub WriteTransaction(pTranDate As Date, pAccount As String, pAccType As
String, pCategory As String, _
pSubCategory As String, pDesc As String, pVal As Double)

Dim strSQL As String

On Error GoTo ErrorHandler

' need to use the proper delimiters for the data types
strSQL = "INSERT INTO [Test Transaction History]"
strSQL = strSQL & " ( [TranDate], Account, [Accounting Type], Catagory,
SubCatagory, Description, [Value] )"
strSQL = strSQL & " VALUES(#" & pTranDate & "#, '" & pAccount & "', '"
strSQL = strSQL & pAccType & "', '" & pCategory & "', '" & pSubCategory
& "', '"
strSQL = strSQL & pDesc & "', " & pVal & ");"

' insert the data
CurrentDb.Execute strSQL, dbfailonerror

ErrorHandler_Exit:
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,
"WriteTransaction Error"
Resume ErrorHandler_Exit
End Sub
'---------------------------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LindaF said:
Did a few changes but still getting a problem with the date.

Sample input csv (permanent linked in Access):
Date, Amount, Description
2/06/2009,-500,WITHDRAWAL - Receipt 123456
31/05/2009,171.62,Bonus Interest - Receipt 987654

Sample Output:
TranDate Account Accounting Type Catagory SubCatagory Description Value
12:00:14 AM 9 Misc Cash Transfer WITHDRAWAL - Receipt 123456 -$500.00
12:04:27 AM 9 Accrual Cash Tax Estimated Interest Income Tax -$25.74
12:04:27 AM 9 Income Cash Interest Bonus Interest - Receipt 987654 $171.62

New Code:
Function ImportINGTransactions()

Dim db As Database
Dim rst As Recordset
Dim strFolder As String
Dim strFile As String
Dim strFullPath As String
Dim strTargetFolder As String
Dim strTargetPath As String
Dim strSQL As String
Dim dtTranDate As Date
Dim strAccount As String
Dim strAccType As String
Dim strCategory As String
Dim strSubCategory As String
Dim strDesc As String
Dim dVal As Double
Dim TaxRate As Double

On Error GoTo ErrorHandler

Set db = CurrentDb
TaxRate = 0.15

strFolder = "C:\Users\Public\#SMSF\Accounts\"

' Find any ING files ready for importing
strFile = Dir(strFolder & "ING.csv", vbNormal)

'check file name length to confirm files exist
If Len(Trim(Nz(strFile, ""))) = 0 Then
'If strFile = "" Then
MsgBox "No ING Transactions to import", , "Import ING Transactions"
GoTo Done
End If

strSQL = "SELECT TranDate, Amount, Description from [ING Link] "
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
dtTranDate = CDate(rst!TranDate)
strAccount = "9"
strCategory = "'Cash'"
strDesc = "'" & rst!Description & "'"
dVal = rst!Amount

Select Case True
Case UCase(strDesc) Like "*INTEREST*"
strAccType = "'Income'"
strSubCategory = "'Interest'"
Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)

strAccType = "'Accrual'"
strSubCategory = "'Tax'"
strDesc = "'Estimated Interest Income Tax'"
dVal = -dVal * TaxRate
Case Else
strAccType = "'Misc'"
strSubCategory = "'Transfer'"
End Select

Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)
rst.MoveNext
Loop

Done:
Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Import
Transactions Error"
Resume Done

End Function

Sub WriteTransaction(dtTranDate As Date, strAccount As String, strAccType As
String, strCategory As String, _
strSubCategory As String, strDesc As String, dVal As
Double)

Dim strSQL As String

On Error GoTo ErrorHandler

' Turn off warning messages so I don't get the "You are adding 1 record
..." each time
DoCmd.SetWarnings False

strSQL = "Insert into [Test Transaction History] "
strSQL = strSQL & "( [TranDate], Account, [Accounting Type], Catagory,
SubCatagory, Description, [Value] ) "
strSQL = strSQL & "select " & dtTranDate & "," & strAccount & "," &
strAccType & ","
strSQL = strSQL & strCategory & "," & strSubCategory & "," & strDesc &
"," & dVal & ";"
DoCmd.RunSQL strSQL

Done:

' Turn off warning messages
DoCmd.SetWarnings True

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,
"WriteTransaction Error"
Resume Done
End Sub
Click to expand...
 
Thanks I have got the dates now but as you say they assume an american format
so I will have to look into that. Appreciate all your help.

Linda

Steve Sanford said:
I haven't figured out why you have strFolder and strFile if "ING Link" is
"... permanent linked in Access)"

In the table"Test Transaction History", "Value" is a reserved word. Maybe
change it to "Amount".

In the code, string variables had single quotes inside of double quotes. I
removed the single quotes.

ie: strAccType = " ' Income ' " (expanded) changed to strAccType = "
Income "

And in Sub WriteTransaction(), I use "p" to indicate parameter.

I think the problem with the Trandate is not being properly delimited in the
SQL Insert statement in Sub WriteTransaction().

There are two forms of the SQL command "INSERT INTO". One uses a sub query
to select one or more records to insert, and the other form of the command
uses "Values" keyword to insert one record

From the site http://www.1keydata.com/sql/sqlinsert.html :

Quote:
The syntax for inserting data into a table one row at a time is as follows:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

Unquote

When building the SQL string, the values must be properly delimited.



So I created a CSV file and linked it as "ING Link". I created a table
"Test Transaction History".

Using the input date:

Date, Amount, Description
2/06/2009,-500,WITHDRAWAL - Receipt 123456
31/05/2009,171.62,Bonus Interest - Receipt 987654

I get the output:

'TranDate Account Accounting Type Catagory SubCatagory Description Value
'6/2/2009 9 Misc Cash Transfer WITHDRAWAL - Receipt 123456 -500
'5/31/2009 9 Income Cash Interest Bonus Interest - Receipt 987654
171.62
'5/31/2009 9 Accrual Cash Tax Estimated Interest Income Tax -25.743

Note that the dates are in "mm/dd/yyyy" format.

Below is the code I used

'-----------------------------------------
Function ImportINGTransactions()

'using DAO
'need reference set for Microsoft 3.x Object Library
Dim db As Database
Dim rst As Recordset
Dim strFolder As String
Dim strFile As String
Dim strFullPath As String
Dim strTargetFolder As String
Dim strTargetPath As String
Dim strSQL As String
Dim dtTranDate As Date
Dim strAccount As String
Dim strAccType As String
Dim strCategory As String
Dim strSubCategory As String
Dim strDesc As String
Dim dVal As Double
Dim TaxRate As Double

On Error GoTo ErrorHandler

Set db = CurrentDb
TaxRate = 0.15

' strFolder = "C:\Users\Public\#SMSF\Accounts\"
'
' ' Find any ING files ready for importing
' strFile = Dir(strFolder & "ING.csv", vbNormal)
'
' 'check file name length to confirm files exist
' If Len(Trim(Nz(strFile, ""))) = 0 Then
' 'If strFile = "" Then
' MsgBox "No ING Transactions to import", , "Import ING
Transactions"
' GoTo Done
' End If

strSQL = "SELECT TranDate, Amount, Description from [ING Link] "
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
dtTranDate = CDate(rst!TranDate)
strAccount = "9"
strCategory = "Cash"
strDesc = rst!Description
dVal = rst!Amount

Select Case True
Case UCase(strDesc) Like "*INTEREST*"
strAccType = "Income"
strSubCategory = "Interest"
Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)

strAccType = "Accrual"
strSubCategory = "Tax"
strDesc = "Estimated Interest Income Tax"
dVal = -dVal * TaxRate
Case Else
strAccType = "Misc"
strSubCategory = "Transfer"
End Select

Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)
rst.MoveNext
Loop

ErrorHandler_Exit:
Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Import
Transactions Error"
Resume ErrorHandler_Exit

End Function

Sub WriteTransaction(pTranDate As Date, pAccount As String, pAccType As
String, pCategory As String, _
pSubCategory As String, pDesc As String, pVal As Double)

Dim strSQL As String

On Error GoTo ErrorHandler

' need to use the proper delimiters for the data types
strSQL = "INSERT INTO [Test Transaction History]"
strSQL = strSQL & " ( [TranDate], Account, [Accounting Type], Catagory,
SubCatagory, Description, [Value] )"
strSQL = strSQL & " VALUES(#" & pTranDate & "#, '" & pAccount & "', '"
strSQL = strSQL & pAccType & "', '" & pCategory & "', '" & pSubCategory
& "', '"
strSQL = strSQL & pDesc & "', " & pVal & ");"

' insert the data
CurrentDb.Execute strSQL, dbfailonerror

ErrorHandler_Exit:
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,
"WriteTransaction Error"
Resume ErrorHandler_Exit
End Sub
'---------------------------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LindaF said:
Did a few changes but still getting a problem with the date.

Sample input csv (permanent linked in Access):
Date, Amount, Description
2/06/2009,-500,WITHDRAWAL - Receipt 123456
31/05/2009,171.62,Bonus Interest - Receipt 987654

Sample Output:
TranDate Account Accounting Type Catagory SubCatagory Description Value
12:00:14 AM 9 Misc Cash Transfer WITHDRAWAL - Receipt 123456 -$500.00
12:04:27 AM 9 Accrual Cash Tax Estimated Interest Income Tax -$25.74
12:04:27 AM 9 Income Cash Interest Bonus Interest - Receipt 987654 $171.62

New Code:
Function ImportINGTransactions()

Dim db As Database
Dim rst As Recordset
Dim strFolder As String
Dim strFile As String
Dim strFullPath As String
Dim strTargetFolder As String
Dim strTargetPath As String
Dim strSQL As String
Dim dtTranDate As Date
Dim strAccount As String
Dim strAccType As String
Dim strCategory As String
Dim strSubCategory As String
Dim strDesc As String
Dim dVal As Double
Dim TaxRate As Double

On Error GoTo ErrorHandler

Set db = CurrentDb
TaxRate = 0.15

strFolder = "C:\Users\Public\#SMSF\Accounts\"

' Find any ING files ready for importing
strFile = Dir(strFolder & "ING.csv", vbNormal)

'check file name length to confirm files exist
If Len(Trim(Nz(strFile, ""))) = 0 Then
'If strFile = "" Then
MsgBox "No ING Transactions to import", , "Import ING Transactions"
GoTo Done
End If

strSQL = "SELECT TranDate, Amount, Description from [ING Link] "
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
dtTranDate = CDate(rst!TranDate)
strAccount = "9"
strCategory = "'Cash'"
strDesc = "'" & rst!Description & "'"
dVal = rst!Amount

Select Case True
Case UCase(strDesc) Like "*INTEREST*"
strAccType = "'Income'"
strSubCategory = "'Interest'"
Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)

strAccType = "'Accrual'"
strSubCategory = "'Tax'"
strDesc = "'Estimated Interest Income Tax'"
dVal = -dVal * TaxRate
Case Else
strAccType = "'Misc'"
strSubCategory = "'Transfer'"
End Select

Call WriteTransaction(dtTranDate, strAccount, strAccType,
strCategory, strSubCategory, strDesc, dVal)
rst.MoveNext
Loop

Done:
Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Import
Transactions Error"
Resume Done

End Function

Sub WriteTransaction(dtTranDate As Date, strAccount As String, strAccType As
String, strCategory As String, _
strSubCategory As String, strDesc As String, dVal As
Double)

Dim strSQL As String

On Error GoTo ErrorHandler

' Turn off warning messages so I don't get the "You are adding 1 record
..." each time
DoCmd.SetWarnings False

strSQL = "Insert into [Test Transaction History] "
strSQL = strSQL & "( [TranDate], Account, [Accounting Type], Catagory,
SubCatagory, Description, [Value] ) "
strSQL = strSQL & "select " & dtTranDate & "," & strAccount & "," &
strAccType & ","
strSQL = strSQL & strCategory & "," & strSubCategory & "," & strDesc &
"," & dVal & ";"
DoCmd.RunSQL strSQL

Done:

' Turn off warning messages
DoCmd.SetWarnings True

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,
Click to expand...
Click to expand...
 
Back
Top