altering table fieldname using sql 1

[gremlins]

I have an access query that makes a table. One of the fields is selective using the following code:

future_date: IIf([TERMINATION_DATE]>Now()+3,"",[TERMINATION_DATE])

which basically returns blank if a date in the TERMINATION_DATE field is greater than 3 days in advance of today.

I did want to be able to call this field the same title - TERMINATION_DATE, because other queries use this table again, but I get a circular reference error if I do.

Therefore I want to change it after I have made the table with the 'future_date' field in it. I have been trying to incorporate code in to the module like:

ALTER TABLE [tblName] ALTER FIELDNAME [future_date] = [TERMINATION_DATE]

but it won't have it.

Any suggestions?

 

[RoyVidar]

I'm not 100% sure, but I don't think sql ddl supports changing the name of a column. To use sql, I think the workaround would be three steps (running three sql statements)

1 - add a new column with the new name
[tt]alter table mytable add column TERMINATION_DATE DATETIME[/tt]
2 - issue an update query on the column (update the new column based on the old)
3 - drop the old column
[tt]alter table mytable drop column future_date[/tt]

Roy-Vidar

 

[oharab]

You don't need to change the field name. You just need to be explicit in defining your fields:

TERMINATION_DATE: IIf([tblName].[TERMINATION_DATE]>Now()+3,"",[tblName].[TERMINATION_DATE])

This will work fine as you have told Access to look at the field called TERMINATION_DATE in the table called tblName rather than a generic object called TERMINATION_DATE which it then has to decide what you mean.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[gremlins]

Cheers.

RoyVidar - did consider this method but didn't want to start messing around creating and deleting columns.

Oharab - You're a star. Funny how it all makes sense when you can see the answer. The circular reference problem is because it's trying to assess its own field as well as those I want to look at in the other table.

It's always a simple answer isn't it?

Thanks