pulling access into excel

[amypaulsen]

good morning oh knowledgeable ones...

I am using a SQL statement in VB within excel to pull in a table out of access. It is currently working using the reference tblTransactions.UNIT but what i really want to reference is the query NOT the table. What would I use in the SQL statment?

As always...many thanks!

 

[Norie]

Why not build the query you require in Access?

Make sure it reurns the results you require.

Then goto View>SQL View and copy the SQL from there into Excel.

 

[amypaulsen]

I have done that. I built the query in Access and am trying to pull it into excel. I am only trying to pull certain data out of the query so I have to build the parameters separately in VB

 

[Norie]

what i really want to reference is the query NOT the table

So what are you 'referencing' in Access to get the SQL the table or a query.

I don't thinnk I quite understand what you wish to do.

 

[amypaulsen]

I'm sorry...it's a bit confusing...

I have a report in Excel that needs to stay in Excel (clients request) but the data is housed in Access where it is updated on a weekly basis. So, I built the queries in Access so the user can update the table which will automatically update the queries. Then the user goes back to Excel and chooses the desired week and through the SQL statement written in VB in Excel, it pulls the corresponding week.

I've gotten it to pull from the query, however, I'm having problems retrieving the desired selection. Here is a copy of the statement from VB:

strSQL = "SELECT qryTransactions.UNIT, qryTransactions.'" & Cells(5, 1).Value & "' from [qryTransactions] where qryTransactions.UNIT = '" & Cells(12, 35).Value & "'"

hope this makes more sense...

 

[Norie]

Amy

That looks fine.

What is the problem?

Check in Acces that qryTransactions is returning the correct records.

 

[amypaulsen]

the part I am having a problem with is:

qryTransactions.'" & cells(5,1).value & "' from...

when I run the macro, the error message I am getting is:

run-time error 3075:

Invalid use of '.', '!', or '()'. in query expression 'qryTransactions.'W48''.

So the pull is obviously recognizing what is in the cell..."W48", but it wont retrieve it from the Access database. I swear this is making me pull out the last friggin hair I have!!!! UGH!

 

[Norie]

Check what strSQL looks like.

I don't think you need the ' single quote for Cells(5,1). I'm assuming that cell contains a field name?

Also check the data type of cell - that could be causing problems.

In fact use variables instead of the cells directly.

e.g.

valCell1 = Cells(5, 1).Value
valCell2 = Cells(12, 35).Value

 

[amypaulsen]

that was a fabulous idea and you were almost my hero, but I ran it and got the same error except: qryTransactions.True

I've changed the formatting to value & text just to try them both out but they are defined in Access as TEXT! Ugh...

The thing that is getting me is that it says the error is because of improper use of sytax ".", "!", or "()". Out of those items, the only thing I'm using is the period and when I remove/change it, the whole thing bombs out.

Doesn't make SENSE!

Sorry for yelling I just need someone to shoot me!

 

[Norie]

What are the values in the cells?

What are they meant to be?