Update query checking for Is Null 1

[08211987]

Hi,
I have an update query with the following criteria:
Like "*OS:*" Or Like "*Firmware:*" Or Is Null and when there is a match another field gets updated with "ECS Shared". The logic
finds the OS and the Firmware but does not find the Null. I also tried putting the Is Null on the 2nd Criteria line in the query and I tried just a separate query for the Is Null but it never finds a match. The field is defined as Short Text and there are blanks.

Any help would be appreciated!

 

[MajP]

If it is not finding nulls it may be because you have empty strings "" or blanks " " instead. This is hard to create from data input, but if you import data it can happen.

Try creating a calculated field

select trim([yourfield]) as cleanField.... where trim([yourfield]) = ""

See if that returns records. Then you can apply your checks against this field to include is null, "".

 

[MajP]

If you do find you return records on the calculated field, I personally would run an update query to change the blanks and empty strings to null. That way you do not have to check all three cases. In vba code you will often see the check
if trim(Me.SomefieldName & "") = "" then ...

That will check as three cases: Null, "", "

 

[08211987]

Yea this data is brought in from an antiquated system where anything is possible. Is it always good practice to use Trim in such cases?

I tried this in the query design: CCSubgroup: Trim([Compentency Subgroup]) in field name and Is Null in criteria.
Is that what you mean? It still returned 0 records

Thanks!

 

[dhookom]

As MajP suggested there are differences between any string (even zero-length) and Null.

I would use:

Trim([Compentency Subgroup] & "")=""

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Trim([Compentency Subgroup]) in field name and Is Null in criteria.

No, because trim only takes out spaces from strings so it would turn this " " into "" and "" would remain "". It takes out all the spaces on front and back so this “ some string “ becomes “some string”. If you trim an empty string or a string with only spaces it becomes “”. However, if you trim a null it will return null and not “”. But if you first add “” to the null the null becomes “”. Now if you trim that it becomes “” as well. So one check gets all three.
Trim([Compentency Subgroup] & "")=""
You could try
[Compentency Subgroup] = “” or [Compentency Subgroup] is null or [Compentency Subgroup] = “ ”
That gets an empty string, null, and a single space. But it will not find more than one space.
One more thing. You may have other non-printable characters such as carriage returns and line feeds. Trim does not do that and you will need more code to clean that up.

 

[MajP]

If your problem includes hidden characters like line feeds, tabs, carriage returns you can use a UDF

Public Function CleanField(varField As Variant) As Variant
   'turn spaces and nulls to empty strings
   CleanField = varField & ""
   CleanField = Trim(CleanField)
   'Hidden characters are ASCII 0 to 32 but these are the main ones. You can google the table
   'You can loop them all except 32 since that is a space which could be inside your string
   CleanField = Replace(CleanField, Chr(9), "")  'Tab
   CleanField = Replace(CleanField, Chr(10), "") 'Line Feed
   CleanField = Replace(CleanField, Chr(13), "") 'Carriage return
   'either leave anwer as "" string or change all to null
   If CleanField = "" Then CleanField = Null
End Function

You can then use this in an update query to or just in a where clause

SELECT 
  ID,
  cleanField([SpecialCharacters]) AS cleanField, 
   SpecialCharacters
FROM 
   tblSpecialCharacters
WHERE 
  cleanField([SpecialCharacters]) Is Null

My specialCharacters field has nulls, spaces, tabs, etc in the values.

 

[08211987]

I really appreciate both your responses and will need to take some time to study your suggestions because they are a bit over my head, but I will do that this weekend. Thanks so much!

 

[08211987]

MajP,
Thanks so much for the info, I was lucky and got away with the first suggestion this time but will keep your extensive suggestion for when that time arrives. Works perfectly!