Referring to blank cells in a field

[RhondaStockton]

Hi there
I have a report with totals per a specific category. But not all the information is in the table, so there are a few blank cells.
I need to replace these blank cells with "Not Specified" in the report. I was thinking to use an Iif statement, but I'm not sure how to refer to a blank cell (if the cell is blank, "Not specified", [category])
Please could you advise?
Thank you

 

[SydneyGeek]

This can be tricky. Access provides 2 functions -- IsNull and IsBlank (or IsEmpty -- one of these is Access, the other Excel)
I've found that referring to either of them can be a pain. You often don't get what you want.
I'd suggest this:
Build a query with just the field that you want to populate. Try using IsNull or the other options in the Criteria field. See (in datasheet view) if you get what you need.
If so, change the query to an Update query (select the Query menu in Design view). In the Update To row that appears, enter "Not Specified" or whatever text you want -- this is a TEXT field, right?

Click the Run icon (the exclamation mark) and you'll get a warning about changing records. Click OK. Save teh query as FillBlanks (or some other useful name).
Next time you run the report, run this query first.

Denis

 

[mdmilner]

You might try using LEN instead.
Test the field contents for length >0

Depending on field format you may generate an error code which you will have to handle.

Mike