Date criteria

[BruceM]

A query includes an expiration date that can be several years in the future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I would not
have a field name with a space, but I inherited the database. For the
criteria to retrieve only records with an expiration date between one month
in the past and one month in the future I use this:

Between DateSerial(Year(Date())+1,Month(Date())-1,Day(Date())) And
DateSerial(Year(Date())+1,Month(Date())+1,Day(Date())-1)

In the next three lines I repeat the criteria, except with +2, +3, and +4
for the Year part of DateSerial. +4 is as high as I ever need to go. It
works, but it seems awfully clumsy. I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply criteria I
received a Data type mismatch in criteria expression error message. Here is
the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

I also created query fields using DatePart to extract Month and Day, and
used those fields in the criteria instead of [EXP DATE], but again it did not
work. Can what I am attempting be done?

 

[ricol]

Have you looked on the DateAdd function in the Help, i.e
DateAdd('m',-1,Date()) etc

"BruceM" skrev:

 

[Dale Fye]

Bruce,

The first thing I would do is convince your boss to purchase Speed Ferret.
This is a great program for making global changes in an Access database.
You can change field names, control names, form names, etc...
If you want to change the field name [EXP DATE] to [EXP_DATE], it will
search every form, module, report, table, query, ... and find every instance
of the search string in the mdb. It will then allow you to browse each one
to make sure you actually want to make the changes. It will even allow you
to restore it to the original version if you make a mistake and the program
bombs (although I always make a backup before I use it, I have never needed
to use the backup). It will pay for itself after 2 or 3 uses.

As I understand it, you want to select those record where the [EXP DATE]
value is between todays date, plus or minus one month. Try for your
criteria

WHERE [EXP DATE]
BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date())

HTH
Dale

 

[BruceM]

Thanks for the replies. In answer to the suggestion to use DateAdd, I
already tried that. DateAdd("m",1,Date() produces results other than one
month from the current date when applied to a DateSerial expression.
The problem is not with the [EXP DATE] field, which is a date field.
DateAdd works as expected there. The problem is that when a vendor's
certificate expires on 1/15/06 we need to send something to them on 1/15/05.
If it expires on 1/15/07 we need to send them something on 1/15/05 and
1/15/06. The reminder goes out a month before the expiration month/day, can
be generated up to a month afterward. After that they receive a second
notice, which is another query and report.
I added ReviewDate to the query as a calculated field:
ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))
It looks right (if the expiration date is 1/15/06, ReviewDate shows
1/15/05). I had hoped I could evaluate the result as if it was a date, but
any attempt to use DateSerial (adding or subtracting 1 for Month or 30 for
Day) or DateAdd (the expression you suggested) as the criteria for ReviewDate
returns the Data type mismatch in criteria expression error message. The
problem seems to be that ReviewDate is not a date field, so date functions
cannot work with the result. Literal dates don't work either -- same error
message.

Bruce,

The first thing I would do is convince your boss to purchase Speed Ferret.
This is a great program for making global changes in an Access database.
You can change field names, control names, form names, etc...
If you want to change the field name [EXP DATE] to [EXP_DATE], it will
search every form, module, report, table, query, ... and find every instance
of the search string in the mdb. It will then allow you to browse each one
to make sure you actually want to make the changes. It will even allow you
to restore it to the original version if you make a mistake and the program
bombs (although I always make a backup before I use it, I have never needed
to use the backup). It will pay for itself after 2 or 3 uses.

As I understand it, you want to select those record where the [EXP DATE]
value is between todays date, plus or minus one month. Try for your
criteria

WHERE [EXP DATE]
BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date())

HTH
Dale

A query includes an expiration date that can be several years in the future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I would not
have a field name with a space, but I inherited the database. For the
criteria to retrieve only records with an expiration date between one month
in the past and one month in the future I use this:

Between DateSerial(Year(Date())+1,Month(Date())-1,Day(Date())) And
DateSerial(Year(Date())+1,Month(Date())+1,Day(Date())-1)

In the next three lines I repeat the criteria, except with +2, +3, and +4
for the Year part of DateSerial. +4 is as high as I ever need to go. It
works, but it seems awfully clumsy. I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply criteria I
received a Data type mismatch in criteria expression error message. Here is
the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

I also created query fields using DatePart to extract Month and Day, and
used those fields in the criteria instead of [EXP DATE], but again it did not
work. Can what I am attempting be done?

 

[BruceM]

Thanks for the information about Speed Ferret. I read something about that
recently. I think it will be worth getting, but for now I changed the field
name in the table and the query with no resultant change in the outcome and
the error message.

Thanks for the replies. In answer to the suggestion to use DateAdd, I
already tried that. DateAdd("m",1,Date() produces results other than one
month from the current date when applied to a DateSerial expression.
The problem is not with the [EXP DATE] field, which is a date field.
DateAdd works as expected there. The problem is that when a vendor's
certificate expires on 1/15/06 we need to send something to them on 1/15/05.
If it expires on 1/15/07 we need to send them something on 1/15/05 and
1/15/06. The reminder goes out a month before the expiration month/day, can
be generated up to a month afterward. After that they receive a second
notice, which is another query and report.
I added ReviewDate to the query as a calculated field:
ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))
It looks right (if the expiration date is 1/15/06, ReviewDate shows
1/15/05). I had hoped I could evaluate the result as if it was a date, but
any attempt to use DateSerial (adding or subtracting 1 for Month or 30 for
Day) or DateAdd (the expression you suggested) as the criteria for ReviewDate
returns the Data type mismatch in criteria expression error message. The
problem seems to be that ReviewDate is not a date field, so date functions
cannot work with the result. Literal dates don't work either -- same error
message.

Bruce,

The first thing I would do is convince your boss to purchase Speed Ferret.
This is a great program for making global changes in an Access database.
You can change field names, control names, form names, etc...
If you want to change the field name [EXP DATE] to [EXP_DATE], it will
search every form, module, report, table, query, ... and find every instance
of the search string in the mdb. It will then allow you to browse each one
to make sure you actually want to make the changes. It will even allow you
to restore it to the original version if you make a mistake and the program
bombs (although I always make a backup before I use it, I have never needed
to use the backup). It will pay for itself after 2 or 3 uses.

As I understand it, you want to select those record where the [EXP DATE]
value is between todays date, plus or minus one month. Try for your
criteria

WHERE [EXP DATE]
BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date())

HTH
Dale

A query includes an expiration date that can be several years in the future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I would not
have a field name with a space, but I inherited the database. For the
criteria to retrieve only records with an expiration date between one month
in the past and one month in the future I use this:

Between DateSerial(Year(Date())+1,Month(Date())-1,Day(Date())) And
DateSerial(Year(Date())+1,Month(Date())+1,Day(Date())-1)

In the next three lines I repeat the criteria, except with +2, +3, and +4
for the Year part of DateSerial. +4 is as high as I ever need to go. It
works, but it seems awfully clumsy. I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply criteria I
received a Data type mismatch in criteria expression error message. Here is
the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

I also created query fields using DatePart to extract Month and Day, and
used those fields in the criteria instead of [EXP DATE], but again it did not
work. Can what I am attempting be done?

 

[Dale Fye]

Bruce,

Have you tried the cDate function? This function changes text strings and
numbers into dates (if they are formatted properly).

OK, I misunderstood your previous posts. How often do you send these things
out? Daily, or once a month. If once a month, how about using this
criteria:

WHERE Date() BETWEEN DateAdd("m", -13, [EXP_DATE]) AND DateAdd("m", -12,
[EXP_DATE])
OR Date() BETWEEN DateAdd("m", -25, [EXP_DATE]) AND
ateAdd("m", -24, [EXP_DATE])

Or, you could parameterize the query, so you could put in the first day of
the month, regardless of when in the month you wanted to do this.

WHERE [What date?] BETWEEN DateAdd("m", -13, [EXP_DATE]) AND
DateAdd("m", -12, [EXP_DATE])
OR [What date?] BETWEEN DateAdd("m", -25, [EXP_DATE]) AND
DateAdd("m", -24, [EXP_DATE])

HTH
Dale

Thanks for the replies. In answer to the suggestion to use DateAdd, I
already tried that. DateAdd("m",1,Date() produces results other than one
month from the current date when applied to a DateSerial expression.
The problem is not with the [EXP DATE] field, which is a date field.
DateAdd works as expected there. The problem is that when a vendor's
certificate expires on 1/15/06 we need to send something to them on 1/15/05.
If it expires on 1/15/07 we need to send them something on 1/15/05 and
1/15/06. The reminder goes out a month before the expiration month/day, can
be generated up to a month afterward. After that they receive a second
notice, which is another query and report.
I added ReviewDate to the query as a calculated field:
ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))
It looks right (if the expiration date is 1/15/06, ReviewDate shows
1/15/05). I had hoped I could evaluate the result as if it was a date, but
any attempt to use DateSerial (adding or subtracting 1 for Month or 30 for
Day) or DateAdd (the expression you suggested) as the criteria for ReviewDate
returns the Data type mismatch in criteria expression error message. The
problem seems to be that ReviewDate is not a date field, so date functions
cannot work with the result. Literal dates don't work either -- same error
message.

Bruce,

The first thing I would do is convince your boss to purchase Speed Ferret.
This is a great program for making global changes in an Access database.
You can change field names, control names, form names, etc...
If you want to change the field name [EXP DATE] to [EXP_DATE], it will
search every form, module, report, table, query, ... and find every instance
of the search string in the mdb. It will then allow you to browse each one
to make sure you actually want to make the changes. It will even allow you
to restore it to the original version if you make a mistake and the program
bombs (although I always make a backup before I use it, I have never needed
to use the backup). It will pay for itself after 2 or 3 uses.

As I understand it, you want to select those record where the [EXP DATE]
value is between todays date, plus or minus one month. Try for your
criteria

WHERE [EXP DATE]
BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date())

HTH
Dale

A query includes an expiration date that can be several years in the future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I

would
not

have a field name with a space, but I inherited the database. For the
criteria to retrieve only records with an expiration date between one month
in the past and one month in the future I use this:

Between DateSerial(Year(Date())+1,Month(Date())-1,Day(Date())) And
DateSerial(Year(Date())+1,Month(Date())+1,Day(Date())-1)

In the next three lines I repeat the criteria, except with +2, +3, and +4
for the Year part of DateSerial. +4 is as high as I ever need to go. It
works, but it seems awfully clumsy. I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply

criteria
I

received a Data type mismatch in criteria expression error message.

Here
is

the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

I also created query fields using DatePart to extract Month and Day, and
used those fields in the criteria instead of [EXP DATE], but again it

did
not

work. Can what I am attempting be done?

 

[Gregory Paret]

A query includes an expiration date that can be several years in the future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I would not
...
...I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply criteria I
received a Data type mismatch in criteria expression error message. Here is

I'm guessing that you have some NULLs in your data. DateSerial does return a
(variant of type) date, but its parameters must be valid numbers. Month(NULL)
is NULL, which will make DateSerial fail. Try wrapping your Month() and Day()
calls with Nz() as in

ReviewDate: DateSerial(Year(Date()),Nz(Month([EXP DATE])),Nz(Day([EXP DATE])))

This has the (possibly interesting) effect of leaving the NULL-dated records
with dates with last year which should make them not be included in queries
with year criteria of this year (via Date()).

the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

If applied to [ReviewDate], aren't they always going to be between
Month(...)-1 and ...+1? On [EXP DATE], it would only match this year, and not
the anniversaries in the future. Try it with

Between DateSerial(Year(Date()),Month(Date())-1,Day(Date())) And
DateSerial(Year(Date()),Month(Date())+1,Day(Date())-1)

or try BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date()) as
suggested by Dale.

-Greg.

 

[BruceM]

Thanks for replying. Nz did the trick. After all of my efforts and
frustration it turned out to be a simple but undocumented aspect of the
DateSerial function. You'd think MS could have mentioned something in Help
or the KB (or anywhere at all) the part about DateSerial not handling null
values. Then again, one would reasonably expect any number of things from
MS. I'm sure the information will prove useful in dealing with other
undocumented limitations of the product.

A query includes an expiration date that can be several years in the future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I would not
...
...I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply criteria I
received a Data type mismatch in criteria expression error message. Here is

I'm guessing that you have some NULLs in your data. DateSerial does return a
(variant of type) date, but its parameters must be valid numbers. Month(NULL)
is NULL, which will make DateSerial fail. Try wrapping your Month() and Day()
calls with Nz() as in

ReviewDate: DateSerial(Year(Date()),Nz(Month([EXP DATE])),Nz(Day([EXP DATE])))

This has the (possibly interesting) effect of leaving the NULL-dated records
with dates with last year which should make them not be included in queries
with year criteria of this year (via Date()).

the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

If applied to [ReviewDate], aren't they always going to be between
Month(...)-1 and ...+1? On [EXP DATE], it would only match this year, and not
the anniversaries in the future. Try it with

Between DateSerial(Year(Date()),Month(Date())-1,Day(Date())) And
DateSerial(Year(Date()),Month(Date())+1,Day(Date())-1)

or try BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date()) as
suggested by Dale.

-Greg.

 

[BruceM]

Thanks for the information about CDate. I didn't know about the type
conversion functions. It still didn't help directly, but I worked out a
Byzantine solution that solved the problem rather indirectly. However, right
after I finished that I learned that DateSerial cannot handle Nulls. As it
happens I had a couple of null date fields, but Nz took care of that.
I had attempted to apply CDate to the expression that combines this year
with the month and day from EXP DATE. It looked right, but again it wouldn't
evaluate as a date (DateAdd led to a Type Mismatch error message). So I
added an experimental field to the table in which 1/15/05 was entered as
text. CDate worked as expected, and I could evaluate the result (Between ...
And). Armed with that information I constructed a small query in which I
used CStr to turn the constructed date into text. It looked right, so I
added that query to the main query, and applied CDate to the field, and at
last it worked! Then I learned about Nz.
The DateAdd ... Or ... DateAdd etc. format you suggested was more compact
than the DateSerial expression I had been using, but accomplished the same
thing. It would work OK with a few "Or" sections, but would be unwieldy with
20 or so, and that situation is coming up elsewhere pretty soon.
Thanks again. Although I didn't end up using CDate, I'm sure I will find
plenty of uses for conversion functions. I would not have known about them
without your taking the time to give some thought to my question.

Bruce,

Have you tried the cDate function? This function changes text strings and
numbers into dates (if they are formatted properly).

OK, I misunderstood your previous posts. How often do you send these things
out? Daily, or once a month. If once a month, how about using this
criteria:

WHERE Date() BETWEEN DateAdd("m", -13, [EXP_DATE]) AND DateAdd("m", -12,
[EXP_DATE])
OR Date() BETWEEN DateAdd("m", -25, [EXP_DATE]) AND
ateAdd("m", -24, [EXP_DATE])

Or, you could parameterize the query, so you could put in the first day of
the month, regardless of when in the month you wanted to do this.

WHERE [What date?] BETWEEN DateAdd("m", -13, [EXP_DATE]) AND
DateAdd("m", -12, [EXP_DATE])
OR [What date?] BETWEEN DateAdd("m", -25, [EXP_DATE]) AND
DateAdd("m", -24, [EXP_DATE])

HTH
Dale

Thanks for the replies. In answer to the suggestion to use DateAdd, I
already tried that. DateAdd("m",1,Date() produces results other than one
month from the current date when applied to a DateSerial expression.
The problem is not with the [EXP DATE] field, which is a date field.
DateAdd works as expected there. The problem is that when a vendor's
certificate expires on 1/15/06 we need to send something to them on 1/15/05.
If it expires on 1/15/07 we need to send them something on 1/15/05 and
1/15/06. The reminder goes out a month before the expiration month/day, can
be generated up to a month afterward. After that they receive a second
notice, which is another query and report.
I added ReviewDate to the query as a calculated field:
ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))
It looks right (if the expiration date is 1/15/06, ReviewDate shows
1/15/05). I had hoped I could evaluate the result as if it was a date, but
any attempt to use DateSerial (adding or subtracting 1 for Month or 30 for
Day) or DateAdd (the expression you suggested) as the criteria for ReviewDate
returns the Data type mismatch in criteria expression error message. The
problem seems to be that ReviewDate is not a date field, so date functions
cannot work with the result. Literal dates don't work either -- same error
message.

Bruce,

The first thing I would do is convince your boss to purchase Speed Ferret.
This is a great program for making global changes in an Access database.
You can change field names, control names, form names, etc...
If you want to change the field name [EXP DATE] to [EXP_DATE], it will
search every form, module, report, table, query, ... and find every instance
of the search string in the mdb. It will then allow you to browse each one
to make sure you actually want to make the changes. It will even allow you
to restore it to the original version if you make a mistake and the program
bombs (although I always make a backup before I use it, I have never needed
to use the backup). It will pay for itself after 2 or 3 uses.

As I understand it, you want to select those record where the [EXP DATE]
value is between todays date, plus or minus one month. Try for your
criteria

WHERE [EXP DATE]
BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date())

HTH
Dale

A query includes an expiration date that can be several years in the
future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I would
not
have a field name with a space, but I inherited the database. For the
criteria to retrieve only records with an expiration date between one
month
in the past and one month in the future I use this:

Between DateSerial(Year(Date())+1,Month(Date())-1,Day(Date())) And
DateSerial(Year(Date())+1,Month(Date())+1,Day(Date())-1)

In the next three lines I repeat the criteria, except with +2, +3, and +4
for the Year part of DateSerial. +4 is as high as I ever need to go. It
works, but it seems awfully clumsy. I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the
above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply criteria
I
received a Data type mismatch in criteria expression error message. Here
is
the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

I also created query fields using DatePart to extract Month and Day, and
used those fields in the criteria instead of [EXP DATE], but again it did
not
work. Can what I am attempting be done?

 

[Brendan Reynolds]

I'm sorry you had a hard time finding the solution, Bruce, but I don't think
it is accurate to say that this is undocumented. It is documented that the
data type of all three arguments to the DateSerial function is Integer, and
it is documented that Variant is the only data type that can accept a Null
value.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks for replying. Nz did the trick. After all of my efforts and
frustration it turned out to be a simple but undocumented aspect of the
DateSerial function. You'd think MS could have mentioned something in
Help
or the KB (or anywhere at all) the part about DateSerial not handling null
values. Then again, one would reasonably expect any number of things from
MS. I'm sure the information will prove useful in dealing with other
undocumented limitations of the product.

A query includes an expiration date that can be several years in the
future.
However, every year on the expiration month and day a notice needs to
be
sent. The field in question is [EXP DATE]. For what its worth I would
not
...
...I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the
above
criteria in [EXP DATE] I applied criteria to filter out records with
the
expiration date this year, then I added a calculated field to the
query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply
criteria I
received a Data type mismatch in criteria expression error message.
Here is

I'm guessing that you have some NULLs in your data. DateSerial does
return a
(variant of type) date, but its parameters must be valid numbers.
Month(NULL)
is NULL, which will make DateSerial fail. Try wrapping your Month() and
Day()
calls with Nz() as in

ReviewDate: DateSerial(Year(Date()),Nz(Month([EXP DATE])),Nz(Day([EXP
DATE])))

This has the (possibly interesting) effect of leaving the NULL-dated
records
with dates with last year which should make them not be included in
queries
with year criteria of this year (via Date()).

the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE]))
And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

If applied to [ReviewDate], aren't they always going to be between
Month(...)-1 and ...+1? On [EXP DATE], it would only match this year, and
not
the anniversaries in the future. Try it with

Between DateSerial(Year(Date()),Month(Date())-1,Day(Date())) And
DateSerial(Year(Date()),Month(Date())+1,Day(Date())-1)

or try BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date()) as
suggested by Dale.

-Greg.

 

[BruceM]

I should have specified that it is not documented in Help. Now that I know
where to look I see that it is obliquely documented there. Information on
DateSerial says that it returns a variant, but that the three named arguments
are integers. Information on integers does not specify that integers cannot
be null, but information on variant says that it is the only data type that
can be null. Now I know that, but do you see why it appears undocumented to
me? It never occurred to me to look up variant to learn that integer cannot
be null. To my way of thinking, documentation includes being able to find
the information at need, not just that it is written down somewhere.
Don't get me wrong, I think Access is a very good product, but I would not
know that were it not for user groups such as this one to explain what is
really going on. If not for my expressing my frustration and your resultant
reply I would not know about variant data types and null values, information
that will surely prove useful.

I'm sorry you had a hard time finding the solution, Bruce, but I don't think
it is accurate to say that this is undocumented. It is documented that the
data type of all three arguments to the DateSerial function is Integer, and
it is documented that Variant is the only data type that can accept a Null
value.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks for replying. Nz did the trick. After all of my efforts and
frustration it turned out to be a simple but undocumented aspect of the
DateSerial function. You'd think MS could have mentioned something in
Help
or the KB (or anywhere at all) the part about DateSerial not handling null
values. Then again, one would reasonably expect any number of things from
MS. I'm sure the information will prove useful in dealing with other
undocumented limitations of the product.

BruceM wrote:
A query includes an expiration date that can be several years in the
future.
However, every year on the expiration month and day a notice needs to
be
sent. The field in question is [EXP DATE]. For what its worth I would
not
...
...I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the
above
criteria in [EXP DATE] I applied criteria to filter out records with
the
expiration date this year, then I added a calculated field to the
query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply
criteria I
received a Data type mismatch in criteria expression error message.
Here is

I'm guessing that you have some NULLs in your data. DateSerial does
return a
(variant of type) date, but its parameters must be valid numbers.
Month(NULL)
is NULL, which will make DateSerial fail. Try wrapping your Month() and
Day()
calls with Nz() as in

ReviewDate: DateSerial(Year(Date()),Nz(Month([EXP DATE])),Nz(Day([EXP
DATE])))

This has the (possibly interesting) effect of leaving the NULL-dated
records
with dates with last year which should make them not be included in
queries
with year criteria of this year (via Date()).

the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE]))
And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

If applied to [ReviewDate], aren't they always going to be between
Month(...)-1 and ...+1? On [EXP DATE], it would only match this year, and
not
the anniversaries in the future. Try it with

Between DateSerial(Year(Date()),Month(Date())-1,Day(Date())) And
DateSerial(Year(Date()),Month(Date())+1,Day(Date())-1)

or try BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date()) as
suggested by Dale.

-Greg.

 

[Brendan Reynolds]

I can understand and sympathise with your frustration, Bruce. I think Access
is a great product, but that doesn't prevent me from cursing it bitterly at
times! My intention was not to criticize, but to clarify.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I should have specified that it is not documented in Help. Now that I know
where to look I see that it is obliquely documented there. Information on
DateSerial says that it returns a variant, but that the three named
arguments
are integers. Information on integers does not specify that integers
cannot
be null, but information on variant says that it is the only data type
that
can be null. Now I know that, but do you see why it appears undocumented
to
me? It never occurred to me to look up variant to learn that integer
cannot
be null. To my way of thinking, documentation includes being able to find
the information at need, not just that it is written down somewhere.
Don't get me wrong, I think Access is a very good product, but I would not
know that were it not for user groups such as this one to explain what is
really going on. If not for my expressing my frustration and your
resultant
reply I would not know about variant data types and null values,
information
that will surely prove useful.

I'm sorry you had a hard time finding the solution, Bruce, but I don't
think
it is accurate to say that this is undocumented. It is documented that
the
data type of all three arguments to the DateSerial function is Integer,
and
it is documented that Variant is the only data type that can accept a
Null
value.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks for replying. Nz did the trick. After all of my efforts and
frustration it turned out to be a simple but undocumented aspect of the
DateSerial function. You'd think MS could have mentioned something in
Help
or the KB (or anywhere at all) the part about DateSerial not handling
null
values. Then again, one would reasonably expect any number of things
from
MS. I'm sure the information will prove useful in dealing with other
undocumented limitations of the product.
:

BruceM wrote:
A query includes an expiration date that can be several years in the
future.
However, every year on the expiration month and day a notice needs
to
be
sent. The field in question is [EXP DATE]. For what its worth I
would
not
...
...I spent a long time trying to get
DateSerial to help me, but it can't since the results of the
function
apparently are not evaluated as a date. For instance, instead of
the
above
criteria in [EXP DATE] I applied criteria to filter out records with
the
expiration date this year, then I added a calculated field to the
query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP
DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07,
for
instance. Looks good, but worthless. When I attempted to apply
criteria I
received a Data type mismatch in criteria expression error message.
Here is

I'm guessing that you have some NULLs in your data. DateSerial does
return a
(variant of type) date, but its parameters must be valid numbers.
Month(NULL)
is NULL, which will make DateSerial fail. Try wrapping your Month()
and
Day()
calls with Nz() as in

ReviewDate: DateSerial(Year(Date()),Nz(Month([EXP DATE])),Nz(Day([EXP
DATE])))

This has the (possibly interesting) effect of leaving the NULL-dated
records
with dates with last year which should make them not be included in
queries
with year criteria of this year (via Date()).

the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE]))
And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

If applied to [ReviewDate], aren't they always going to be between
Month(...)-1 and ...+1? On [EXP DATE], it would only match this year,
and
not
the anniversaries in the future. Try it with

Between DateSerial(Year(Date()),Month(Date())-1,Day(Date())) And
DateSerial(Year(Date()),Month(Date())+1,Day(Date())-1)

or try BETWEEN DateAdd("m", -1, Date()) AND DateAdd("m", 1, Date()) as
suggested by Dale.

-Greg.