MS-Access - why can't this update query fill empty cells?

Question

In MS-Access database, table called NewTable3

colname is a text column containing lot of empty cells or blanks.

i want to put ? character in empty cells . when i run the query

UPDATE NewTable3 SET colname = '?' WHERE ISNULL(colname) ;

This query updates 0 records why . what is wrong with this query

Answer 1

Try:

UPDATE NewTable3 SET colname = '[?]' WHERE ISNULL(colname);

The questionmark is used for anonymous parameters, so you need to escape it as above. Note that I have not tried this.

Answer 2

UPDATE NewTable3 SET NewTable3.colname = "?"
WHERE (((NewTable3.colname) Is Null));

To keep your function: WHERE (((IsNull([NewTable3.colname]))=True));

Answer 3

Two quick things:

1) Try putting the colname in square brackets. 2) Remember that empty cells (Nulls) and empty strings ("") are different.

Together:

UPDATE NewTable3 SET [colname] = "?" WHERE ISNULL([colname]) OR [colname] = "";

Also, are you running the query in Access itself, or just using the Access engine and using the data in another program/via a VBA script? It can make a difference.

Answer 4

EDIT:

Based on @onedaywhen's prodding, I now see that I never fully absorbed the original question, which was asking about replacing Nulls with the literal ? character. This is insane and not helpful or useful. If you don't have a meaningful default value for the field, then LEAVE IT NULL. If you want to distinguish between Null (unknown) and Blank (i.e., known to be blank), you can allow zero-length strings and change the Nulls to ZLS.

My original post follows, since I think it is useful for people who might get to this crazy question needing to do things properly:

---

In total, all the answers in this thread end up solving all the problems with the original SQL statement, but they do so incompletely, so I'll compile them all together in an attempt to create a comprehensive correct answer.

@Wim Hollebrandse wisely points out that a parameter needs brackets, but posts the SQL as:

  UPDATE NewTable3 SET colname = '[?]' WHERE ISNULL(colname);

This is incorrect, in that the quotes will cause what's inside them to be treated literally, instead of evaluated as a paramter, so you'll end up with all your fields updated to the literal value "[?]". The correct syntax would be:

  UPDATE NewTable3 SET colname = [?] WHERE ISNULL(colname);

@GuinnessFan points out a problem in the WHERE clause, suggesting out that the result of IsNull() needs to be compared to True in order for the WHERE clause to work. In other words, this:

  WHERE IsNull(NewTable3.colname)

...should be this:

  WHERE IsNull(NewTable3.colname)=True

But given that both statements evaluate the same, they are entirely equivalent. But @GuinnessFan is correct that this is the best syntax:

  WHERE NewTable3.colname Is Null

@mavnn points out that the fields may be "empty" while not being Null, which is a very common problem. I believe on principle (and consistent with my understanding of the official SQL standards) that fields should be initialized as Null and should not allow zero-length strings. It is certainly possible in some applications that one might want to distinguish Null, i.e., value not yet supplied, from blank (zero-length string), i.e., value known to be blank. But if that's part of the application design, then the user should know that criteria on such fields need to consider whether one or both should be included (i.e., both Null and <>"" or one or the other).

From my point of view, it was unfortunate that the the old default for text fields (where AllowZLS defaulted to FALSE) was changed in Access 2003 to allow ZLS's by default. This means that many people who don't notice that AllowZLS is set to TRUE when they create their tables end up with ZLS's stored in their text fields without intending to do so (and importing a table from a previous version also defaults to TRUE).

While testing for Null and ="" will make the WHERE clause that is seeking all "empty" fields work as expected, the permanent fix is to change the field definition to disallow ZLS's. But do note that changing AllowZLS to FALSE does not clear the existing ZLS's -- you have to run a SQL UPDATE to remove them.

Last of all, in using parameters, it is better to declare them such that the values that the user can input are restricted to appropriate values. If the field is numeric, you to limit it to numeric values, if a date, date values, if text or memo, to text:

  PARAMETERS [User Prompt] Long;
  UPDATE MyTable SET LongIntegerColumn = [User Prompt]

  PARAMETERS [User Prompt] DateTime;
  UPDATE MyTable SET DateColumn = [User Prompt]

  PARAMETERS [User Prompt] Text ( 255 );
  UPDATE MyTable SET TextColumn = [User Prompt]

Note that with Text(255) as your parameter type, anything supplied by the user is truncated to 255 characters, even if it's longer than that (it would be a pretty unusual situation where'd you'd need that). For values longer than that (such as memo fields), you omit the text length declaration:

  PARAMETERS [User Prompt] Text;
  UPDATE MyTable SET TextColumn = [User Prompt]

In any event, I think so-called anonymous parameters are not too helpful, as you aren't leveraging the power of parameters to restrict data type of input criteria.

Answer 5

I don't believe that replacing the NULL value with your own 'magic' value ? will cause you anything but further pain.

Here's hoping you may draw inspiration from this article:

How To Handle Missing Information Without Using (some magic value)