Datediff question

[Golfinray]

I have datediff commands in two queries and I am storing the differences in
tables. I am using datediff("d",[date plans received],[date plan review
letter sent]) in both queries. I am getting some squirelly answers. On one
set of dates, it works fine. One will be 6/12/2007 and the other 6/19/2007
and it will return the answer I'm looking for, 7. On another it will will
return 385 days which can't be right. By the way, my data type in the table
where the number is stored is number. All the data types on the dates are
date/time. What am I doing wrong? Thanks so much!!!

 

[Bob Barrows [MVP]]

I have datediff commands in two queries and I am storing the
differences in tables. I am using datediff("d",[date plans
received],[date plan review letter sent]) in both queries. I am
getting some squirelly answers. On one set of dates, it works fine.
One will be 6/12/2007 and the other 6/19/2007 and it will return the
answer I'm looking for, 7. On another it will will return 385 days
which can't be right.

By "another", do you mean another query against the same dates? or
another set of dates? If so, what is the other set dates?

By the way, my data type in the table where the
number is stored is number.

What number? I thought we were talking about dates?
Oh, the field where you are storing the difference ... why store it?

All the data types on the dates are
date/time. What am I doing wrong? Thanks so much!!!

Impossible to say without seeing the relevant data inputs, as well as
the sql to insert the data

 

[Golfinray]

I need to store it for reports. I have two queries both with dates, but not
the same dates. I don't know why it won't work right.

I have datediff commands in two queries and I am storing the
differences in tables. I am using datediff("d",[date plans
received],[date plan review letter sent]) in both queries. I am
getting some squirelly answers. On one set of dates, it works fine.
One will be 6/12/2007 and the other 6/19/2007 and it will return the
answer I'm looking for, 7. On another it will will return 385 days
which can't be right.

By "another", do you mean another query against the same dates? or
another set of dates? If so, what is the other set dates?

By the way, my data type in the table where the
number is stored is number.

What number? I thought we were talking about dates?
Oh, the field where you are storing the difference ... why store it?

All the data types on the dates are
date/time. What am I doing wrong? Thanks so much!!!

Impossible to say without seeing the relevant data inputs, as well as
the sql to insert the data

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Klatuu]

Golfinary,
You do not need to store it for reports. You should never store calculated
values in a table.
Do the calculation on the report.

As to the inconsistencies of the numbers, I would look at the data in the
rows that are giving incorrect answers. If both fields are date data types,
your expression should always return the correct value.
--
Dave Hargis, Microsoft Access MVP

I need to store it for reports. I have two queries both with dates, but not
the same dates. I don't know why it won't work right.

I have datediff commands in two queries and I am storing the
differences in tables. I am using datediff("d",[date plans
received],[date plan review letter sent]) in both queries. I am
getting some squirelly answers. On one set of dates, it works fine.
One will be 6/12/2007 and the other 6/19/2007 and it will return the
answer I'm looking for, 7. On another it will will return 385 days
which can't be right.

By "another", do you mean another query against the same dates? or
another set of dates? If so, what is the other set dates?

By the way, my data type in the table where the
number is stored is number.

What number? I thought we were talking about dates?
Oh, the field where you are storing the difference ... why store it?

All the data types on the dates are
date/time. What am I doing wrong? Thanks so much!!!

Impossible to say without seeing the relevant data inputs, as well as
the sql to insert the data

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

I need to store it for reports.

It is rare that calculated data needs to be stored for reports.
Typically, the calculations are done within the reports, or in the
queries that supply the data to the reports. Unless dealing with
historical calculations, i.e., calculations that must not be allowed to
change even when the data that went into them changes, there is never a
need to store calculated values.

I have two queries both with dates,
but not the same dates. I don't know why it won't work right.

Neither do I: I have seen the date values that result in the correct
answer - I still haven't seen the date values that are causing the
"wrong" answer
This will be the second time I've asked to see an example of this. I
have no hope of helping you without seeing it.

 

[Klatuu]

Bob,
FYI, it is more efficient to do the calculations, if possible, at the report
level. It reduces network traffic. I know this to be a fact because about 8
or 9 years ago, I was on a team where one of our members wrote a very complex
report with tons of calculations all done at the query level. The report took
over an hour to run. I took the report and moved the calculations to the
report level and it ran in under 3 minutes.