Time difference between dates

J

juicegully

Hi,

I've been stuck on this problem the whole day and haven't found anything in
the newsgroups to help (maybe i'm looking in the wrong section?!). Anyway,
here goes:

I've created a form whose default view is "continuous forms" becuase I want
the nice (I can add pictures, etc) look of a form (rather than a datasheet)
but be able to view all records for a patient. On the form are text boxes
whose control sources are Date1 and Date2, respectively. For each record I
want to display the number of years, months, and days between Date2 and Date1
of the first record (ie. Date2 - (Date1 of first record)).

Originally, when my forms were still in Single Form view, in the After
Update event of txtDate2, I would calculate the number of years, months, and
days between the two dates and display them in textboxes (and not labels b/c
at times I would disable the text boxes). Now in Continuous Forms view, in
each txtYrs, txtMos, and txtDays is the correct number for the record the
focus is on. E.g. If I'm looking at all of my 5 records, all the txtYrs show
the same number, all the txtMos show the same number, and all the txtDays
show the same number. I really hope this makes sense!! What I want is the
txtYrs,Mos,Days in the first record to display the time difference for the
first record, the txtYrs,Mos,Days in the second record to display the time
difference for the second record , and so on.

It was suggested to me to "Calculate this in the query...then it will show
for all records" but I don't know how to calculate the difference in days in
the query! I was thinking of just creating a new field containing this
difference (to be calculate after txtDate2 has a value). Even if I do have a
new field called "Diff" in my table, I don't know how to display the years,
months, and days for each record unless I create 3 more fields in my table -
Yrs, Mos, and Days. I don't understand how to "calculate this in the query".

I'm not sure if this is the correct discussion group for this question, but
if anyone has any ideas they can share, I'd be VERY grateful.

Thank you!
 
O

Ofer

The problem is, that all the fields are unbounded, in that case when you
assign a value to one field, all the fields in the other records get the same
value.

The formula you using to calculate the time difference put it in the
ControlSource Property of the field
= formula
 
J

juicegully

I've just figured out some things about queries which seem to be helping a lot.
The problem now is.. I like the "=formula" idea... but the formula seems to
complicated to be the control source!

Here is my original code to calculate txtYears, Months, and Days:
(BaseDate is just a variable that is used to calculate the number of
yrs,mos, and days. It is initialized with the value in Date1 of the first
record)
(From my first post, Date2 = txtNewDate, Date1 of first record=BaseDate)

' Displays number of years
If DateDiff("yyyy", BaseDate, txtNewDate) >= 1 Then
intYears = DateDiff("yyyy", BaseDate, txtNewDate)
If DateAdd("yyyy", intYears, BaseDate) > txtNewDate Then intYears =
intYears - 1
BaseDate = DateAdd("yyyy", intYears, BaseDate)
If intYears > 0 Then txtYears = intYears
End If

' Displays number of months
If DateDiff("m", BaseDate, txtNewDate) >= 1 Then
intMonths = DateDiff("m", BaseDate, txtNewDate)
If DateAdd("m", intMonths, BaseDate) > txtNewDate Then intMonths =
intMonths -1
BaseDate = DateAdd("m", intMonths, BaseDate)
If intMonths > 0 Then txtMonths = intMonths
End If

' Displays number of days
If DateDiff("d", BaseDate, txtNewDate) >= 1 Then txtDays = DateDiff("d",
BaseDate, txtNewDate)
================================================
Because the number of days depend on BaseDate from the calculation of the
number of months and the number of months depend on BaseDate from the
calculation of the number of years, I don't know how I'm supposed to do
"=formula"

Any ideas please?

Thanks
 
O

Ofer

It doesn't have to be the formula, it can be a function that you pass
parameters to it, and return a value with it

=FunctionName([BaseDate],[txtNewDate])

Function FunctionName(BaseDate,txtNewDate)
Your code


FunctionName = the time difference
End Function
 
J

juicegully

Hi,

I tried what you said but sometimes the user doesn't put anything into
txtNewDate so my function doesn't work properly and in the textbox i see
"#Error". How can I have a blank txtYears,Months, and Days if txtNewDate is
blank? Is there anything I can put into the query/function so that the
function only runs if txtNewDate contains a date?

My function header (not sure if this is the correct word) is:
Public Function YrsMosDays(intUnit As Integer, BaseDate As Date, NewDate As
Date) As Integer

Thank you!


Ofer said:
It doesn't have to be the formula, it can be a function that you pass
parameters to it, and return a value with it

=FunctionName([BaseDate],[txtNewDate])

Function FunctionName(BaseDate,txtNewDate)
Your code


FunctionName = the time difference
End Function


--
In God We Trust - Everything Else We Test


juicegully said:
I've just figured out some things about queries which seem to be helping a lot.
The problem now is.. I like the "=formula" idea... but the formula seems to
complicated to be the control source!

Here is my original code to calculate txtYears, Months, and Days:
(BaseDate is just a variable that is used to calculate the number of
yrs,mos, and days. It is initialized with the value in Date1 of the first
record)
(From my first post, Date2 = txtNewDate, Date1 of first record=BaseDate)

' Displays number of years
If DateDiff("yyyy", BaseDate, txtNewDate) >= 1 Then
intYears = DateDiff("yyyy", BaseDate, txtNewDate)
If DateAdd("yyyy", intYears, BaseDate) > txtNewDate Then intYears =
intYears - 1
BaseDate = DateAdd("yyyy", intYears, BaseDate)
If intYears > 0 Then txtYears = intYears
End If

' Displays number of months
If DateDiff("m", BaseDate, txtNewDate) >= 1 Then
intMonths = DateDiff("m", BaseDate, txtNewDate)
If DateAdd("m", intMonths, BaseDate) > txtNewDate Then intMonths =
intMonths -1
BaseDate = DateAdd("m", intMonths, BaseDate)
If intMonths > 0 Then txtMonths = intMonths
End If

' Displays number of days
If DateDiff("d", BaseDate, txtNewDate) >= 1 Then txtDays = DateDiff("d",
BaseDate, txtNewDate)
================================================
Because the number of days depend on BaseDate from the calculation of the
number of months and the number of months depend on BaseDate from the
calculation of the number of years, I don't know how I'm supposed to do
"=formula"

Any ideas please?

Thanks
Click to expand...
Click to expand...
 
O

Ofer

You can check the paramaters you are passing and return "' if they are null

Function FunctionName(BaseDate,txtNewDate)
If isnull(BaseDate) or isnull(txtNewDate) then
FunctionName=""
exit function
End if

Your code


FunctionName = the time difference
End Function

--
In God We Trust - Everything Else We Test


Ofer said:
It doesn't have to be the formula, it can be a function that you pass
parameters to it, and return a value with it

=FunctionName([BaseDate],[txtNewDate])

Function FunctionName(BaseDate,txtNewDate)
Your code


FunctionName = the time difference
End Function


--
In God We Trust - Everything Else We Test


juicegully said:
I've just figured out some things about queries which seem to be helping a lot.
The problem now is.. I like the "=formula" idea... but the formula seems to
complicated to be the control source!

Here is my original code to calculate txtYears, Months, and Days:
(BaseDate is just a variable that is used to calculate the number of
yrs,mos, and days. It is initialized with the value in Date1 of the first
record)
(From my first post, Date2 = txtNewDate, Date1 of first record=BaseDate)

' Displays number of years
If DateDiff("yyyy", BaseDate, txtNewDate) >= 1 Then
intYears = DateDiff("yyyy", BaseDate, txtNewDate)
If DateAdd("yyyy", intYears, BaseDate) > txtNewDate Then intYears =
intYears - 1
BaseDate = DateAdd("yyyy", intYears, BaseDate)
If intYears > 0 Then txtYears = intYears
End If

' Displays number of months
If DateDiff("m", BaseDate, txtNewDate) >= 1 Then
intMonths = DateDiff("m", BaseDate, txtNewDate)
If DateAdd("m", intMonths, BaseDate) > txtNewDate Then intMonths =
intMonths -1
BaseDate = DateAdd("m", intMonths, BaseDate)
If intMonths > 0 Then txtMonths = intMonths
End If

' Displays number of days
If DateDiff("d", BaseDate, txtNewDate) >= 1 Then txtDays = DateDiff("d",
BaseDate, txtNewDate)
================================================
Because the number of days depend on BaseDate from the calculation of the
number of months and the number of months depend on BaseDate from the
calculation of the number of years, I don't know how I'm supposed to do
"=formula"

Any ideas please?

Thanks
Click to expand...
Click to expand...
 
O

Ofer

One more thing, try and use variant instead of date type
--
In God We Trust - Everything Else We Test


juicegully said:
Hi,

I tried what you said but sometimes the user doesn't put anything into
txtNewDate so my function doesn't work properly and in the textbox i see
"#Error". How can I have a blank txtYears,Months, and Days if txtNewDate is
blank? Is there anything I can put into the query/function so that the
function only runs if txtNewDate contains a date?

My function header (not sure if this is the correct word) is:
Public Function YrsMosDays(intUnit As Integer, BaseDate As Date, NewDate As
Date) As Integer

Thank you!


Ofer said:
It doesn't have to be the formula, it can be a function that you pass
parameters to it, and return a value with it

=FunctionName([BaseDate],[txtNewDate])

Function FunctionName(BaseDate,txtNewDate)
Your code


FunctionName = the time difference
End Function


--
In God We Trust - Everything Else We Test


juicegully said:
I've just figured out some things about queries which seem to be helping a lot.
The problem now is.. I like the "=formula" idea... but the formula seems to
complicated to be the control source!

Here is my original code to calculate txtYears, Months, and Days:
(BaseDate is just a variable that is used to calculate the number of
yrs,mos, and days. It is initialized with the value in Date1 of the first
record)
(From my first post, Date2 = txtNewDate, Date1 of first record=BaseDate)

' Displays number of years
If DateDiff("yyyy", BaseDate, txtNewDate) >= 1 Then
intYears = DateDiff("yyyy", BaseDate, txtNewDate)
If DateAdd("yyyy", intYears, BaseDate) > txtNewDate Then intYears =
intYears - 1
BaseDate = DateAdd("yyyy", intYears, BaseDate)
If intYears > 0 Then txtYears = intYears
End If

' Displays number of months
If DateDiff("m", BaseDate, txtNewDate) >= 1 Then
intMonths = DateDiff("m", BaseDate, txtNewDate)
If DateAdd("m", intMonths, BaseDate) > txtNewDate Then intMonths =
intMonths -1
BaseDate = DateAdd("m", intMonths, BaseDate)
If intMonths > 0 Then txtMonths = intMonths
End If

' Displays number of days
If DateDiff("d", BaseDate, txtNewDate) >= 1 Then txtDays = DateDiff("d",
BaseDate, txtNewDate)
================================================
Because the number of days depend on BaseDate from the calculation of the
number of months and the number of months depend on BaseDate from the
calculation of the number of years, I don't know how I'm supposed to do
"=formula"

Any ideas please?

Thanks

:

The problem is, that all the fields are unbounded, in that case when you
assign a value to one field, all the fields in the other records get the same
value.

The formula you using to calculate the time difference put it in the
ControlSource Property of the field
= formula


--
In God We Trust - Everything Else We Test


:

Hi,

I've been stuck on this problem the whole day and haven't found anything in
the newsgroups to help (maybe i'm looking in the wrong section?!). Anyway,
here goes:

I've created a form whose default view is "continuous forms" becuase I want
the nice (I can add pictures, etc) look of a form (rather than a datasheet)
but be able to view all records for a patient. On the form are text boxes
whose control sources are Date1 and Date2, respectively. For each record I
want to display the number of years, months, and days between Date2 and Date1
of the first record (ie. Date2 - (Date1 of first record)).

Originally, when my forms were still in Single Form view, in the After
Update event of txtDate2, I would calculate the number of years, months, and
days between the two dates and display them in textboxes (and not labels b/c
at times I would disable the text boxes). Now in Continuous Forms view, in
each txtYrs, txtMos, and txtDays is the correct number for the record the
focus is on. E.g. If I'm looking at all of my 5 records, all the txtYrs show
the same number, all the txtMos show the same number, and all the txtDays
show the same number. I really hope this makes sense!! What I want is the
txtYrs,Mos,Days in the first record to display the time difference for the
first record, the txtYrs,Mos,Days in the second record to display the time
difference for the second record , and so on.

It was suggested to me to "Calculate this in the query...then it will show
for all records" but I don't know how to calculate the difference in days in
the query! I was thinking of just creating a new field containing this
difference (to be calculate after txtDate2 has a value). Even if I do have a
new field called "Diff" in my table, I don't know how to display the years,
months, and days for each record unless I create 3 more fields in my table -
Yrs, Mos, and Days. I don't understand how to "calculate this in the query".

I'm not sure if this is the correct discussion group for this question, but
if anyone has any ideas they can share, I'd be VERY grateful.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
J

juicegully

Hi Ofer,

I tried doing what you said - changing the data type to variant and then
checking for null parameters:

Public Function YrsMosDays(BaseDate As Date, txtNewDate As Date)

and

If IsNull(BaseDate) Or IsNull(txtNewDate) Then
YrsMosDays = ""
Exit Function
End If

but i'm still getting the error message ("# ERROR"). Also, when i do have a
blank date it doesn't seem like the value is null! it can't be empty b/c date
data types can't be empty strings ("") right? any other ideas?

thanks


Ofer said:
You can check the paramaters you are passing and return "' if they are null

Function FunctionName(BaseDate,txtNewDate)
If isnull(BaseDate) or isnull(txtNewDate) then
FunctionName=""
exit function
End if

Your code


FunctionName = the time difference
End Function

--
In God We Trust - Everything Else We Test


Ofer said:
It doesn't have to be the formula, it can be a function that you pass
parameters to it, and return a value with it

=FunctionName([BaseDate],[txtNewDate])

Function FunctionName(BaseDate,txtNewDate)
Your code


FunctionName = the time difference
End Function


--
In God We Trust - Everything Else We Test


juicegully said:
I've just figured out some things about queries which seem to be helping a lot.
The problem now is.. I like the "=formula" idea... but the formula seems to
complicated to be the control source!

Here is my original code to calculate txtYears, Months, and Days:
(BaseDate is just a variable that is used to calculate the number of
yrs,mos, and days. It is initialized with the value in Date1 of the first
record)
(From my first post, Date2 = txtNewDate, Date1 of first record=BaseDate)

' Displays number of years
If DateDiff("yyyy", BaseDate, txtNewDate) >= 1 Then
intYears = DateDiff("yyyy", BaseDate, txtNewDate)
If DateAdd("yyyy", intYears, BaseDate) > txtNewDate Then intYears =
intYears - 1
BaseDate = DateAdd("yyyy", intYears, BaseDate)
If intYears > 0 Then txtYears = intYears
End If

' Displays number of months
If DateDiff("m", BaseDate, txtNewDate) >= 1 Then
intMonths = DateDiff("m", BaseDate, txtNewDate)
If DateAdd("m", intMonths, BaseDate) > txtNewDate Then intMonths =
intMonths -1
BaseDate = DateAdd("m", intMonths, BaseDate)
If intMonths > 0 Then txtMonths = intMonths
End If

' Displays number of days
If DateDiff("d", BaseDate, txtNewDate) >= 1 Then txtDays = DateDiff("d",
BaseDate, txtNewDate)
================================================
Because the number of days depend on BaseDate from the calculation of the
number of months and the number of months depend on BaseDate from the
calculation of the number of years, I don't know how I'm supposed to do
"=formula"

Any ideas please?

Thanks

:

The problem is, that all the fields are unbounded, in that case when you
assign a value to one field, all the fields in the other records get the same
value.

The formula you using to calculate the time difference put it in the
ControlSource Property of the field
= formula


--
In God We Trust - Everything Else We Test


:

Hi,

I've been stuck on this problem the whole day and haven't found anything in
the newsgroups to help (maybe i'm looking in the wrong section?!). Anyway,
here goes:

I've created a form whose default view is "continuous forms" becuase I want
the nice (I can add pictures, etc) look of a form (rather than a datasheet)
but be able to view all records for a patient. On the form are text boxes
whose control sources are Date1 and Date2, respectively. For each record I
want to display the number of years, months, and days between Date2 and Date1
of the first record (ie. Date2 - (Date1 of first record)).

Originally, when my forms were still in Single Form view, in the After
Update event of txtDate2, I would calculate the number of years, months, and
days between the two dates and display them in textboxes (and not labels b/c
at times I would disable the text boxes). Now in Continuous Forms view, in
each txtYrs, txtMos, and txtDays is the correct number for the record the
focus is on. E.g. If I'm looking at all of my 5 records, all the txtYrs show
the same number, all the txtMos show the same number, and all the txtDays
show the same number. I really hope this makes sense!! What I want is the
txtYrs,Mos,Days in the first record to display the time difference for the
first record, the txtYrs,Mos,Days in the second record to display the time
difference for the second record , and so on.

It was suggested to me to "Calculate this in the query...then it will show
for all records" but I don't know how to calculate the difference in days in
the query! I was thinking of just creating a new field containing this
difference (to be calculate after txtDate2 has a value). Even if I do have a
new field called "Diff" in my table, I don't know how to display the years,
months, and days for each record unless I create 3 more fields in my table -
Yrs, Mos, and Days. I don't understand how to "calculate this in the query".

I'm not sure if this is the correct discussion group for this question, but
if anyone has any ideas they can share, I'd be VERY grateful.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
J

juicegully

Hi Ofer

I just read your post again and did exactly what you said:

Public Function YrsMosDays(BaseDate, NewDate)

and

If Not IsDate(BaseDate) Or Not IsDate(PSADate) Then
YrsMosDays = ""
Exit Function
End If

And I don't have to worry about having the function run when data types
other than date are entered into the textboxes b/c the format for the text
boxes is Medium date so Access will check it before running the function!

Thanks for all your help!
Ofer said:
You can check the paramaters you are passing and return "' if they are null

Function FunctionName(BaseDate,txtNewDate)
If isnull(BaseDate) or isnull(txtNewDate) then
FunctionName=""
exit function
End if

Your code


FunctionName = the time difference
End Function

--
In God We Trust - Everything Else We Test


Ofer said:
It doesn't have to be the formula, it can be a function that you pass
parameters to it, and return a value with it

=FunctionName([BaseDate],[txtNewDate])

Function FunctionName(BaseDate,txtNewDate)
Your code


FunctionName = the time difference
End Function


--
In God We Trust - Everything Else We Test


juicegully said:
I've just figured out some things about queries which seem to be helping a lot.
The problem now is.. I like the "=formula" idea... but the formula seems to
complicated to be the control source!

Here is my original code to calculate txtYears, Months, and Days:
(BaseDate is just a variable that is used to calculate the number of
yrs,mos, and days. It is initialized with the value in Date1 of the first
record)
(From my first post, Date2 = txtNewDate, Date1 of first record=BaseDate)

' Displays number of years
If DateDiff("yyyy", BaseDate, txtNewDate) >= 1 Then
intYears = DateDiff("yyyy", BaseDate, txtNewDate)
If DateAdd("yyyy", intYears, BaseDate) > txtNewDate Then intYears =
intYears - 1
BaseDate = DateAdd("yyyy", intYears, BaseDate)
If intYears > 0 Then txtYears = intYears
End If

' Displays number of months
If DateDiff("m", BaseDate, txtNewDate) >= 1 Then
intMonths = DateDiff("m", BaseDate, txtNewDate)
If DateAdd("m", intMonths, BaseDate) > txtNewDate Then intMonths =
intMonths -1
BaseDate = DateAdd("m", intMonths, BaseDate)
If intMonths > 0 Then txtMonths = intMonths
End If

' Displays number of days
If DateDiff("d", BaseDate, txtNewDate) >= 1 Then txtDays = DateDiff("d",
BaseDate, txtNewDate)
================================================
Because the number of days depend on BaseDate from the calculation of the
number of months and the number of months depend on BaseDate from the
calculation of the number of years, I don't know how I'm supposed to do
"=formula"

Any ideas please?

Thanks

:

The problem is, that all the fields are unbounded, in that case when you
assign a value to one field, all the fields in the other records get the same
value.

The formula you using to calculate the time difference put it in the
ControlSource Property of the field
= formula


--
In God We Trust - Everything Else We Test


:

Hi,

I've been stuck on this problem the whole day and haven't found anything in
the newsgroups to help (maybe i'm looking in the wrong section?!). Anyway,
here goes:

I've created a form whose default view is "continuous forms" becuase I want
the nice (I can add pictures, etc) look of a form (rather than a datasheet)
but be able to view all records for a patient. On the form are text boxes
whose control sources are Date1 and Date2, respectively. For each record I
want to display the number of years, months, and days between Date2 and Date1
of the first record (ie. Date2 - (Date1 of first record)).

Originally, when my forms were still in Single Form view, in the After
Update event of txtDate2, I would calculate the number of years, months, and
days between the two dates and display them in textboxes (and not labels b/c
at times I would disable the text boxes). Now in Continuous Forms view, in
each txtYrs, txtMos, and txtDays is the correct number for the record the
focus is on. E.g. If I'm looking at all of my 5 records, all the txtYrs show
the same number, all the txtMos show the same number, and all the txtDays
show the same number. I really hope this makes sense!! What I want is the
txtYrs,Mos,Days in the first record to display the time difference for the
first record, the txtYrs,Mos,Days in the second record to display the time
difference for the second record , and so on.

It was suggested to me to "Calculate this in the query...then it will show
for all records" but I don't know how to calculate the difference in days in
the query! I was thinking of just creating a new field containing this
difference (to be calculate after txtDate2 has a value). Even if I do have a
new field called "Diff" in my table, I don't know how to display the years,
months, and days for each record unless I create 3 more fields in my table -
Yrs, Mos, and Days. I don't understand how to "calculate this in the query".

I'm not sure if this is the correct discussion group for this question, but
if anyone has any ideas they can share, I'd be VERY grateful.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Top