• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Importing from Excel to Access

Hi Guys, I am trying to import an Access table in Excel via a Macro where the POSt Date FIeld = DAY -1 and where the Account Field contains the string "FX" within it, but I am getting a Type Mismatch on my SELECT line. Any ideas? My code is below:

Sub Importaccount()

Cheers Justin
Dim db As Database
Dim rst As Recordset
Dim crange, strsql As String

Sheets("Data").Activate

Set db = OpenDatabase("G:\\Restricted\UKMFG-TS-AM-EUC03\CFDs\Databases 2009\Dividends database- Oct'11.mdb")

Sheets("Data").Visible = True

Application.ScreenUpdating = True

'strsql = "SELECT [Dividend Postings- GFM].Account, [Dividend Postings- GFM].[Post Date] FROM [Dividend Postings- GFM]WHERE ((([Dividend Postings- GFM].Account) Like " * FX * ") AND (([Dividend Postings- GFM].[Post Date])=#10/11/2011#))"

strsql = "Select * from [Dividend Postings- GFM] where Account Like " * FX * "  AND [Post Date] = ""#11/10/2011#"""
'


Set rst = db.OpenRecordset(strsql)

Sheets("Data").Range("A1").Offset(1, 0).CopyFromRecordset rst

rst.Close
db.Close
Sheets("Data").Visible = True
Application.ScreenUpdating = False



End Sub
0
JCutcliffe
Asked:
JCutcliffe
  • 5
  • 3
1 Solution
 
Patrick MatthewsCommented:
Try:

strsql = "Select * " & _
    "from [Dividend Postings- GFM] " & _
    "where Account Like '*FX*' AND [Post Date] = #11/10/2011#"

Open in new window

0
 
JCutcliffeAuthor Commented:
Hi, now I am getting "Run time error '3464' Data type mismatch in crireria expression "

on this part of the code.

Set rst = db.OpenRecordset(strsql)

Any ideas? Justin
0
 
Patrick MatthewsCommented:
Declare it as:

Dim rst As DAO.Recordset

Open in new window


and make sure you set a reference to the Microsoft DAO (data access objects) library.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
JCutcliffeAuthor Commented:
I don't think it is that as it imports from Access fine when just using the following statement

strsql = "Select * from [Dividend Postings- GFM]
0
 
JCutcliffeAuthor Commented:
Hi Matt,
If Post Date & Account are both Field Names in the Access Table, why is Post date in Square Brackets & not Account?

Justin
0
 
sameer2010Commented:
Because of space and account being keyword
0
 
JCutcliffeAuthor Commented:
OK! I am using this code now:
It works fine when I use Select = Account Like "*FX*" separately from [Post Date] = ""##11/10/2011#""
However, when I use them together, it brings back no data at all. Any theories?
 

Sub Importaccount()


Dim db As Database
Dim rst As DAO.Recordset

'.DAO
Dim crange, strsql As String

Sheets("Data").Activate

Set db = OpenDatabase("G:\\Restricted\UKMFG-TS-AM-EUC03\CFDs\Databases 2009\Dividends database- Oct'11.mdb")

Sheets("Data").Visible = True

Application.ScreenUpdating = True


strsql = "Select * from [Dividend Postings- GFM] where Account Like '*FX*' AND [Post Date] Like ""#11/10/2011#"""


Set rst = db.OpenRecordset(strsql)

Sheets("Data").Range("A1").Offset(1, 0).CopyFromRecordset rst

rst.Close
db.Close
Sheets("Data").Visible = True
Application.ScreenUpdating = False



End Sub
0
 
JCutcliffeAuthor Commented:
Hi it seems to be falling over because of the Date.

now I am getting "Run time error '3464' Data type mismatch in crireria expression "

Any ideas?

Sub Importaccount()


Dim db As Database
Dim rst As DAO.Recordset

'.DAO
Dim crange, strsql As String

Sheets("Data").Activate

Set db = OpenDatabase("G:\\Restricted\UKMFG-TS-AM-EUC03\CFDs\Databases 2009\Dividends database- Oct'11.mdb")

Sheets("Data").Visible = True

Application.ScreenUpdating = True


strsql = "Select * from [Dividend Postings- GFM] where [Post Date] = ""#11/10/2011#"""
'Account Like '*FX*' "
'AND
Set rst = db.OpenRecordset(strsql)

Sheets("Data").Range("A1").Offset(1, 0).CopyFromRecordset rst

rst.Close
db.Close
Sheets("Data").Visible = True
Application.ScreenUpdating = False



End Sub

0
 
Patrick MatthewsCommented:
It's failing because you're not handling the date correctly.

I am beginning to suspect that you did not actually try my suggestion in http:#a36954968
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now