Set value as null or ""?

[Vincdc]

Hello:
I have 4 dropdown boxes e.g. A,B,C,D in a form. If I choose certain value in
dropdown box A, then I want dropdown box B and C show nothing, I mean the old
values should be deleted. My question is that should I use the code like A=""
or I should set the value as Null? And how can I set a value as null?
Thanks in advance!

Vincent

 

[Ofer Cohen]

To set a text type field to empty you need to use

Me.[ObjectName] = ""

To set a Numuric field or date field to empty you need to use Null

Me.[ObjectName] = Null

 

[Vincdc]

Hello:
Thanks for your suggestion!
Can I set text type as null or numeric as ""? Any preference on these two?
Thanks!

Vincent

To set a text type field to empty you need to use

Me.[ObjectName] = ""

To set a Numuric field or date field to empty you need to use Null

Me.[ObjectName] = Null

--
Good Luck
BS"D

Hello:
I have 4 dropdown boxes e.g. A,B,C,D in a form. If I choose certain value in
dropdown box A, then I want dropdown box B and C show nothing, I mean the old
values should be deleted. My question is that should I use the code like A=""
or I should set the value as Null? And how can I set a value as null?
Thanks in advance!

Vincent

 

[Ofer Cohen]

No you can't, number and date can be set to Null only, and text can be set to
"", other wise you'll get a Type Mismatch error.

--
Good Luck
BS"D

Hello:
Thanks for your suggestion!
Can I set text type as null or numeric as ""? Any preference on these two?
Thanks!

Vincent

To set a text type field to empty you need to use

Me.[ObjectName] = ""

To set a Numuric field or date field to empty you need to use Null

Me.[ObjectName] = Null

--
Good Luck
BS"D

Hello:
I have 4 dropdown boxes e.g. A,B,C,D in a form. If I choose certain value in
dropdown box A, then I want dropdown box B and C show nothing, I mean the old
values should be deleted. My question is that should I use the code like A=""
or I should set the value as Null? And how can I set a value as null?
Thanks in advance!

Vincent

 

[Vincdc]

Thanks a lot!

No you can't, number and date can be set to Null only, and text can be set to
"", other wise you'll get a Type Mismatch error.

--
Good Luck
BS"D

Hello:
Thanks for your suggestion!
Can I set text type as null or numeric as ""? Any preference on these two?
Thanks!

Vincent

To set a text type field to empty you need to use

Me.[ObjectName] = ""

To set a Numuric field or date field to empty you need to use Null

Me.[ObjectName] = Null

--
Good Luck
BS"D


:

Hello:
I have 4 dropdown boxes e.g. A,B,C,D in a form. If I choose certain value in
dropdown box A, then I want dropdown box B and C show nothing, I mean the old
values should be deleted. My question is that should I use the code like A=""
or I should set the value as Null? And how can I set a value as null?
Thanks in advance!

Vincent

 

[Dirk Goldgar]

To set a text type field to empty you need to use

Me.[ObjectName] = ""

To set a Numuric field or date field to empty you need to use Null

Me.[ObjectName] = Null

Not exactly. A text field can contain a zero-length string ("") or it
can be Null. The two aren't necessarily the same -- a semantic
distinction can be made between them, though you may or may not choose
to make it.

Also, it's possible to set a text field (in the table's design view) to
disallow zero-length strings, in which case 'Me.[FieldName] = ""' will
fail. I almost always use that setting, because I like to be able to
test for an empty field by checking for Null (using the IsNull function
or, in SQL, IS NULL), without having to worry about also checking for a
zero-length string.

So you can certainly set a text field to be "empty" using

Me.[FieldName] = Null

That's what I customarily do. If you choose to represent "empty" with a
zero-length string, you may certainly do so, but bear in mind that it
may complicate your code when it comes time to test whether a field is
empty. It may be Null, or it may be a zero-length string; and Null <>
"".

 

[Ofer Cohen]

Thank Dirk, you are right, I tried it and I succeded applying Null to a text
field.
I just remember always getting an error when I tried to assign Null to a
text field, mybe the problem is that I'm using linked tables from an SQL
Server, I'll try it next week when I'll get back to work, unless you know if
that is the problem.

Thanls again for the clearing that for me.


--
Good Luck
BS"D

To set a text type field to empty you need to use

Me.[ObjectName] = ""

To set a Numuric field or date field to empty you need to use Null

Me.[ObjectName] = Null

Not exactly. A text field can contain a zero-length string ("") or it
can be Null. The two aren't necessarily the same -- a semantic
distinction can be made between them, though you may or may not choose
to make it.

Also, it's possible to set a text field (in the table's design view) to
disallow zero-length strings, in which case 'Me.[FieldName] = ""' will
fail. I almost always use that setting, because I like to be able to
test for an empty field by checking for Null (using the IsNull function
or, in SQL, IS NULL), without having to worry about also checking for a
zero-length string.

So you can certainly set a text field to be "empty" using

Me.[FieldName] = Null

That's what I customarily do. If you choose to represent "empty" with a
zero-length string, you may certainly do so, but bear in mind that it
may complicate your code when it comes time to test whether a field is
empty. It may be Null, or it may be a zero-length string; and Null <>
"".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Nick 'The database Guy']

Just a small point Dirk, I have found that if you use 'If Nz([field
name]) = ""' Access will not differenciate between Null and a zero
length string.

Good Luck

Nick

Thank Dirk, you are right, I tried it and I succeded applying Null to a text
field.
I just remember always getting an error when I tried to assign Null to a
text field, mybe the problem is that I'm using linked tables from an SQL
Server, I'll try it next week when I'll get back to work, unless you know if
that is the problem.

Thanls again for the clearing that for me.


--
Good Luck
BS"D

To set a text type field to empty you need to use

Me.[ObjectName] = ""

To set a Numuric field or date field to empty you need to use Null

Me.[ObjectName] = Null

Not exactly. A text field can contain a zero-length string ("") or it
can be Null. The two aren't necessarily the same -- a semantic
distinction can be made between them, though you may or may not choose
to make it.

Also, it's possible to set a text field (in the table's design view) to
disallow zero-length strings, in which case 'Me.[FieldName] = ""' will
fail. I almost always use that setting, because I like to be able to
test for an empty field by checking for Null (using the IsNull function
or, in SQL, IS NULL), without having to worry about also checking for a
zero-length string.

So you can certainly set a text field to be "empty" using

Me.[FieldName] = Null

That's what I customarily do. If you choose to represent "empty" with a
zero-length string, you may certainly do so, but bear in mind that it
may complicate your code when it comes time to test whether a field is
empty. It may be Null, or it may be a zero-length string; and Null <>
"".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Just a small point Dirk, I have found that if you use 'If Nz([field
name]) = ""' Access will not differenciate between Null and a zero
length string.

Absolutely right, because you're converting any Nulls in the field to a
zero-length string before making your comparison. That's one method,
and another is just to concatenate the field to a ZLS before comparing:

If ([field] & "") = "" Then

or (my favorite)

If Len([field] & vbNullString) = 0 Then

But all of those add overhead I'd rather avoid, and the possibility of a
ZLS in the field prevents the use of SQL criteria like

WHERE [field] IS NULL

to find all the "empty" fields.