Help with dynamically naming a table

[Al]

I have a table named "BuildingDetails_06".Next year a new table will replace
it and will be named "BuildingDetails_07". I am trying to build a query that
does not require the user to go in the design every year to rename the table
in this query or other queries that may be using this table. I have tried the
following but it does not work. due to the FROM Clause.
****************************************************
SELECT Area, subsystem, Format(DateAdd("yyyy",0,Date()),"yyyy") as [FY],
[FY1] as [Total]
FROM BuildingDetails_ & Format(DateAdd("yyyy",-1,Date()),"yy")
*******************************************************
can some one help?
thanks
Al

 

[KARL DEWEY]

I think the easiest thing is to not use multiple tables but a field that
indicates the year.

 

[Al]

Yes, I agree and this is the way that I will go. However, is there a way to
do it the way I mentioned in my posting?

I think the easiest thing is to not use multiple tables but a field that
indicates the year.
--
KARL DEWEY
Build a little - Test a little

I have a table named "BuildingDetails_06".Next year a new table will replace
it and will be named "BuildingDetails_07". I am trying to build a query that
does not require the user to go in the design every year to rename the table
in this query or other queries that may be using this table. I have tried the
following but it does not work. due to the FROM Clause.
****************************************************
SELECT Area, subsystem, Format(DateAdd("yyyy",0,Date()),"yyyy") as [FY],
[FY1] as [Total]
FROM BuildingDetails_ & Format(DateAdd("yyyy",-1,Date()),"yy")
*******************************************************
can some one help?
thanks
Al

 

[KARL DEWEY]

I dunno, maybe someone else can suggest how.
--
KARL DEWEY
Build a little - Test a little

Yes, I agree and this is the way that I will go. However, is there a way to
do it the way I mentioned in my posting?

I think the easiest thing is to not use multiple tables but a field that
indicates the year.
--
KARL DEWEY
Build a little - Test a little

I have a table named "BuildingDetails_06".Next year a new table will replace
it and will be named "BuildingDetails_07". I am trying to build a query that
does not require the user to go in the design every year to rename the table
in this query or other queries that may be using this table. I have tried the
following but it does not work. due to the FROM Clause.
****************************************************
SELECT Area, subsystem, Format(DateAdd("yyyy",0,Date()),"yyyy") as [FY],
[FY1] as [Total]
FROM BuildingDetails_ & Format(DateAdd("yyyy",-1,Date()),"yy")
*******************************************************
can some one help?
thanks
Al

 

[Dale Fye]

You could write some code to loop through all of your querydefs and use the
Replace( ) function to replace references to BuildingDetails_06 with
BuildingDetails_07.

This would not be too difficult, but the challenge would be those queries
that are being used as the data source for lists and combo boxes or even
forms that are not in saved queries.

I'm with Karl. This is bad database design. Add the FY field to the
BuildingDetails table!!!!

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Yes, I agree and this is the way that I will go. However, is there a way to
do it the way I mentioned in my posting?

I think the easiest thing is to not use multiple tables but a field that
indicates the year.
--
KARL DEWEY
Build a little - Test a little

I have a table named "BuildingDetails_06".Next year a new table will replace
it and will be named "BuildingDetails_07". I am trying to build a query that
does not require the user to go in the design every year to rename the table
in this query or other queries that may be using this table. I have tried the
following but it does not work. due to the FROM Clause.
****************************************************
SELECT Area, subsystem, Format(DateAdd("yyyy",0,Date()),"yyyy") as [FY],
[FY1] as [Total]
FROM BuildingDetails_ & Format(DateAdd("yyyy",-1,Date()),"yy")
*******************************************************
can some one help?
thanks
Al

 

[Al]

I agree. I just did that.
thanks

You could write some code to loop through all of your querydefs and use the
Replace( ) function to replace references to BuildingDetails_06 with
BuildingDetails_07.

This would not be too difficult, but the challenge would be those queries
that are being used as the data source for lists and combo boxes or even
forms that are not in saved queries.

I'm with Karl. This is bad database design. Add the FY field to the
BuildingDetails table!!!!

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Yes, I agree and this is the way that I will go. However, is there a way to
do it the way I mentioned in my posting?

I think the easiest thing is to not use multiple tables but a field that
indicates the year.
--
KARL DEWEY
Build a little - Test a little


:

I have a table named "BuildingDetails_06".Next year a new table will replace
it and will be named "BuildingDetails_07". I am trying to build a query that
does not require the user to go in the design every year to rename the table
in this query or other queries that may be using this table. I have tried the
following but it does not work. due to the FROM Clause.
****************************************************
SELECT Area, subsystem, Format(DateAdd("yyyy",0,Date()),"yyyy") as [FY],
[FY1] as [Total]
FROM BuildingDetails_ & Format(DateAdd("yyyy",-1,Date()),"yy")
*******************************************************
can some one help?
thanks
Al