Date Calculation problem

[pmundle]

Hi everyone!!
I am having a date calculation problem.My form has two date fields
DATE1 & DATE 2.The dates have a date and time format(ex: 10/10/2005
12:04).I have a calculated field in my form which calculates the
difference of the two dates [DATE2]-[DATE1].
My problem is whenever the DATE 2 FIELD is Null or whenever it has
the current date in it,I want the difference to be [today's date 00:00
hrs]-[DATE1].
I am having no code knowledge. Thanks in advance.

 

[Mike]

Something like this I think:
=IIf(IsNull([Date2])=True Or [Date2]=Date(); Date()-[Date1];[Date2]-[Date1])

 

[pmundle]

Thanks Mike.But I still have a problem.May be I was not clear in
defining my problem.
1.The calculation is perfect when the DATE 2 field is Null.
2.When DATE 2 has today's date and "any time" with it, the result is
DATE2-DATE1.

DATE2 DATE1 DIFF
NULL 10/10/05 12:00 0.5
10/11/05 10/10/05 12:00 0.5
10/11/05 12:00 10/10/05 12:00 1.0

I want the last DIFF value to be 0.5 too.
Thanks once again.

 

[Mike]

Try this:
IIf(IsNull([Date2])=ИÑтина Or
DateSerial(Year([Date2]);Month([Date2]);Day([Date2]))=Date();Date()-[Date1];[Date2]-[Date1])

 

[Mike]

Try this:
IIf(IsNull([Date2])=ИÑтина Or
DateSerial(Year([Date2]);Month([Date2]);Day([Date2]))=Date();Date()-[Date1];[Date2]-[Date1])

 

[Mike]

Sorry for non-English text in previous message

Try this:
IIf(IsNull([Date2])=True Or
DateSerial(Year([Date2]);Month([Date2]);Day([Date2]))=Date();Date()-[Date1];[Date2]-[Date1])

 

[pmundle]

I tried this expression but the result is same as the previous one.That
is , I still get 1.0 instead of 0.5

Sorry for non-English text in previous message

Try this:
IIf(IsNull([Date2])=True Or
DateSerial(Year([Date2]);Month([Date2]);Day([Date2]))=Date();Date()-[Date1];[Date2]-[Date1])

Thanks Mike.But I still have a problem.May be I was not clear in
defining my problem.
1.The calculation is perfect when the DATE 2 field is Null.
2.When DATE 2 has today's date and "any time" with it, the result is
DATE2-DATE1.

DATE2 DATE1 DIFF
NULL 10/10/05 12:00 0.5
10/11/05 10/10/05 12:00 0.5
10/11/05 12:00 10/10/05 12:00 1.0

I want the last DIFF value to be 0.5 too.
Thanks once again.

 

[Mike]

I tested it and got 0.5
Just open a new query, change view to sql-view and paste:

SELECT #10/11/2005 12:00:00# AS Date2, #10/10/2005 12:00:00# AS Date1,
IIf(IsNull([Date2])=True Or
DateSerial(Year([Date2]),Month([Date2]),Day([Date2]))=Date(),Date()-[Date1],[Date2]-[Date1]) AS Result;

Save the query and run it. You'll get 0.5 as the result.

I tried this expression but the result is same as the previous one.That
is , I still get 1.0 instead of 0.5

Sorry for non-English text in previous message

Try this:
IIf(IsNull([Date2])=True Or
DateSerial(Year([Date2]);Month([Date2]);Day([Date2]))=Date();Date()-[Date1];[Date2]-[Date1])

Thanks Mike.But I still have a problem.May be I was not clear in
defining my problem.
1.The calculation is perfect when the DATE 2 field is Null.
2.When DATE 2 has today's date and "any time" with it, the result is
DATE2-DATE1.

DATE2 DATE1 DIFF
NULL 10/10/05 12:00 0.5
10/11/05 10/10/05 12:00 0.5
10/11/05 12:00 10/10/05 12:00 1.0

I want the last DIFF value to be 0.5 too.
Thanks once again.

 

[John Vinson]

Hi everyone!!
I am having a date calculation problem.My form has two date fields
DATE1 & DATE 2.The dates have a date and time format(ex: 10/10/2005
12:04).I have a calculated field in my form which calculates the
difference of the two dates [DATE2]-[DATE1].
My problem is whenever the DATE 2 FIELD is Null or whenever it has
the current date in it,I want the difference to be [today's date 00:00
hrs]-[DATE1].
I am having no code knowledge. Thanks in advance.

Just to add to Mike's suggestions - the DateDiff() function is good
for calculating date differences. A Date/Time value (regardless of
format) is stored as a Double Float count of days and fractions of a
day (times); subtracting two dates actually gives you a date sometime
around December 30, 1899 (the start point for the double numbers).

If your time differences will never be more than 24 hours, you can use

NZ([Date2], Date()) - [Date1]

The NZ function will return its second argument - today's date at
midnight - if Date2 is NULL.

If your times will exceed 24 hours, you may get confusing results:
i.e. a 30 hour difference will display as #12/31/1899 06:00:00#.
DateDiff will give you the elapsed time in any time increment you
choose, from seconds to years - might that be more useful?

John W. Vinson[MVP]

 

[pmundle]

I tried your suggestion on a query but I get error messages like
"malformed guid" when I try to save it.
On a form,the expression does not save in the control source
property.Instead,the dateserial gets deleted and the second condition
becomes [Date2]=date().

Hi everyone!!
I am having a date calculation problem.My form has two date fields
DATE1 & DATE 2.The dates have a date and time format(ex: 10/10/2005
12:04).I have a calculated field in my form which calculates the
difference of the two dates [DATE2]-[DATE1].
My problem is whenever the DATE 2 FIELD is Null or whenever it has
the current date in it,I want the difference to be [today's date 00:00
hrs]-[DATE1].
I am having no code knowledge. Thanks in advance.

Just to add to Mike's suggestions - the DateDiff() function is good
for calculating date differences. A Date/Time value (regardless of
format) is stored as a Double Float count of days and fractions of a
day (times); subtracting two dates actually gives you a date sometime
around December 30, 1899 (the start point for the double numbers).

If your time differences will never be more than 24 hours, you can use

NZ([Date2], Date()) - [Date1]

The NZ function will return its second argument - today's date at
midnight - if Date2 is NULL.

If your times will exceed 24 hours, you may get confusing results:
i.e. a 30 hour difference will display as #12/31/1899 06:00:00#.
DateDiff will give you the elapsed time in any time increment you
choose, from seconds to years - might that be more useful?

John W. Vinson[MVP]

 

[John Vinson]

I tried your suggestion on a query but I get error messages like
"malformed guid" when I try to save it.

I have NO idea whatsoever what might have caused this... but certainly
not directly applying my suggestion to any query resembling what you
described. Something else is going on.

Could you post the SQL of your current query, and indicate where you
need the calculation done?

John W. Vinson[MVP]

 

[pmundle]

It was a typing mistake.Sorry.It is working now.Thanks.