By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,400 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,400 IT Pros & Developers. It's quick & easy.

Searching for a blank field in an SQL statement

P: 22
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.


Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE [Track Title] = ''")
  2.  
Feb 19 '08 #1
Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE [Track Title] = ''")
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE Len(Nz([Track Title])) = 0;"
  2.  
-Stewart
Feb 19 '08 #2

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE Len(Nz([Track Title])) = 0;"
  2.  
-Stewart
Feb 19 '08 #3

NeoPa
Expert Mod 15k+
P: 31,768
Testing for Null in SQL is best done using the Is Null construct.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL ("UPDATE [TRACK] SET [QueryIdent] = True WHERE [Track Title] Is Null;"
If you want to test for (Null or "") then use :
Expand|Select|Wrap|Line Numbers
  1. 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...
Feb 20 '08 #4

Expert Mod 2.5K+
P: 2,545
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
Feb 20 '08 #5

NeoPa
Expert Mod 15k+
P: 31,768
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.
Feb 20 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.