Blank/empty field confusion

I'm quite new to Access DB design.

My problem is that I am not sure how certain fields that are left
blank/empty are treated. Is there in fact "something" inside that field.
This relates to a DB search form which I have in Visual Web Developer 2008 -
it has 5 fields - 3 text, 2 drop-down. It seems that when this search form
queries the Access DB, it is behaving like a blank/empty field in a record
in fact has something inside it. Is this correct? How can I get the empty
fields in the DB to be ignored during the search process?

Thanks!

Mart

"Martin Stabrey" wrote in message
...
I'm quite new to Access DB design.

My problem is that I am not sure how certain fields that are left
blank/empty are treated. Is there in fact "something" inside that field.
This relates to a DB search form which I have in Visual Web Developer
2008 - it has 5 fields - 3 text, 2 drop-down. It seems that when this
search form queries the Access DB, it is behaving like a blank/empty field
in a record in fact has something inside it. Is this correct? How can I
get the empty fields in the DB to be ignored during the search process?

Thanks!

Mart



If the AllowZeroLengthStrings property is set to True, then a field that
appears to be empty may contain either a null value or an empty string. To
test for these two possible values in .NET, you'll need to compare against
both DbNull and String.Empty.

--
Brendan Reynolds

Thanks Brendan!

Can the AllowZeroLengthStrings be changed after data has already been
entered in the DB so that it affects all data in the DB?
I must admit to not entirely knowing what you mean, or knowing what to do
about the comparing of DbNull and String.empty.

Mart

"Brendan Reynolds" wrote in message
...
"Martin Stabrey" wrote in message
...
I'm quite new to Access DB design.

My problem is that I am not sure how certain fields that are left
blank/empty are treated. Is there in fact "something" inside that field.
This relates to a DB search form which I have in Visual Web Developer
2008 - it has 5 fields - 3 text, 2 drop-down. It seems that when this
search form queries the Access DB, it is behaving like a blank/empty
field in a record in fact has something inside it. Is this correct? How
can I get the empty fields in the DB to be ignored during the search
process?

Thanks!

Mart



If the AllowZeroLengthStrings property is set to True, then a field that
appears to be empty may contain either a null value or an empty string. To
test for these two possible values in .NET, you'll need to compare against
both DbNull and String.Empty.

--
Brendan Reynolds

The AllowZeroLength property is a property of text fields that determines if
a zero length string (a string containing no characters) is permitted in
that field. You'd have to change it on each individual text field, and (to
the best of my memory) it would not cause any changes to data already in the
database, it would only prevent any zero length strings being entered in the
future. To change data already in the database, you'd need to run a bunch of
update queries, similar to the following example ...

UPDATE SomeTable SET SomeField = Null WHERE SomeField = ""

That's two empty quotes with nothing (not even a space) between them at the
end.

I've been working in Access and away from .NET for a while, so I'm a bit
rusty on the exact syntax you need to use in .NET to compare a value with
DbNull and String.Empty, and the details will depend on just what you're
doing in which version of .NET, and whether you're using VB.NET or C# or
some other language. As an example, here's something that I wrote way back
to return the value of a column in a ADO.NET datareader as a string,
converting null values to empty strings ...

private string GetString(System.Data.IDataReader dr, string columnName)
{
if (dr.IsDBNull(dr.GetOrdinal(columnName)))
return string.Empty;
else
return (string)dr[columnName];
}

If you're still stuck, you might want to try asking the question in an
ADO.NET forum, or a VB.NET or C# forum, depending on which language you're
using.

--
Brendan Reynolds

"Martin Stabrey" wrote in message
...
Thanks Brendan!

Can the AllowZeroLengthStrings be changed after data has already been
entered in the DB so that it affects all data in the DB?
I must admit to not entirely knowing what you mean, or knowing what to do
about the comparing of DbNull and String.empty.

Mart

"Brendan Reynolds" wrote in message
...
"Martin Stabrey" wrote in message
...
I'm quite new to Access DB design.

My problem is that I am not sure how certain fields that are left
blank/empty are treated. Is there in fact "something" inside that field.
This relates to a DB search form which I have in Visual Web Developer
2008 - it has 5 fields - 3 text, 2 drop-down. It seems that when this
search form queries the Access DB, it is behaving like a blank/empty
field in a record in fact has something inside it. Is this correct? How
can I get the empty fields in the DB to be ignored during the search
process?

Thanks!

Mart



If the AllowZeroLengthStrings property is set to True, then a field that
appears to be empty may contain either a null value or an empty string.
To test for these two possible values in .NET, you'll need to compare
against both DbNull and String.Empty.

--
Brendan Reynolds

Thanks Brendan. Will give it a try.

"Brendan Reynolds" wrote in message
...

The AllowZeroLength property is a property of text fields that determines
if a zero length string (a string containing no characters) is permitted
in that field. You'd have to change it on each individual text field, and
(to the best of my memory) it would not cause any changes to data already
in the database, it would only prevent any zero length strings being
entered in the future. To change data already in the database, you'd need
to run a bunch of update queries, similar to the following example ...

UPDATE SomeTable SET SomeField = Null WHERE SomeField = ""

That's two empty quotes with nothing (not even a space) between them at
the end.

I've been working in Access and away from .NET for a while, so I'm a bit
rusty on the exact syntax you need to use in .NET to compare a value with
DbNull and String.Empty, and the details will depend on just what you're
doing in which version of .NET, and whether you're using VB.NET or C# or
some other language. As an example, here's something that I wrote way back
to return the value of a column in a ADO.NET datareader as a string,
converting null values to empty strings ...

private string GetString(System.Data.IDataReader dr, string columnName)
{
if (dr.IsDBNull(dr.GetOrdinal(columnName)))
return string.Empty;
else
return (string)dr[columnName];
}

If you're still stuck, you might want to try asking the question in an
ADO.NET forum, or a VB.NET or C# forum, depending on which language you're
using.

--
Brendan Reynolds

"Martin Stabrey" wrote in message
...
Thanks Brendan!

Can the AllowZeroLengthStrings be changed after data has already been
entered in the DB so that it affects all data in the DB?
I must admit to not entirely knowing what you mean, or knowing what to do
about the comparing of DbNull and String.empty.

Mart

"Brendan Reynolds" wrote in message
...
"Martin Stabrey" wrote in message
...
I'm quite new to Access DB design.

My problem is that I am not sure how certain fields that are left
blank/empty are treated. Is there in fact "something" inside that
field.
This relates to a DB search form which I have in Visual Web Developer
2008 - it has 5 fields - 3 text, 2 drop-down. It seems that when this
search form queries the Access DB, it is behaving like a blank/empty
field in a record in fact has something inside it. Is this correct? How
can I get the empty fields in the DB to be ignored during the search
process?

Thanks!

Mart



If the AllowZeroLengthStrings property is set to True, then a field that
appears to be empty may contain either a null value or an empty string.
To test for these two possible values in .NET, you'll need to compare
against both DbNull and String.Empty.

--
Brendan Reynolds

Hi Brendan,

Nice to see you back in this newsgroup!

...and (to the best of my memory) it would not cause any changes to data
already in the database, it would only prevent any zero length strings being
entered in the future.

This is a correct statement.

Martin:
Access MVP Allen Browne has an article that explains more about ZLS (zero
length string) vs. nulls in Access/JET applications, available he

Problem properties
http://allenbrowne.com/bug-09.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Brendan Reynolds" wrote:


The AllowZeroLength property is a property of text fields that determines if
a zero length string (a string containing no characters) is permitted in
that field. You'd have to change it on each individual text field, and (to
the best of my memory) it would not cause any changes to data already in the
database, it would only prevent any zero length strings being entered in the
future. To change data already in the database, you'd need to run a bunch of
update queries, similar to the following example ...

UPDATE SomeTable SET SomeField = Null WHERE SomeField = ""

That's two empty quotes with nothing (not even a space) between them at the
end.

I've been working in Access and away from .NET for a while, so I'm a bit
rusty on the exact syntax you need to use in .NET to compare a value with
DbNull and String.Empty, and the details will depend on just what you're
doing in which version of .NET, and whether you're using VB.NET or C# or
some other language. As an example, here's something that I wrote way back
to return the value of a column in a ADO.NET datareader as a string,
converting null values to empty strings ...

private string GetString(System.Data.IDataReader dr, string columnName)
{
if (dr.IsDBNull(dr.GetOrdinal(columnName)))
return string.Empty;
else
return (string)dr[columnName];
}

If you're still stuck, you might want to try asking the question in an
ADO.NET forum, or a VB.NET or C# forum, depending on which language you're
using.

--
Brendan Reynolds

"Tom Wickerath" AOS168b AT comcast DOT net wrote in message
news
Hi Brendan,

Nice to see you back in this newsgroup!

Thanks Tom! Best wishes to yourself and the other Access MVPs, present and
past.

--
Brendan Reynolds