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!

 

[amypaulsen]

the value of the cell is "W48" or whatever the user chooses. It is actually coming from a textbox in a form. The user gets a pop up form upon opening Excel and is asked which week they would like to choose. They type in W48 and it should retrieve from Access the corresponding value for field name W48. It works perfectly when I put: qryTransactions.W48 in the SQL statement but I get the error when using qryTransactions.'" & cells(5,1).value & "' or qryTransactions.'" & txtWeek.text & "' BUT, you can see within the error that it is recognizing that the user put "W48" into the box.

Hope I'm making sense...and thanks A LOT for your help!

 

[Norie]

In fact use variables instead of the cells directly.

e.g.

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

Have you tried this? and this

Check what strSQL looks like.

 

[amypaulsen]

tried using the varCells and that didn't work but the cells(12, 35) is pulling in just fine because I have it defined in the beginning as qryTransactions.UNIT

Here is the entire strSQL:

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

when I have written like this:

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

it works just fine. But I have to make it so the user can pick any week of the year: W1 through W52

 

[Norie]

There are definitely fields called W1 to W52 in the query?

Try this:

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

 

[amypaulsen]

OHMYGOSH!!!!!!!!!!!!!!!!!!!!!!!!!!!!

YOU DID IT!!! You are SUCH my hero!!!

Thank you thank you thank you!!!