Searching for a blank field in an SQL statement

[shredder249]

Hi, im trying to run an update SQL statement which sets the flag (queryident) to ticked if the track title field in that record is blank. However using '' to indicate a blank field does not work. Any ideas? The statment is below.

DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE [Track Title] = ''")
 

[Stewart Ross]

DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE [Track Title] = ''")
 

Hi. You may have null values in your fields rather than empty strings. As nulls will not be found by testing for string length I have used Nz in the code below to return an empty string if the field is null. It also explicitly test for empty strings using the Len function:

DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE Len(Nz([Track Title])) = 0;"
 

-Stewart

[shredder249]

Thanks! All working fine now.

Hi. You may have null values in your fields rather than empty strings. As nulls will not be found by testing for string length I have used Nz in the code below to return an empty string if the field is null. It also explicitly test for empty strings using the Len function:

DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE Len(Nz([Track Title])) = 0;"
 

-Stewart

[NeoPa]

Testing for Null in SQL is best done using the Is Null construct.

DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE [Track Title] Is Null;"

If you want to test for (Null or "") then use :

DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE Nz([Track Title], '') = '';"

The second version is very little different from the previous post. The first is recommended where possible as it runs more efficiently

NB. The quotes within the SQL string are (') rather than ("). I think you understand this already but for anyone else...

[Stewart Ross]

Testing for Null in SQL is best done using the Is Null construct....

Hi NeoPa. IsNull in this case would leave the potential for table lines which contain truly zero-length strings not being updated, hence the suggested use of Nz instead (copes with both cases, even if one predominates, or is the only situation at present).

-Stewart

[NeoPa]

Indeed you're right Stewart.
A couple of small points worth noting though :

1. I was referring to the "Is Null" SQL construct rather than the IsNull() VBA function. This runs faster (as it's a SQL construct) for larger, more complicated queries. Only useful for Null checks though.
2. My second example was very similar to yours except I excluded the Len() part. This (either of them - using Nz()) is a good example of covering the situation where Nulls OR empty strings must be handled.

I would have to say that, in my experience, it's very rare for tables to allow both empty strings AND nulls.

By the way Stewart, welcome to TheScripts. We've not crossed paths much before, but I've already heard ABOUT you. Keep up the good work.