Writing to Null

[Charles2019]

I've been running this query IIf([Agent Name] Is Null,"NoName",[Agent Name]) to write NoName to a Null field. Recently, the statement will not write the value in the table with the Null field. I import the table from Excel and have used the LEN statement in Excel to look for hidden characters in the field. Any thoughts on what else I can check or change in the query statement?

 

[Isaac]

Maybe it's importing as a zls instead? zero length string

 

[plog]

Betting the house on Isaac being right. You can catch Null and empty strings by using NZ to convert nulls to empty strings then testing the size:

YourFieldName: IIf(Len(Nz([Agent Name], ""))>0,[Agent Name], "NoName")

And your doing yourself (or me) any favors with spaces in your field names. Make it AgentName

 

[Charles2019]

Betting the house on Isaac being right. You can catch Null and empty strings by using NZ to convert nulls to empty strings then testing the size:

YourFieldName: IIf(Len(Nz([Agent Name], ""))>0,[Agent Name], "NoName")

And your doing yourself (or me) any favors with spaces in your field names. Make it AgentName

I tried your suggested statement and it wasn't successful in writing to the field. Agree on the space in the naming, the entire header row from the import is that way :-( I did an ISBLANK statement in Excel to see what it returned and I got a zero value in the field. So, I'm thinking that's what in the cell.

 

[Charles2019]

I tried your suggested statement and it wasn't successful in writing to the field. Agree on the space in the naming, the entire header row from the import is that way :-( I did an ISBLANK statement in Excel to see what it returned and I got a zero value in the field. So, I'm thinking that's what in the cell.

Success, thanks for all of the help. Pulled a new file, imported it, used the statement above and presto, the field was populated.

 

[Pat Hartman]

IIf([Agent Name] Is Null,"NoName",[Agent Name])

Should be

IIf(IsNull([Agent Name]),"NoName",[Agent Name])

IsNull() is a VBA function which is what you want to use within another VBA function. "Is Null" is a SQL expression).

I think your original expression should raise an error. It is possible that Access has changed VBA to recognize "Is Null" but it is also possible that SQL is simply ignoring something it doesn't understand.

 

[Isaac]

IIf([Agent Name] Is Null,"NoName",[Agent Name])

Should be

IIf(IsNull([Agent Name]),"NoName",[Agent Name])

IsNull() is a VBA function which is what you want to use within another VBA function. "Is Null" is a SQL expression).

I think your original expression should raise an error. It is possible that Access has changed VBA to recognize "Is Null" but it is also possible that SQL is simply ignoring something it doesn't understand.

I thought OP was writing a query?

 

[Galaxiom]

IIf([Agent Name] Is Null,"NoName",[Agent Name])

Should be

IIf(IsNull([Agent Name]),"NoName",[Agent Name])

IsNull() is a VBA function which is what you want to use within another VBA function. "Is Null" is a SQL expression).

I think your original expression should raise an error. It is possible that Access has changed VBA to recognize "Is Null" but it is also possible that SQL is simply ignoring something it doesn't understand.

Right idea but based on wrong information.

Iif() is also an engine function. The engine version and the VBA version differ in that one of them evaluates the arguments for both outcomes while the other only evaluates the side that is the result of the test. Forget which way around they are.

Best to continue using Is Null with Iif() because Is Null is also evaluated by the engine while IsNull() is a VBA function.

Also, when testing for both Null and NullString it is far better to use:

WHEN fieldname Is Null OR fieldname = ""

than:

WHEN Nz(fieldname,"") =  ""

because the first expression can be evaluated by the engine and use an index if available.

Nz() is a VBA function.

It is better to use Iif() than Nz().

 

[Isaac]

Also, when testing for both Null and NullString it is far better to use

Yeah...avoid rbar

 

[June7]

To handle possibility of Null or empty string: IIf([Agent Name] & "" = "", "NoName", [Agent Name])

But if there are other non-printing characters, none of these expressions will deal with adequately.