Update within table (Access2003)

Topic

New Reply

Is this possible to update field in one table (where values are blank) to the another field in the same table?

Region_____GeoRegion
East
West________WEST
Central

Update GeoRegion to Region where values are blank.

Region_____GeoRegion
East________East
West________WEST
Central_____Central

Reply | Quote

Replies

Create a query based on the table.
Select Query | Update query.
Add the Region and GeoRegion fields to the query grid.
Enter the following in the Criteria line for the GeoRegion field: Is Null
Enter the following in the Update to line for the GeoRegion field:

[NameOfTable].[Region]

where NameOfTable is the name of the table.
Select Query | Run or click the Run button on the toolbar.

Reply | Quote

WOW!
My mistake was I was using
Where IsNull([NameOfTable].[Region])

Thanks so much!

Reply | Quote

All great! Except now my all values are different case.
Some all UPPER, some Normal with capital first letter.

Is there way to make them all Normal? Thanks

Reply | Quote

The query will copy whatever is in the Region field. If you want it to use “proper” case, set the Update to line to

StrConv([Region],3)

This will only affect the values that are actually updated. It won’t change the values in the GeoRegion field that have already been filled in. If you want to change those too, create another update query, and add only the GeoRegion field, with:

Criteria: Is Not Null
Update to: StrConv([GeoRegion],3)

Reply | Quote

Do you know EVERYTHING? :-))
THANKS!

Reply | Quote