Using DateAdd Field for a Criteria Not Working

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi,
This one has me puzzled, hope you can help. I have used
=[Forms]![Report1]![StartDate] And <=[Forms]![Report1]!
Click to expand...
[EndDate] - and its always worked, but now I'm using it on
a field that is:
NextTrainingDate: DateAdd("d",[NumberofDays],
[DateofTraining])

Number of Days is say 365 and Date of Training is in
MM/DD/YY format - it calculates the right date no problem,
but for example I have a date that comes out to 1/10/2004 -
if I enter my start date as 01/01/2004 and my end date as
01/31/2004 it doesn't show!!! But if I pick 01/01/2000 to
say 12/31/2004 it shows up! Ugh! I'm guessing it has
something to do with how the date is calculated, maybe as
a number?? Please if anyone has any ideas they would be
very appreciated!! I also tried bypassing the DateAdd
function and just adding the appropriate amount of days,
but the results were the same! Please help..I'm pulling
my hairs out - and not just the gray ones :)
 
Dear Gina;

You say "Date of Training is in MM/DD/YY format"

Is "Date of Training" a column in your table? And you have formatted
it IN THE TABLE? Or is this just a normal date/time column stored in
binary without any formatting until you display it.

Within the query, you are working with any date/time values in their
binary sorage, which allows you to use all the date related functions.
If so, then it is not useful to think of it as being formatted in any
particular way. It isn't formatted at all!

Next, you are picking up dates from controls on your form. These will
definitely be in text, and formatted however they are entered. An
input mask is usefull, and it is important to test the values entered
to make sure the ARE dates before allowing the query to be run.

The query can best use these dates converted from text to date/time
values. It is best to use CDate() on them before using them as dates.
Of course, this will fail if they aren't actually dates, hence my
reason for suggesting you not allow the query to be run if they are
not.

Now, you say, "I have a date that comes out to 1/10/2004"

Is this a value in another date/time column in your query? Is it
perhaps a string? Could it also have a time component other than
midnight (a date/time always has a time, but it is 0:00:00 if not
entered). If this date is not a date, but a string, perhaps you need
to convert it to a date/time value before using it as a date, again
with CDate().

The short and long: make sure when you're comparing dates that they
are really date/time values, not strings. And make sure you're aware
there can be times attached of which you're not aware unless you make
sure you are aware.

It is by far best policy not to store dates as strings in tables
unless you label the the column with a name that tells you instantly
it is a string and not a date.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi,
This one has me puzzled, hope you can help. I have used
=[Forms]![Report1]![StartDate] And <=[Forms]![Report1]!
Click to expand...
[EndDate] - and its always worked, but now I'm using it on
a field that is:
NextTrainingDate: DateAdd("d",[NumberofDays],
[DateofTraining])

Number of Days is say 365 and Date of Training is in
MM/DD/YY format - it calculates the right date no problem,
but for example I have a date that comes out to 1/10/2004 -
if I enter my start date as 01/01/2004 and my end date as
01/31/2004 it doesn't show!!! But if I pick 01/01/2000 to
say 12/31/2004 it shows up! Ugh! I'm guessing it has
something to do with how the date is calculated, maybe as
a number?? Please if anyone has any ideas they would be
very appreciated!! I also tried bypassing the DateAdd
function and just adding the appropriate amount of days,
but the results were the same! Please help..I'm pulling
my hairs out - and not just the gray ones :)
Click to expand...
 
Hi Tom,
Sorry for the confusion on the MM/DD/YY format, I just
meant to say that is how it was entered into the table
using an input mask (but actually its MM/DD/YYYY) - the
Training Date field is definitely a Date field. If I do a
criteria on this field it works fine, but if I try using
the DateAdd function to add say 365 days to it, then the
criteria for that newly calculated date doesn't work. It
displays the correct calculated date, but the criteria
doesn't always recognize it as falling in that range. The
calculation is all done in the query. I have four tables,
an employee table, a training table (for training the
employee has taken), a training lookup table, which simply
has all the possible trainings available and then a time
frame table which indicates how often each training needs
to be taken (annual, every two years etc.). This table
also holds the value that is going to be added to the last
training date (ex. annual would be every 365 days). Hope
this doesn't confuse you more. That part doesn't really
matter, because even if I just simply add 365 days in the
query without using the related tables, it still doesn't
work! I tried using CDate but didn't seem to matter, I
used it on DateofTraining in the calcuated
NextTrainingDate (not sure if that is what you meant). I
appreciate your help on this!
Gina
P.S. I also tried using Between in the Criteria and it
didn't make a difference. It is so frustrating because
the query is calcuating the date right, but just not
recognizing it in the criteria. Thanks for all your time.
-----Original Message-----
Dear Gina;

You say "Date of Training is in MM/DD/YY format"

Is "Date of Training" a column in your table? And you have formatted
it IN THE TABLE? Or is this just a normal date/time column stored in
binary without any formatting until you display it.

Within the query, you are working with any date/time values in their
binary sorage, which allows you to use all the date related functions.
If so, then it is not useful to think of it as being formatted in any
particular way. It isn't formatted at all!

Next, you are picking up dates from controls on your form. These will
definitely be in text, and formatted however they are entered. An
input mask is usefull, and it is important to test the values entered
to make sure the ARE dates before allowing the query to be run.

The query can best use these dates converted from text to date/time
values. It is best to use CDate() on them before using them as dates.
Of course, this will fail if they aren't actually dates, hence my
reason for suggesting you not allow the query to be run if they are
not.

Now, you say, "I have a date that comes out to 1/10/2004"

Is this a value in another date/time column in your query? Is it
perhaps a string? Could it also have a time component other than
midnight (a date/time always has a time, but it is 0:00:00 if not
entered). If this date is not a date, but a string, perhaps you need
to convert it to a date/time value before using it as a date, again
with CDate().

The short and long: make sure when you're comparing dates that they
are really date/time values, not strings. And make sure you're aware
there can be times attached of which you're not aware unless you make
sure you are aware.

It is by far best policy not to store dates as strings in tables
unless you label the the column with a name that tells you instantly
it is a string and not a date.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi,
This one has me puzzled, hope you can help. I have used
=[Forms]![Report1]![StartDate] And <=[Forms]![Report1]!
Click to expand...
[EndDate] - and its always worked, but now I'm using it on
a field that is:
NextTrainingDate: DateAdd("d",[NumberofDays],
[DateofTraining])

Number of Days is say 365 and Date of Training is in
MM/DD/YY format - it calculates the right date no problem,
but for example I have a date that comes out to 1/10/2004 -
if I enter my start date as 01/01/2004 and my end date as
01/31/2004 it doesn't show!!! But if I pick 01/01/2000 to
say 12/31/2004 it shows up! Ugh! I'm guessing it has
something to do with how the date is calculated, maybe as
a number?? Please if anyone has any ideas they would be
very appreciated!! I also tried bypassing the DateAdd
function and just adding the appropriate amount of days,
but the results were the same! Please help..I'm pulling
my hairs out - and not just the gray ones :)
Click to expand...

.
Click to expand...
 
Try the following:

1. Declare [Forms]![Report1]![StartDate] and
[Forms]![Report1]![EndDate] as Parameters of DateTime type.
In the Query DesignView, use the Menu

Quries / Parameters ...

to access the Parameters Dialog.

2. If the abouve doesn't work, put the following criteria
in the Column [DateOfTraining], NOT [NextTrainingDate]:

Between ([Forms]![Report1]![StartDate] - TrInterval)
And ([Forms]![Report1]![EndDate] - TrInterval)

TrInterval is 365 as per your example.

3. You may like to try 2 above but use the proper DateAdd
calculations rather than the arithmetic additions /
subtractions.

I had a similar case but involved a different inbuild
function and everything proved what I used was correct but
the Query didn't work correctly. Eventually, I simply
devised a different algorithm to work around it.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top