Variables in a query

I need to ask for variables in an Excel query in Excel XP.

I need the year and a month. The query then restricts the search to the
year entered and the month plus the following 2 months.

I have done this by entering the value to be >=[Enter Month] and
<=[Enter Month] + 2

This works, but I get asked for the Enter Month variable twice.

Is there a way round this?

Are you using an inline formula with SQL.REQUEST or are you using a macro? The best way to do it is to write a macro to generate a stream of text, inserting the variable into the statement twice.

Also, are you pulling from another worksheet or a SQL/Access data source?

For more information, check here:
http://support.microsoft.com/kb/246335/EN-US/

Keith

The values of parameters is not preserved between calls, so effectively the
parameter is asked for twice because you have it twice. A couple of
possible solutions.

1) Make the boxes a little more intuitive (Although still as frustrating)

Between [Enter a start month] And [Enter an end month, start month +2]

2) Enter the start value on the worksheet and have the second value
calculated on the worksheet from that start value and then have the
parameters picked up from these worksheet cells. This route will even
refresh the data each time the cell is changed.

Take a look at

http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"Keith" <[email protected]> wrote in message
news:[email protected]...
>I need to ask for variables in an Excel query in Excel XP.
>
> I need the year and a month. The query then restricts the search to the
> year entered and the month plus the following 2 months.
>
> I have done this by entering the value to be >=[Enter Month] and <=[Enter
> Month] + 2
>
> This works, but I get asked for the Enter Month variable twice.
>
> Is there a way round this?

Nick,
Your website demonstrating a database query with MS Query and parameters in
Excel is excellent.
Assuming the user has permission access to the db, do you also have or can
you explain how to add/edit/delete records stored in Access via Excel?

Thanks

"Nick Hodge" wrote:

> Keith
>
> The values of parameters is not preserved between calls, so effectively the
> parameter is asked for twice because you have it twice. A couple of
> possible solutions.
>
> 1) Make the boxes a little more intuitive (Although still as frustrating)
>
> Between [Enter a start month] And [Enter an end month, start month +2]
>
> 2) Enter the start value on the worksheet and have the second value
> calculated on the worksheet from that start value and then have the
> parameters picked up from these worksheet cells. This route will even
> refresh the data each time the cell is changed.
>
> Take a look at
>
> http://nickhodge.co.uk/gui/datamenu/...taexamples.htm
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "Keith" <[email protected]> wrote in message
> news:[email protected]...
> >I need to ask for variables in an Excel query in Excel XP.
> >
> > I need the year and a month. The query then restricts the search to the
> > year entered and the month plus the following 2 months.
> >
> > I have done this by entering the value to be >=[Enter Month] and <=[Enter
> > Month] + 2
> >
> > This works, but I get asked for the Enter Month variable twice.
> >
> > Is there a way round this?
>
>
>

Afraid not, I use Access when I need to, and do any data manipulation in it,
with a great deal of reporting in Excel from the data, but never had a need
to edit the Access data from Excel...

Anyone?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"gocush" <[email protected]/delete> wrote in message
news:[email protected]...
> Nick,
> Your website demonstrating a database query with MS Query and parameters
> in
> Excel is excellent.
> Assuming the user has permission access to the db, do you also have or can
> you explain how to add/edit/delete records stored in Access via Excel?
>
> Thanks
>
> "Nick Hodge" wrote:
>
>> Keith
>>
>> The values of parameters is not preserved between calls, so effectively
>> the
>> parameter is asked for twice because you have it twice. A couple of
>> possible solutions.
>>
>> 1) Make the boxes a little more intuitive (Although still as frustrating)
>>
>> Between [Enter a start month] And [Enter an end month, start month +2]
>>
>> 2) Enter the start value on the worksheet and have the second value
>> calculated on the worksheet from that start value and then have the
>> parameters picked up from these worksheet cells. This route will even
>> refresh the data each time the cell is changed.
>>
>> Take a look at
>>
>> http://nickhodge.co.uk/gui/datamenu/...taexamples.htm
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> [email protected]HIS
>>
>>
>> "Keith" <[email protected]> wrote in message
>> news:[email protected]...
>> >I need to ask for variables in an Excel query in Excel XP.
>> >
>> > I need the year and a month. The query then restricts the search to
>> > the
>> > year entered and the month plus the following 2 months.
>> >
>> > I have done this by entering the value to be >=[Enter Month] and
>> > <=[Enter
>> > Month] + 2
>> >
>> > This works, but I get asked for the Enter Month variable twice.
>> >
>> > Is there a way round this?
>>
>>
>>

I have used some adaptations from Andy Wiggins work. See
www.BygSoftware.com

Just looking for other techniques.

"Nick Hodge" wrote:

> Afraid not, I use Access when I need to, and do any data manipulation in it,
> with a great deal of reporting in Excel from the data, but never had a need
> to edit the Access data from Excel...
>
> Anyone?
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "gocush" <[email protected]/delete> wrote in message
> news:[email protected]...
> > Nick,
> > Your website demonstrating a database query with MS Query and parameters
> > in
> > Excel is excellent.
> > Assuming the user has permission access to the db, do you also have or can
> > you explain how to add/edit/delete records stored in Access via Excel?
> >
> > Thanks
> >
> > "Nick Hodge" wrote:
> >
> >> Keith
> >>
> >> The values of parameters is not preserved between calls, so effectively
> >> the
> >> parameter is asked for twice because you have it twice. A couple of
> >> possible solutions.
> >>
> >> 1) Make the boxes a little more intuitive (Although still as frustrating)
> >>
> >> Between [Enter a start month] And [Enter an end month, start month +2]
> >>
> >> 2) Enter the start value on the worksheet and have the second value
> >> calculated on the worksheet from that start value and then have the
> >> parameters picked up from these worksheet cells. This route will even
> >> refresh the data each time the cell is changed.
> >>
> >> Take a look at
> >>
> >> http://nickhodge.co.uk/gui/datamenu/...taexamples.htm
> >>
> >> --
> >> HTH
> >> Nick Hodge
> >> Microsoft MVP - Excel
> >> Southampton, England
> >> [email protected]HIS
> >>
> >>
> >> "Keith" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I need to ask for variables in an Excel query in Excel XP.
> >> >
> >> > I need the year and a month. The query then restricts the search to
> >> > the
> >> > year entered and the month plus the following 2 months.
> >> >
> >> > I have done this by entering the value to be >=[Enter Month] and
> >> > <=[Enter
> >> > Month] + 2
> >> >
> >> > This works, but I get asked for the Enter Month variable twice.
> >> >
> >> > Is there a way round this?
> >>
> >>
> >>
>
>
>

gocush wrote:
> Assuming the user has permission access to the db, do you also have
or can
> you explain how to add/edit/delete records stored in Access via
Excel?

Do you mean via MS Query. It can be done but not via the GUI query
builder tools. You will have to type the appropriate SQL DML (INSERT
INTO/UPDATE/DELETE respectively) yourself. Note that if you do this you
will lose support for parameters because the query cannot be displayed
'graphically'.

Jamie.

--