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?

Variables in a query
 

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
HIS


"Keith" wrote in message
...
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?

Variables in a query
 

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
HIS


"Keith" wrote in message
...
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?

Variables in a query
 

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
HIS


"gocush" /delete wrote in message
...
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
HIS


"Keith" wrote in message
...
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?

Variables in a query
 

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
HIS


"gocush" /delete wrote in message
...
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
HIS


"Keith" wrote in message
...
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?

Variables in a query
 

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.

--