Variables in a query

[Keith]

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 Hodge]

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/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

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

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/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

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 Hodge]

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
(e-mail address removed)

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

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/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

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?

 

[Guest]

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

Just looking for other techniques.

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
(e-mail address removed)

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

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/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


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?

 

[Jamie Collins]

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.

--