MS Access, Text Fields and Null.

[cafeenman]

So this is what I've come up against over and over and have never come to a good resolution.

I do have a resolution but it means having front-end code working.

The problem.

If you create a text field in a table and want it to have an empty string, then it returns Null when you try to read it in code.

If you edit the table directly, put some text in, save it by moving to another record, then move back and deleted the text then you still get the null value in code.

BUT!

If I write vbnullstring to the field from code then it's not null any more.

So to fix the null I have to use a front end to do that.

Which sucks when I copy, say a list of zip-codes or something and paste it into a table that has another text field.

I have to write code to loop through it and set it all to vbnullstring.

What I need is a way (inside the database, not from code) to tell it that an empty string is perfectly acceptable and to stop the null nonsense.

This is what I'm doing now for an existing project.

Some tables have a Definition field and others don't. But all types can be listed in this form which is why there's the "HasDictionary" thing.

This one is simple it's the only field that might have nothing in it.

But in a more normal front-end, there might be a whole lot of text fields that are created with null values.

It's actually why I stopped using all forms of data controls. I got tired of them throwing me null errors and just started rolling my own.

The question: Is there a way to have Access create the new record with an empty, but not null value? Not even a space character (which my version of Access won't let me enter a space character as the default value anyway). I'm talking about from Access, not from any front-end.

Private Sub lstTableEntries_Click()
Dim RST As DAO.Recordset
Dim SQL As String
Dim nPrimaryKey As Long
Dim nRecordcount As Long

On Error GoTo errHandler

nPrimaryKey = Itemdata(lstTableEntries)

If nPrimaryKey <= 0 Then Exit Sub

SQL = "SELECT * FROM " & cmbLookupTables.Text & " WHERE LookupID = " & nPrimaryKey

nRecordcount = OpenRST(RST, SQL, idx_Recordset_Dynaset)

If nRecordcount = 0 Then Exit Sub

With RST

  If HasDefinition(cmbLookupTables.Text) Then

    If IsNull(.Fields("Definition")) Then ' // Begin stupidity.

      .Edit

        .Fields("Definition") = vbNullString

      .Update                             ' / End of the stupids.

    End If

    txtDefinition.Text = .Fields("Definition")

  End If

End With

txtLookupEntry.Text = RST.Fields("Lookup")

lblDefinition.Enabled = txtDefinition.Enabled

cmdDeleteEntry.Enabled = lstTableEntries.ListIndex >= 0

Changed = False

Exit Sub

errHandler:

MsgBox Error & " (" & Err & ")."

End Sub

[cafeenman]

Also too, not that it affects this problem in any way, this is the HasDefinition code which is simply searching for a field named "Definition".

Edit: Also too, this reminds me that I need to make this a generic function instead of single-purpose.

E.g. "Public Function FieldExists(ByRef TableName As String, ByRef FieldName As String) As Boolean" and pass a table and field name.

Should be a 47 second conversion from this to that.

Private Function HasDefinition(ByRef TableName As String) As Boolean
Dim Field As DAO.Field

On Error GoTo errHandler

For Each Field In DB.TableDefs(TableName).Fields

  If StrComp("Definition", Field.Name, vbTextCompare) = 0 Then

    HasDefinition = True

    Exit Function

  End If

Next Field

Exit Function

errHandler:

MsgBox Error & " (" & Err & ")."

End Function

OK, that part is fixed. Still not the problem though.

Public Function FieldExists(ByRef TableName As String, ByRef FieldName As String) As Boolean
Dim Field As DAO.Field

On Error GoTo errHandler

For Each Field In DB.TableDefs(TableName).Fields

  If StrComp(FieldName, Field.Name, vbTextCompare) = 0 Then

    FieldExists = True

    Exit Function

  End If

Next Field

Exit Function

errHandler:

MsgBox Error & " (" & Err & ")."

End Function

[Zvoni]

Errr???

SELECT Id, SomeFields,
Iif(ProblematicField IS NULL, '', ProblematicField) As ProblematicField,
OtherFields......
FROM SomeTable

Or i'm missing the issue completely.....

[cafeenman]

If I'm reading your query correctly, iif isn't selecting the field if it's null.

That skirts the problem of the field being null in the first place.

I also don't know how VB saving vbnullstring is different from the string in the table being empty. Especially after I save something in the field and then delete it later.

I assumed the null was because the field wasn't created until it was used to save disk space or memory or something.

But once I put something in it then it must be created which, assuming I'm right about any of this, that Access is deleting the field entirely when I delete it's contents.

[cafeenman]

Also too, let's say it's a customer record and you're creating a new one.

You have maybe 25 text fields that can be filled in - some required, some optional.

That would get really cumbersome really fast if I had to do that for every possible text field that could be null.

[Arnoutdv]

An empty string is not the same as an empty field in the record of a DB.
In your first posts you are checking the definition of the .Fields instead of checking the value of a cell.
If you don't want to write a value in a new record then don't fill it.
Can you show the code in which you add a new record to the table?

[cafeenman]

I'm about to upload the entire thing to code bank. The problem isn't the writing. If I never ask it what it's value is then there's not a problem. I don't ask when I'm saving. I just save it and that isn't a problem.

It's when I'm trying to read the value from the table to put into various controls that null becomes an issue.

Give it about four minutes and check code bank.

[Zvoni]

I'm about to upload the entire thing to code bank. The problem isn't the writing. If I never ask it what it's value is then there's not a problem. I don't ask when I'm saving. I just save it and that isn't a problem.

It's when I'm trying to read the value from the table to put into various controls that null becomes an issue.

Give it about four minutes and check code bank.

Because you can't assign a NULL-Value to a Control (e.g. TextBox)

My Query above (the IIF) just "translates" any NULL's coming from the Column to an empty string, which a TextBox, Label DOES understand

AND DON'T POST TO CODEBANK!

[Arnoutdv]

The Codebank is not for posting code with problems.

2 ways you can handle returned Null values

Using the 1st method you actively check for Null and when necessary you do additional actions

If IsNull(rsDataSet.Fields(0)) Then
   txtWhatever.Text = ""
Else
   txtWhatever.Text = rsDataSet.Fields(0)
End If

This methods ignores the fact it's Null by appending a empty string to results.
And let the VB6 interpreter/compiler deal with it

   txtWhatever.Text = rsDataSet.Fields(0) & ""

[cafeenman]

The post to code bank is complete code. Why not post?

I see what your code does. Now how are you going to do that when you have First Name, Last Name, Middle Initial, Hobbies, Emergency Contact, etc. - all text fields that may or may not be required?

The solution I'm looking for is how to get rid of the nulls when the record is created. I already have ways to handle the nulls. I want to not have nulls at all.

Just like you can create a new record and have the default for a numeric field be 0, I want the default for a text field to be empty but not null.

[cafeenman]

The Codebank is not for posting code with problems.
[/code]

I'm sorry if I wasn't clear. The code I'm posting is complete and has this problem resolved.

This post is about finding a better resolution than what I'm using.

I didn't post about a problem in Code Bank. I posted code. And a reply with a fix that I broke somehow between the last time I tested it and posting it.

[cafeenman]

This methods ignores the fact it's Null by appending a empty string to results.
And let the VB6 interpreter/compiler deal with it

   txtWhatever.Text = rsDataSet.Fields(0) & ""

That won't raise an error if .Fields(0) is null?

[Arnoutdv]

Not in my experience, but the first method is a cleaner way to deal with Null values

[cafeenman]

The first way is what I'm doing. I just don't like it. Usually because it creeps up in some obscure thing that nobody ever does until two years later when it all crashes because I forgot to check for a null.

[Zvoni]

The post to code bank is complete code. Why not post?

I see what your code does. Now how are you going to do that when you have First Name, Last Name, Middle Initial, Hobbies, Emergency Contact, etc. - all text fields that may or may not be required?

The solution I'm looking for is how to get rid of the nulls when the record is created. I already have ways to handle the nulls. I want to not have nulls at all.

Just like you can create a new record and have the default for a numeric field be 0, I want the default for a text field to be empty but not null.

German Access here

In Access go to your Table and switch to "Design"-Mode.
Choose your Text-Column/Field
Below you should see the Properties of your COlumn.
"Default" is in my Access the 3rd Property from the Top ("Standardwert")
To the right are 3 Points ("ellipses") --> Click there

You get a Dialogue
Choose "Constants" in the Left List, and there should be something "Empty Text" on the right List

[Arnoutdv]

I don't understand what you are referring to:

If IsNull(rsDataSet.Fields(0)) Then
   txtWhatever.Text = ""
Else
   txtWhatever.Text = rsDataSet.Fields(0)
End If

This won't suddenly crash. It's clean and it's obvious how the special case IsNull is handled.
If you have multiple columns in table which are allowed to be NULL then you have to deal with this in your code.
I really don't see the problem.

[Zvoni]

I don't understand what you are referring to:

If IsNull(rsDataSet.Fields(0)) Then
   txtWhatever.Text = ""
Else
   txtWhatever.Text = rsDataSet.Fields(0)
End If

This won't suddenly crash. It's clean and it's obvious how the special case IsNull is handled.
If you have multiple columns in table which are allowed to be NULL then you have to deal with this in your code.
I really don't see the problem.

Or use the IIF in the SELECT-Statement, then it doesn't matter.
Consequence: He has to fully formulate his SELECT-Statements.
No "lazy" way with "SELECT * FROM"

If OP doesn't want to change away from the "SELECT * FROM" then i'm done with him. Then he's on his own.....

[gilman]

Access distinguishes between null values ??and the empty string, if you want to treat null values ??as the empty string the easiest way is to use;

txtWhatever.Text = rsDataSet.Fields("FieldName") & ""

[cafeenman]

I don't understand what you are referring to:

If IsNull(rsDataSet.Fields(0)) Then
   txtWhatever.Text = ""
Else
   txtWhatever.Text = rsDataSet.Fields(0)
End If

This won't suddenly crash. It's clean and it's obvious how the special case IsNull is handled.
If you have multiple columns in table which are allowed to be NULL then you have to deal with this in your code.
I really don't see the problem.

No. I meant stuff I've already written that I forgot to check. Nothing wrong with the code you posted. That's how to prevent the crashing.

[cafeenman]

Or use the IIF in the SELECT-Statement, then it doesn't matter.
Consequence: He has to fully formulate his SELECT-Statements.
No "lazy" way with "SELECT * FROM"

If OP doesn't want to change away from the "SELECT * FROM" then i'm done with him. Then he's on his own.....

First, I only use * when I need the majority of the fields. It's not a thing I just do.

Second, you haven't answered my question of how you're going to handle a query with a boatload of text fields using your iif method. I mean it's fine if it's just one field you're checking.

But if you're pulling in a couple dozen text fields, that's a couple dozen iif statements in your query. Even if there isn't a character-limit on the query, it's still going to be a nightmare to write, debug and maintain.

Can you post any query you've written that pulls in a bunch of text fields please?

[cafeenman]

German Access here

In Access go to your Table and switch to "Design"-Mode.
Choose your Text-Column/Field
Below you should see the Properties of your COlumn.
"Default" is in my Access the 3rd Property from the Top ("Standardwert")
To the right are 3 Points ("ellipses") --> Click there

You get a Dialogue
Choose "Constants" in the Left List, and there should be something "Empty Text" on the right List

I will check that out. If it does what I think it does then it *is* the answer I'm looking for. Thank you.

[k_zeon]

Hi

I use this function to help.

Public Function IsNullEx(ValueToCheck As Variant, varWhatToReturnIfNull) As Variant
If IsNull(ValueToCheck) Then
IsNullEx = varWhatToReturnIfNull
Else
IsNullEx = ValueToCheck
End If
End Function

then you can use

debug.print IsNullEx(rsVar.Fields(0).Value, "")

or

debug.print IsNullEx(rsVar.Fields(0).Value, 0)

[Zvoni]

Can you post any query you've written that pulls in a bunch of text fields please?

My last "Monster" is a fully formulated SELECT-Query, involving 15 CTE's, 30 Base-Tables, some 50 JOINS (all flavours - INNER, LEFT) and some 50 Output-Columns, the Majority of them Text-Columns

In my Query-Designer (for IBM DB2) it's some 200 lines long......

That's for my day-job.

For my own projects (mainly Lazarus/FreePascal with SQLite), i only ever have a single SELECT-Statement in my Frontend:
"SELECT SQLStatement FROM tbl_sql_statements WHERE ID=@paramID"

I store my fully formulated SQL-Statements (all of them. SELECT, UPDATE, INSERT, DELETE) in the Database itself.

In my Frontend i just pull the Statement i want to use from the DB and assign it to the Command-Property (or whatever it's called in vb), set the params i have to set, and fire it off
All my SQL-Statements are tested. I don't debug SQL-Statements in my Frontend-Project.
I write them, i test them, i debug them, BUT ONCE IT WORKS, i rarely touch them again

I can even change my Statement in the DB itself without having to recompile/redistribute the Frontend.
Hell, i can even change Column-Names (for displaying in a Grid) that way

EDIT:
Excerpt from one of my Queries. 144 Lines long

[vb6forever]

I'm with Zvoni (post #15). Just set the default in the DB itself and be done with it. There's no need to compare anything in your code.