How do I update empty(null) fields in a table with n.a.?

[JimWho]

I have a table where some fields do not have any data. I guess they are considered to be null? Anyway, I would like to update these empty fields in this table with n.a. but I haven't a clue how to do this. Can anyone help in plain english.

I am as green as they come.

Jim

 

[TomCologne]

UPDATE tblYourTable SET tblYourTable.YourField = "n.a."
WHERE (((tblYourTable.YourField) Is Null));

That should do it,

TomCologne

 

[MichaelRed]

To do - or NOT to do. That IS the question!!!!!!!


Null has a 'meaning' in database land. It refers to [empty | unknown]. It is USEFUL (in db land) in many functions. It is non-numeric, so doesn't affect aggregate functions (total, avg, ... etc), it is an at least peculiar 'NON string', so doesn't sort in the mmidst of otherwise valid information,

Replaciing it -although a COMMON activity in some of hte rural villages of db land- is generally frowned upon in the more urban / comospolitan areas of (tiresomely) db land. It is (in my opinion) even better for those pesky 'reporting thinnggyysssss' we are often tasked with, as it doesn;t show up begging the question to be asked in hte 'normal' course of events, but may (of course) be replaced by any tricky words (or even cute phrases) for the benefit of those who are simple visitors (to the tritely phrased 'db land') and abhore the vaccuum implied by the emptiness of null.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[AvGuy]

If the field is a text field you can set the default value of the field (at the table level) to be "N/A." There's also a way to do this for a numeric field, but it would be a very bad idea to do so.

To update all the null fields in your table at one time, use a simple update query where you set the criteria of the target field to "Is Null" and the update value to "N/A" and then run the query.

As mentioned above, however, null values have a certain significance in Access that will be lost if you opt to replace them with a text value. You should therefore consider carefully the benefits to be gained by using such a default value.

AvGuy

 

[JimWho]

Thank you one and all. You have been most helpful. Nullsview is without an area of wonderment, however for my purposes filling n.a. does without a doubt provide value.

A fellow commuter saw my perplexed look while working on the train and I explained my problem and he suggested the following solution, which works beatifully.

Deck Position: Nz([tblExceptions Part 1]![DECK POSITION],"N/A&quot

Now do we have any expert Report writers. I have to now work with subreports and some how merge multi-data from two into one......