Automatically changing a blank field into a null value? (1 Viewer)

[Minddumps]

SOLVED--SEE BELOW TRAFFIC

background of what I'm trying to accomplish: I have set-up a column in a query to count the amount of null values for each record. However, when a field has information and then is deleted, that field is now read as blank (or 0 value) instead of null and my count is thrown off. I used an update query to change those blank fields into nulls.

Ok this being said, I do not want to have to keep changing each field each time I find blanks, so my question is, is there a function or macro or module that will automatically change a newly created blank field into a "null value"?

 

[Rx_]

Is this what you are looking for?
On the Table design - set the default value. (see attachment)

Or:
If in the process of using existing data (someone elses data) it is possible to create a query that will return nulls:
In your query Design View - a formula can be added. The attachment shows how to use a formula.
Any of the "Is.." vba functions (e.g. IsNull([field name])
will work here.
Sometimes a business rule is more complicated.
Creating for example a function in the Modules - and using a Case statement or even more comples codes could be necessary.
Those functions can be called in the Query Design view the same as the built in formulas.

The Immediate If statment is:
IIf (FunctionName([Field]), value if true, value if false)
The value if true can be a field name as shown on the attachment.

 

[tejasdave]

hmm......quite interesting piece.

 

[Minddumps]

First, thank you so much for your reply

Is this what you are looking for?
On the Table design - set the default value. (see attachment)

The default value: I don't think would work if the field has already had information but then deleted. Please correct me if I'm wrong, but I believe once the field has had info, it wouldn't return to the default value?

The Immediate If statment is:
IIf (FunctionName([Field]), value if true, value if false)
The value if true can be a field name as shown on the attachment.

Inputting this for each of my columns might be a good fix except that I don't know how to then calculate those.

NumberOfNulls: IIf(IsNull([Last Inspection Date]),1,0)+IIf(IsNull([Friend Page URL]),1,0)+IIf(IsNull([Username]),1,0)+IIf(IsNull([Password]),1,0)+IIf(IsNull([email address]),1,0)+IIf(IsNull([Fan Page URL]),1,0)

--this counts my nulls, but not blanks. I replaced 'Last Inspection Date' with 'MyNullFix' created using your example but I was then asked for a paramater... obviously I was doing it wrong lol, so is there another way?

 

[Rx_]

Lets go back to what you would like to do and get a good solution for you.
For example, do you already have a table with data (i.e. empty string) that could be updated to nulls, and then continue with nulls from that point forward.
Deleting might leave it with an empty string instead of null. It would take more code to change that on an event.
In your formula, you can check for Null, but also check for LEN and see if it is at least the minimum value. (e.g. a password must be 8 char in length).

Are you trying to check for null or empty (or possibly short) strings?

Did each Iff statement run OK by itself in its own column?
Sometimes you may need to add [MyTableName].[Friend Page URL] to qualify the field if they are from different tables.
Maybe start with a column for each IIf statement. If only one column has an error, you can look at your formula better.
Just tested this quickly, it returned 2 (the expected response)
Substitute the "+" with a "&" and it returned 11 (the expected respons).

MynullTest: IIf(Not IsNull([Well_Name]),1,0)+IIf(Not IsNull([Well_Name]),1,0)

Break up your IIf statements one per column to see if the problem is easier to find. My guess is that your using different tables.

 

[Minddumps]

Lets go back to what you would like to do and get a good solution for you.

I will have over 350 users, each of which manage about 30 different records. The information within the records is updated fairly often. If there is text in one of the fields that I see is incorrect, I will delete it so that they can update it later with the correct text needed. If I delete it, instead of the blank returning null, it returns empty which is why the code I provided in the earlier post doesn't work properly as it only counts the 'nulls" and not the 'emptys'. The reason I need to be able to count both the nulls and empties is because I pull a report on which fields are missing for the records.

For example, do you already have a table with data (i.e. empty string) that could be updated to nulls, and then continue with nulls from that point forward.

This is one solution... by using an update query to change all empties into nulls, but I don't want to have to do this everytime a field text is deleted.

Deleting might leave it with an empty string instead of null. It would take more code to change that on an event.

Yes you are right, but in some of these cases deleting is unavoidable and that might actually be the best solution I'm looking for if you know how to accomplish that or could direct me where to research it perhaps? lol

In your formula, you can check for Null, but also check for LEN and see if it is at least the minimum value. (e.g. a password must be 8 char in length).

Interesting, but unfortunately it still leaves me with the problem of how to count them.

Are you trying to check for null or empty (or possibly short) strings?

I'm trying to find a way to count both null or empty so I don't have to keep changing empty to null using the update query I explained above.

Did each Iff statement run OK by itself in its own column?
Sometimes you may need to add [MyTableName].[Friend Page URL] to qualify the field if they are from different tables.
Maybe start with a column for each IIf statement. If only one column has an error, you can look at your formula better.
Just tested this quickly, it returned 2 (the expected response)
Substitute the "+" with a "&" and it returned 11 (the expected respons).

I believe this error checking would be to see if my count returns an error, but the code I provided works perfectly--if only checking for nulls--I don't get an error when trying to count empties, they are simply excluded from the count

 

[Minddumps]

Great news for anyone searching for this same issue!!!

I believe I have stumbled across two possible solutions:

1st: Open up the table in design vew, for every text, memo, or hyperlink column that you want to exclude blanks from and only count them as null change the 'Allow Zero length' property to No. More information on what this does can be found here: http://msdn.microsoft.com/en-us/library/aa193988(v=office.10).aspx

2nd: Have your system run a loop code to change all blanks to null automatically. *This code cannot be run on while a form that has the information to edit belonging to a table is open.
*So instead I would place this code to run on a cmdbutton that ensures the form with information is closed and opens another form where my report selections are located. In that command button is a macro to open the form and runcode: call function.

the function is entered in a my basUtilityFunctions module:

 Function FixZLS()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Const conPropName = "AllowZeroLength"
    Const conPropValue = False
    
    Set db = CurrentDb()
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.name <> "Switchboard Items" Then
                For Each fld In tdf.Fields
                    If fld.Properties(conPropName) Then
                        Debug.Print tdf.name & "." & fld.name
                        fld.Properties(conPropName) = conPropValue
                    End If
                Next
            End If
        End If
    Next
        
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

More information on this can be found here: http://allenbrowne.com/bug-09.html

I have tested both and so far they seem to solve my problem, if anyone has better solutions or sees a problem with either solution please let us know! Thank you!