Query on Blank Date

[yonggie]

I would like to do a query on "Blank Date".

For example, i have 10 fields for my user to fill up. The date shall be input
only when his customer present the discount card. So end of year, he wish to
know how many slots from users in the database unfilled.

i tried the following
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=Null
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=''
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=""

it doesnt work.

Please help.

Thanks.

 

[Armen Stein]

I would like to do a query on "Blank Date".

For example, i have 10 fields for my user to fill up. The date shall be input
only when his customer present the discount card. So end of year, he wish to
know how many slots from users in the database unfilled.

i tried the following
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=Null
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=''
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=""

it doesnt work.

Please help.

Thanks.

First, your table structure isn't normalized. Any time you see
numbered fields (Date_1, Date_2, etc.) you need to evaluate the
structure. A better structure is a related child table, where each
record contains one date.

But to answer your question:

Nothing can *equal* Null, since Null by definition is undefined.

So you can't say FieldName = Null. That will always return false. You
need to use the IsNull function instead.

Like this:
[Information]![Date_1]>#1/1/2008# And IsNull([Information]![Date_2])

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[yonggie]

Each person contains for example 10 dates, but they below to different field.

Im a beginner, so would appreciate if you could enlighten me more.

Thank you

I would like to do a query on "Blank Date".

[quoted text clipped - 12 lines]

Thanks.

First, your table structure isn't normalized. Any time you see
numbered fields (Date_1, Date_2, etc.) you need to evaluate the
structure. A better structure is a related child table, where each
record contains one date.

But to answer your question:

Nothing can *equal* Null, since Null by definition is undefined.

So you can't say FieldName = Null. That will always return false. You
need to use the IsNull function instead.

Like this:
[Information]![Date_1]>#1/1/2008# And IsNull([Information]![Date_2])

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

I would like to do a query on "Blank Date".

For example, i have 10 fields for my user to fill up. The date shall be input
only when his customer present the discount card. So end of year, he wish to
know how many slots from users in the database unfilled.

i tried the following
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=Null
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=''
[Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=""

As Armen says, nothing is equal to NULL (or unequal to it either); and a date
is not a text string so it will never be equal to a zero length string.

Armen's suggestion of using the IsNull() function will work but it will be a
bit better to use the standard SQL syntax:

[Information].[Date_1]>#1/1/2008# And [Information].[Date_2] IS NULL

A period is the proper delimiter for tablename.fieldname references, ! is used
for Forms and other objects.

And... I fully agree with Armen that your table structure *IS WRONG*. It's a
good design for a spreadsheet, but Access isn't a spreadsheet! If you have a
one (Information) to many (dates) relationship, Access will work much better
if you use two tables in a one to many relationship. "Fields are expensive,
records are cheap". Check out some of the database design tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]