Parameter Query which changes field selected

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

I can't think of a way to do this without writing code. End user wants to
have a parameter query with a start date and an end date AND then have the
fields used in the query be selected by the parameters.

We have a linked Excel file. Each month is a field e.g. [Balance03/04],
[Balance 04/04], [Balance 05/04] and so on. Obviously this grows each month
as a new field/column is added to the spreadsheet.

They want to have two parameter queries come up so the user specifies two
dates and somehow it then determines which fields it is to use.

I'm thinking I've got to do code but they are looking for a non-code
solution.

Can it be done?
 
While there may be a way to do what you want, adding a new column for each
month is a big, Big, BIG mistake when it comes to Access. In a relational
database, you would simply need one column for the BalanceDate and then you
can query the data and, if need be, turn it into a Cross Tab query to give
it the look and feel of a spreadsheet.
 
I couldn't agree more. However I have to make it work inside the structure
given to me which is an Excel spreadsheet.

Right now I'm thinking about a PivotTable with the two "selected" fields in
the detail. I still end up writing code to move the appropriate field in to
the detail section.

Lynn Trapp said:
While there may be a way to do what you want, adding a new column for each
month is a big, Big, BIG mistake when it comes to Access. In a relational
database, you would simply need one column for the BalanceDate and then you
can query the data and, if need be, turn it into a Cross Tab query to give
it the look and feel of a spreadsheet.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Dkline said:
I can't think of a way to do this without writing code. End user wants to
have a parameter query with a start date and an end date AND then have the
fields used in the query be selected by the parameters.

We have a linked Excel file. Each month is a field e.g. [Balance03/04],
[Balance 04/04], [Balance 05/04] and so on. Obviously this grows each month
as a new field/column is added to the spreadsheet.

They want to have two parameter queries come up so the user specifies two
dates and somehow it then determines which fields it is to use.

I'm thinking I've got to do code but they are looking for a non-code
solution.

Can it be done?
Click to expand...
Click to expand...
 
Well, you may be able to do a Union Query to put the table in a more normal
format and then run a crosstab query from that query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Dkline said:
I couldn't agree more. However I have to make it work inside the structure
given to me which is an Excel spreadsheet.

Right now I'm thinking about a PivotTable with the two "selected" fields in
the detail. I still end up writing code to move the appropriate field in to
the detail section.

Lynn Trapp said:
While there may be a way to do what you want, adding a new column for each
month is a big, Big, BIG mistake when it comes to Access. In a relational
database, you would simply need one column for the BalanceDate and then you
can query the data and, if need be, turn it into a Cross Tab query to give
it the look and feel of a spreadsheet.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Dkline said:
I can't think of a way to do this without writing code. End user wants to
have a parameter query with a start date and an end date AND then have the
fields used in the query be selected by the parameters.

We have a linked Excel file. Each month is a field e.g. [Balance03/04],
[Balance 04/04], [Balance 05/04] and so on. Obviously this grows each month
as a new field/column is added to the spreadsheet.

They want to have two parameter queries come up so the user specifies two
dates and somehow it then determines which fields it is to use.

I'm thinking I've got to do code but they are looking for a non-code
solution.

Can it be done?
Click to expand...
Click to expand...
Click to expand...
 
I'm not clear enough about what you want to provide details,
(and I like the union query suggestion),
but you can create parameter queries like you want.

You just need to use an IIF,SWITCH or CHOOSE function in your
calculated field, to choose (based on the parameter dates)
which fields will be included your calculated field:

a: switch(pdate<date1,bal_1,pdate<date2,bal_1 + bal_2.....

(You can also use these functions in the criteria section,
to choose the criteria you will apply to each column, depending
on the parameter values).

(david)
 
Back
Top