New Field that Pulls from Existing Field

[Zorkmid]

Hi all,

I want to create a new field that draws from another field in the same table. One possibility is "Near Miss" in the existing field. For every record where "Near Miss" is in the existing field, I want to have "Near Miss" in my new field. In all other cases I want "Actual Event".

Is there some kind of append query I can use to do this?

 

[vbaInet]

I don't think there is a need for duplicating data in the same record, in the same table? If you feel you do, then you should be thinking of "normalization". You can use queries instead to display this.

 

[Zorkmid]

No, I want to remove "Near Miss" as a selection on the entry form under that field and have a new field for it. Near Miss is currently buried in a combobox where it doesnt belong.

 

[vbaInet]

Are you going to delete that old field after you've done this?

This would be a combination of an Update query and the IIF() function. Have you used these before?

 

[Zorkmid]

No I haven't

And I dont plan on deleting the old field, just removing the option to select "Near Miss"

 

[vbaInet]

You can write code to disable that control if that value was selected.

If you still want to go ahead with your plan, then here is the format of an update query (based on your spec):

CurrentDb.Execute "UPDATE [TableName] SET [NewFieldName] = 'Near Miss' WHERE [ExistingFieldName] = 'Near Miss';"
CurrentDb.Execute "UPDATE [TableName] SET [NewFieldName] = 'Near Miss' WHERE [ExistingFieldName] <> 'Actual Event';"

First try it on a back-up copy. Put the right table name and field names and paste it into the On Click event of a command button.

 

[Zorkmid]

Where do I put the command button?

 

[vbaInet]

On a form!

 

[Zorkmid]

What's a form?

lol

 

[vbaInet]

Sounds like you need to do some reading Zorkmid. Have a quick search on google for some quick tutorials.

 

[Zorkmid]

You can write code to disable that control if that value was selected.

If you still want to go ahead with your plan, then here is the format of an update query (based on your spec):

CurrentDb.Execute "UPDATE [TableName] SET [NewFieldName] = 'Near Miss' WHERE [ExistingFieldName] = 'Near Miss';"
CurrentDb.Execute "UPDATE [TableName] SET [NewFieldName] = 'Near Miss' WHERE [ExistingFieldName] <> 'Actual Event';"

First try it on a back-up copy. Put the right table name and field names and paste it into the On Click event of a command button.

I think you mean:

CurrentDb.Execute "UPDATE [TableName] SET [NewFieldName] = 'Near Miss' WHERE [ExistingFieldName] = 'Near Miss';"
CurrentDb.Execute "UPDATE [TableName] SET [NewFieldName] = 'Actual Event' WHERE [ExistingFieldName] <> 'Near Miss';"


But I got the picture

THanks!

 

[vbaInet]

That's the one. Good spot

You're welcome.