MS Query

[Don@nospamdmdnet.us]

I am writing an MS Query and it is working I'm just wondering if I can pass a
parameter to the query from the spread sheet by refrencing a cell.
Something like this
Select transamnt from gldetail where fiscalyear = $A$1
if I hardcode the year in the SQL it works fine,
I guess I can write in VBA but I was trying to take a shortcut?
Any ideas? TIA

[Roadie]

Just record editing the query and then have a look at the recorded macro. Your value in A1 should be outside the quotes.

first give fiscalyear its value:
fiscalyear = range("A1").value

Then the query should be something like this:
"SELECT .. FROM .. WHERE fiscalyear = " & fiscalyear

[Bob H]

Try specifying a user input parameter in MS Query using [ ] as your
criteria. Return data to MS Excel (specify any valid inout when prompted).
Once back in Excel. right click somewhere in your qury results. Choose
Parameters. Specify that you want the parameter data from a specified cell
(A1). You can also optioanlly specify to automatically refresh the query
whenever the cell value changes.



"Don@nospamdmdnet.us" wrote:

> I am writing an MS Query and it is working I'm just wondering if I can pass a
> parameter to the query from the spread sheet by refrencing a cell.
> Something like this
> Select transamnt from gldetail where fiscalyear = $A$1
> if I hardcode the year in the SQL it works fine,
> I guess I can write in VBA but I was trying to take a shortcut?
> Any ideas? TIA
>

[Roadie]

Try specifying a user input parameter in MS Query using [ ] as your
criteria. Return data to MS Excel (specify any valid inout when prompted).
Once back in Excel. right click somewhere in your qury results. Choose
Parameters. Specify that you want the parameter data from a specified cell
(A1). You can also optioanlly specify to automatically refresh the query
whenever the cell value changes.

Hm, I've never known there was an option like 'Parameters' and now that I want to try how it works, it's greyed out. Why?