calculating time periods

[BurtArkin]

Using a continuous form, I have a date field which indicates an action. The
next time an action occurs, a new date is entered (on a new line in the
form). How would I calculate the number of days between the two actions?
I'd appreciate any help. Thanks

 

[Ken Snell \(MVP\)]

You'll need to give us more details about the query that is the form's
RecordSource -- SQL statement would be good as a starting point.

 

[BurtArkin]

The form's source is
SELECT ChronTransfusion.ChronTransfusionID, ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason, ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod, ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)], ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last transfusion
date and the one immediately prior.

Thanks for your Rapid Response.

 

[Ken Snell \(MVP\)]

Add a textbox to the form's Detail section; name it txtDiff. Use this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion", "ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"), [ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID, ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason, ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt

You'll need to give us more details about the query that is the form's
RecordSource -- SQL statement would be good as a starting point.

 

[BurtArkin]

Thanks again for your quick reply. I tried your suggestion. It only works
some of the time. Below are 3 columns, date, my number calculated with a
series of convoluted queries, and the txtdiff field. It's ok as long as it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt

Add a textbox to the form's Detail section; name it txtDiff. Use this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion", "ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"), [ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID, ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason, ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt

You'll need to give us more details about the query that is the form's
RecordSource -- SQL statement would be good as a starting point.

--

Ken Snell
<MS ACCESS MVP>

Using a continuous form, I have a date field which indicates an action.
The
next time an action occurs, a new date is entered (on a new line in the
form). How would I calculate the number of days between the two
actions?
I'd appreciate any help. Thanks

 

[Ken Snell \(MVP\)]

What is the data type of the "ChronTransDate" field? If it's a DateTime
type, the DateDiff will work. If it's something else, then it will fail at
times. You should be using a DateTime data type field in the table.

--

Ken Snell
<MS ACCESS MVP>

Thanks again for your quick reply. I tried your suggestion. It only
works
some of the time. Below are 3 columns, date, my number calculated with a
series of convoluted queries, and the txtdiff field. It's ok as long as
it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt

Add a textbox to the form's Detail section; name it txtDiff. Use this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion",
"ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"), [ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID, ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason, ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt


:

You'll need to give us more details about the query that is the form's
RecordSource -- SQL statement would be good as a starting point.

--

Ken Snell
<MS ACCESS MVP>

Using a continuous form, I have a date field which indicates an
action.
The
next time an action occurs, a new date is entered (on a new line in
the
form). How would I calculate the number of days between the two
actions?
I'd appreciate any help. Thanks

 

[BurtArkin]

It is a DateTime field. Here is the code you gave me for the calculation:
=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#" &
Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),[ChronTransDate])

When the first date =3/3/7, calculated field is naturally blank.
When the next date =3/13/7, the calc field shows 6, not 10
When the next date = 4/14/7, the calc field shows 2, not 32
When the next date = 4/16/7, the calc field shows 2
When the next date = 4/26/7, the calc field shows 2, not 10
When the next date = 7/25/7, the calc field shows 18, not 90

I'm as puzzled as you are. Thanks again.
--
Burt

What is the data type of the "ChronTransDate" field? If it's a DateTime
type, the DateDiff will work. If it's something else, then it will fail at
times. You should be using a DateTime data type field in the table.

--

Ken Snell
<MS ACCESS MVP>

Thanks again for your quick reply. I tried your suggestion. It only
works
some of the time. Below are 3 columns, date, my number calculated with a
series of convoluted queries, and the txtdiff field. It's ok as long as
it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt

Add a textbox to the form's Detail section; name it txtDiff. Use this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion",
"ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"), [ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID, ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason, ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt


:

You'll need to give us more details about the query that is the form's
RecordSource -- SQL statement would be good as a starting point.

--

Ken Snell
<MS ACCESS MVP>

Using a continuous form, I have a date field which indicates an
action.
The
next time an action occurs, a new date is entered (on a new line in
the
form). How would I calculate the number of days between the two
actions?
I'd appreciate any help. Thanks

 

[Ken Snell \(MVP\)]

I think your date format is confusing ACCESS when using the expression that
I suggested. Try this expression as the Control Source:

=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<" &
[ChronTransDate]),[ChronTransDate])


If that doesn't work either, then let's add an extra textbox to your form's
Detail section, and make its Control Source this expression:
=DMax("ChronTransDate","ChronTransfusion","ChronTransDate<" &
[ChronTransDate])

This will let us see what ACCESS is returning for the "previous date value".
--

Ken Snell
<MS ACCESS MVP>

It is a DateTime field. Here is the code you gave me for the calculation:
=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
&
Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),[ChronTransDate])

When the first date =3/3/7, calculated field is naturally blank.
When the next date =3/13/7, the calc field shows 6, not 10
When the next date = 4/14/7, the calc field shows 2, not 32
When the next date = 4/16/7, the calc field shows 2
When the next date = 4/26/7, the calc field shows 2, not 10
When the next date = 7/25/7, the calc field shows 18, not 90

I'm as puzzled as you are. Thanks again.
--
Burt

What is the data type of the "ChronTransDate" field? If it's a DateTime
type, the DateDiff will work. If it's something else, then it will fail
at
times. You should be using a DateTime data type field in the table.

--

Ken Snell
<MS ACCESS MVP>

Thanks again for your quick reply. I tried your suggestion. It only
works
some of the time. Below are 3 columns, date, my number calculated with
a
series of convoluted queries, and the txtdiff field. It's ok as long
as
it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt


:

Add a textbox to the form's Detail section; name it txtDiff. Use this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion",
"ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"), [ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID,
ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason, ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt


:

You'll need to give us more details about the query that is the
form's
RecordSource -- SQL statement would be good as a starting point.

--

Ken Snell
<MS ACCESS MVP>

Using a continuous form, I have a date field which indicates an
action.
The
next time an action occurs, a new date is entered (on a new line
in
the
form). How would I calculate the number of days between the two
actions?
I'd appreciate any help. Thanks

 

[BurtArkin]

I really appreciate the trouble you have gone through. The last two
suggestions do not work either, so I guess I'll just go with the "convoluted"
queries to do the calculations, if that's OK with you. If you would like to
experiment further, I'd be happy to send you the database for you to play
with. It's up to you.

Thanks again.
--
Burt

I think your date format is confusing ACCESS when using the expression that
I suggested. Try this expression as the Control Source:

=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<" &
[ChronTransDate]),[ChronTransDate])


If that doesn't work either, then let's add an extra textbox to your form's
Detail section, and make its Control Source this expression:
=DMax("ChronTransDate","ChronTransfusion","ChronTransDate<" &
[ChronTransDate])

This will let us see what ACCESS is returning for the "previous date value".
--

Ken Snell
<MS ACCESS MVP>

It is a DateTime field. Here is the code you gave me for the calculation:
=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
&
Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),[ChronTransDate])

When the first date =3/3/7, calculated field is naturally blank.
When the next date =3/13/7, the calc field shows 6, not 10
When the next date = 4/14/7, the calc field shows 2, not 32
When the next date = 4/16/7, the calc field shows 2
When the next date = 4/26/7, the calc field shows 2, not 10
When the next date = 7/25/7, the calc field shows 18, not 90

I'm as puzzled as you are. Thanks again.
--
Burt

What is the data type of the "ChronTransDate" field? If it's a DateTime
type, the DateDiff will work. If it's something else, then it will fail
at
times. You should be using a DateTime data type field in the table.

--

Ken Snell
<MS ACCESS MVP>


Thanks again for your quick reply. I tried your suggestion. It only
works
some of the time. Below are 3 columns, date, my number calculated with
a
series of convoluted queries, and the txtdiff field. It's ok as long
as
it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt


:

Add a textbox to the form's Detail section; name it txtDiff. Use this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion",
"ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"), [ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID,
ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason, ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt


:

You'll need to give us more details about the query that is the
form's
RecordSource -- SQL statement would be good as a starting point.

--

Ken Snell
<MS ACCESS MVP>

Using a continuous form, I have a date field which indicates an
action.
The
next time an action occurs, a new date is entered (on a new line
in
the
form). How would I calculate the number of days between the two
actions?
I'd appreciate any help. Thanks

 

[Ken Snell \(MVP\)]

You can find an email address for me at this website:
www.cadellsoftware.org

Send me the file with explanation of the form on which you're wanting to use
the expression. I'm thinking that there is still a data type / format
problem.

--

Ken Snell
<MS ACCESS MVP>

I really appreciate the trouble you have gone through. The last two
suggestions do not work either, so I guess I'll just go with the
"convoluted"
queries to do the calculations, if that's OK with you. If you would like
to
experiment further, I'd be happy to send you the database for you to play
with. It's up to you.

Thanks again.
--
Burt

I think your date format is confusing ACCESS when using the expression
that
I suggested. Try this expression as the Control Source:

=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<"
&
[ChronTransDate]),[ChronTransDate])


If that doesn't work either, then let's add an extra textbox to your
form's
Detail section, and make its Control Source this expression:
=DMax("ChronTransDate","ChronTransfusion","ChronTransDate<" &
[ChronTransDate])

This will let us see what ACCESS is returning for the "previous date
value".
--

Ken Snell
<MS ACCESS MVP>

It is a DateTime field. Here is the code you gave me for the
calculation:
=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
&
Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),[ChronTransDate])

When the first date =3/3/7, calculated field is naturally blank.
When the next date =3/13/7, the calc field shows 6, not 10
When the next date = 4/14/7, the calc field shows 2, not 32
When the next date = 4/16/7, the calc field shows 2
When the next date = 4/26/7, the calc field shows 2, not 10
When the next date = 7/25/7, the calc field shows 18, not 90

I'm as puzzled as you are. Thanks again.
--
Burt


:

What is the data type of the "ChronTransDate" field? If it's a
DateTime
type, the DateDiff will work. If it's something else, then it will
fail
at
times. You should be using a DateTime data type field in the table.

--

Ken Snell
<MS ACCESS MVP>


Thanks again for your quick reply. I tried your suggestion. It
only
works
some of the time. Below are 3 columns, date, my number calculated
with
a
series of convoluted queries, and the txtdiff field. It's ok as
long
as
it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt


:

Add a textbox to the form's Detail section; name it txtDiff. Use
this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion",
"ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"), [ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID,
ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason,
ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt


:

You'll need to give us more details about the query that is the
form's
RecordSource -- SQL statement would be good as a starting point.

--

Ken Snell
<MS ACCESS MVP>

message
Using a continuous form, I have a date field which indicates
an
action.
The
next time an action occurs, a new date is entered (on a new
line
in
the
form). How would I calculate the number of days between the
two
actions?
I'd appreciate any help. Thanks

 

[Ken Snell \(MVP\)]

I've looked at the database that you emailed to me. I wasn't understanding
the context in which you were using the continuous forms view of the form. I
was understanding that it was a standalone form -- but you're using it as a
subform. Therefore, there is a linking field between the main form and the
subform that also must be included as a criterion in the DMax function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff textbox to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

--

Ken Snell
<MS ACCESS MVP>

You can find an email address for me at this website:
www.cadellsoftware.org

Send me the file with explanation of the form on which you're wanting to
use the expression. I'm thinking that there is still a data type / format
problem.

--

Ken Snell
<MS ACCESS MVP>

I really appreciate the trouble you have gone through. The last two
suggestions do not work either, so I guess I'll just go with the
"convoluted"
queries to do the calculations, if that's OK with you. If you would like
to
experiment further, I'd be happy to send you the database for you to play
with. It's up to you.

Thanks again.
--
Burt

I think your date format is confusing ACCESS when using the expression
that
I suggested. Try this expression as the Control Source:

=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<"
&
[ChronTransDate]),[ChronTransDate])


If that doesn't work either, then let's add an extra textbox to your
form's
Detail section, and make its Control Source this expression:
=DMax("ChronTransDate","ChronTransfusion","ChronTransDate<" &
[ChronTransDate])

This will let us see what ACCESS is returning for the "previous date
value".
--

Ken Snell
<MS ACCESS MVP>




It is a DateTime field. Here is the code you gave me for the
calculation:
=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
&
Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),[ChronTransDate])

When the first date =3/3/7, calculated field is naturally blank.
When the next date =3/13/7, the calc field shows 6, not 10
When the next date = 4/14/7, the calc field shows 2, not 32
When the next date = 4/16/7, the calc field shows 2
When the next date = 4/26/7, the calc field shows 2, not 10
When the next date = 7/25/7, the calc field shows 18, not 90

I'm as puzzled as you are. Thanks again.
--
Burt


:

What is the data type of the "ChronTransDate" field? If it's a
DateTime
type, the DateDiff will work. If it's something else, then it will
fail
at
times. You should be using a DateTime data type field in the table.

--

Ken Snell
<MS ACCESS MVP>


Thanks again for your quick reply. I tried your suggestion. It
only
works
some of the time. Below are 3 columns, date, my number calculated
with
a
series of convoluted queries, and the txtdiff field. It's ok as
long
as
it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt


:

Add a textbox to the form's Detail section; name it txtDiff. Use
this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion",
"ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),
[ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID,
ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason,
ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt


:

You'll need to give us more details about the query that is the
form's
RecordSource -- SQL statement would be good as a starting
point.

--

Ken Snell
<MS ACCESS MVP>

message
Using a continuous form, I have a date field which indicates
an
action.
The
next time an action occurs, a new date is entered (on a new
line
in
the
form). How would I calculate the number of days between the
two
actions?
I'd appreciate any help. Thanks

 

[duckylegs]

Hi Ken, I saw this post and got excited, I thought I had a solution to a very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table (M_Income),
which is displayed in a continuous subform on a form named (F_Sites). The
link field is Site_ID between the M_Sites and M_Income tables. I've used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#" &
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally given by
BurtArkin.

Are you able to give me any other suggestions as to where I might be going
wrong here? Any help would be greatly appreciated, I feel like I'm so close
here!

I've looked at the database that you emailed to me. I wasn't understanding
the context in which you were using the continuous forms view of the form. I
was understanding that it was a standalone form -- but you're using it as a
subform. Therefore, there is a linking field between the main form and the
subform that also must be included as a criterion in the DMax function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff textbox to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

--

Ken Snell
<MS ACCESS MVP>

You can find an email address for me at this website:
www.cadellsoftware.org

Send me the file with explanation of the form on which you're wanting to
use the expression. I'm thinking that there is still a data type / format
problem.

--

Ken Snell
<MS ACCESS MVP>

I really appreciate the trouble you have gone through. The last two
suggestions do not work either, so I guess I'll just go with the
"convoluted"
queries to do the calculations, if that's OK with you. If you would like
to
experiment further, I'd be happy to send you the database for you to play
with. It's up to you.

Thanks again.
--
Burt


:

I think your date format is confusing ACCESS when using the expression
that
I suggested. Try this expression as the Control Source:

=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<"
&
[ChronTransDate]),[ChronTransDate])


If that doesn't work either, then let's add an extra textbox to your
form's
Detail section, and make its Control Source this expression:
=DMax("ChronTransDate","ChronTransfusion","ChronTransDate<" &
[ChronTransDate])

This will let us see what ACCESS is returning for the "previous date
value".
--

Ken Snell
<MS ACCESS MVP>




It is a DateTime field. Here is the code you gave me for the
calculation:
=DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
&
Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),[ChronTransDate])

When the first date =3/3/7, calculated field is naturally blank.
When the next date =3/13/7, the calc field shows 6, not 10
When the next date = 4/14/7, the calc field shows 2, not 32
When the next date = 4/16/7, the calc field shows 2
When the next date = 4/26/7, the calc field shows 2, not 10
When the next date = 7/25/7, the calc field shows 18, not 90

I'm as puzzled as you are. Thanks again.
--
Burt


:

What is the data type of the "ChronTransDate" field? If it's a
DateTime
type, the DateDiff will work. If it's something else, then it will
fail
at
times. You should be using a DateTime data type field in the table.

--

Ken Snell
<MS ACCESS MVP>


Thanks again for your quick reply. I tried your suggestion. It
only
works
some of the time. Below are 3 columns, date, my number calculated
with
a
series of convoluted queries, and the txtdiff field. It's ok as
long
as
it
doesn't jump a month.

03-Mar-07 0
13-Mar-07 10 10
27-Mar-07 14 14
27-Apr-07 31 13
01-May-07 4 4
05-May-07 4 4
06-Jun-07 32 20

I tried another set with the same anamolous results.
03-Mar-07 0 0
07-Mar-07 4 4
05-Apr-07 29 1
12-Apr-07 7 7
24-Apr-07 12 10
24-May-07 30 7

What do you think is the problem?

--
Burt


:

Add a textbox to the form's Detail section; name it txtDiff. Use
this
expression as its ControlSource:

=DateDiff("d", DMax("ChronTransDate", "ChronTransfusion",
"ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "#"),
[ChronTransDate])

--

Ken Snell
<MS ACCESS MVP>

The form's source is
SELECT ChronTransfusion.ChronTransfusionID,
ChronTransfusion.PatientID,
ChronTransfusion.ChronTransReason,
ChronTransfusion.ChronTransDate,
ChronTransfusion.ChrontransDateNumber,
ChronTransfusion.ChronTransDateFromPeriod,
ChronTransfusion.PatientWeightLB,
ChronTransfusion.PatientWeightKilo,
ChronTransfusion.[ChronTransAmount(ml/kg)],
ChronTransfusion.ChronTransHbPreTx
FROM ChronTransfusion;

What I need to calculate is the period of days between the last
transfusion
date and the one immediately prior.

Thanks for your Rapid Response.
--
Burt


:

You'll need to give us more details about the query that is the
form's
RecordSource -- SQL statement would be good as a starting
point.

--

Ken Snell
<MS ACCESS MVP>

message
Using a continuous form, I have a date field which indicates
an
action.
The
next time an action occurs, a new date is entered (on a new
line
in
the
form). How would I calculate the number of days between the
two
actions?
I'd appreciate any help. Thanks

 

[Ken Snell \(MVP\)]

In order for us to assist, we'll need more details about the two tables --
what are the fields, what are the data types of each field, etc. for the two
tables. Also, provide us with the SQL statements of the main form's
RecordSource query and the subform's RecordSource query.

If you're seeing erratic results, it's likely because you need at least one
more filtering field in the DMax function, but can't suggest what it might
be until we know more about the tables and forms.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken, I saw this post and got excited, I thought I had a solution to a
very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table (M_Income),
which is displayed in a continuous subform on a form named (F_Sites). The
link field is Site_ID between the M_Sites and M_Income tables. I've used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#" &
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally given
by
BurtArkin.

Are you able to give me any other suggestions as to where I might be going
wrong here? Any help would be greatly appreciated, I feel like I'm so
close
here!

I've looked at the database that you emailed to me. I wasn't
understanding
the context in which you were using the continuous forms view of the
form. I
was understanding that it was a standalone form -- but you're using it as
a
subform. Therefore, there is a linking field between the main form and
the
subform that also must be included as a criterion in the DMax function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff textbox
to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

< snipped >

 

[duckylegs]

Thank you for your reply.
The main form and the subform are derived directly from their associated
tables. I'm new at this, I don't know exactly how to describe them to you.
Is there a way to export the list of fields from the tables, or is there
somewhere I can email the database to you directly?
I think you're right that I need another filtering field, or else I haven't
linked the two tables correctly. I've used just the Dmax function to get the
details of the dates being used, but I'm none the wiser with that data
either.
Thanks and kind regards

In order for us to assist, we'll need more details about the two tables --
what are the fields, what are the data types of each field, etc. for the two
tables. Also, provide us with the SQL statements of the main form's
RecordSource query and the subform's RecordSource query.

If you're seeing erratic results, it's likely because you need at least one
more filtering field in the DMax function, but can't suggest what it might
be until we know more about the tables and forms.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken, I saw this post and got excited, I thought I had a solution to a
very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table (M_Income),
which is displayed in a continuous subform on a form named (F_Sites). The
link field is Site_ID between the M_Sites and M_Income tables. I've used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#" &
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally given
by
BurtArkin.

Are you able to give me any other suggestions as to where I might be going
wrong here? Any help would be greatly appreciated, I feel like I'm so
close
here!

I've looked at the database that you emailed to me. I wasn't
understanding
the context in which you were using the continuous forms view of the
form. I
was understanding that it was a standalone form -- but you're using it as
a
subform. Therefore, there is a linking field between the main form and
the
subform that also must be included as a criterion in the DMax function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff textbox
to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

< snipped >

 

[Ken Snell \(MVP\)]

List the table structures this way:

Table1 Name
Field1 Name (indicate if primary key or foreign key, if either; give
data type)
Field2 Name (as above)
Field3 Name (as above)
etc.

Identify which table is used in the main form and which is used in the
subform. Identify if you have established any relationships (Relationships
window) between fields in the tables.

--

Ken Snell
<MS ACCESS MVP>

Thank you for your reply.
The main form and the subform are derived directly from their associated
tables. I'm new at this, I don't know exactly how to describe them to
you.
Is there a way to export the list of fields from the tables, or is there
somewhere I can email the database to you directly?
I think you're right that I need another filtering field, or else I
haven't
linked the two tables correctly. I've used just the Dmax function to get
the
details of the dates being used, but I'm none the wiser with that data
either.
Thanks and kind regards

In order for us to assist, we'll need more details about the two
tables --
what are the fields, what are the data types of each field, etc. for the
two
tables. Also, provide us with the SQL statements of the main form's
RecordSource query and the subform's RecordSource query.

If you're seeing erratic results, it's likely because you need at least
one
more filtering field in the DMax function, but can't suggest what it
might
be until we know more about the tables and forms.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken, I saw this post and got excited, I thought I had a solution to
a
very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table (M_Income),
which is displayed in a continuous subform on a form named (F_Sites).
The
link field is Site_ID between the M_Sites and M_Income tables. I've
used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#"
&
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally
given
by
BurtArkin.

Are you able to give me any other suggestions as to where I might be
going
wrong here? Any help would be greatly appreciated, I feel like I'm so
close
here!

:

I've looked at the database that you emailed to me. I wasn't
understanding
the context in which you were using the continuous forms view of the
form. I
was understanding that it was a standalone form -- but you're using it
as
a
subform. Therefore, there is a linking field between the main form and
the
subform that also must be included as a criterion in the DMax
function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate
value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff
textbox
to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

< snipped >

 

[duckylegs]

Main Table - M_Sites

Site_ID - Autonumber - Indexed No duplicates
Site_No Number - Primary Key
Building_Name - Text
Building_Street_Number - Text
Building_Suburb - Text
Date_Installed - Date
Date_Removed - Date
and various other fields that don't relate to this problem, I think

SubForm Table - M_Income

Income_ID - Autonumber - Indexed no duplicates
Site_ID - FK - Number - Indexed - duplicates ok
Collection_Date - Date - Indexed - duplciates ok

*Site_ID and Collection_Date are concatenated primary key - to prevent entry
of duplicates

Income_Month_ID - number
Income - currency
Commission_Date - date
Commission_Paid - currency

There is a one to many relationship between M_Sites and M_Income, with
Site_ID as the field joining the two tables.

The following are a sample of the results I'm getting -

Site 100
Collection_Date Result
30/3/07 25 - correct
5/3/07 -25
4/1/07 -85
1/1/07 blank

Site 101

30/3/07 25 - correct
5/3/07 -25
1/1/07 blank

Site 102

1/5/07 123
30/3/07 25 - correct
5/3/07 -57
2/2/07 35 - correct
29/12/06 blank

Hope this makes sense, thanks for your time.

List the table structures this way:

Table1 Name
Field1 Name (indicate if primary key or foreign key, if either; give
data type)
Field2 Name (as above)
Field3 Name (as above)
etc.

Identify which table is used in the main form and which is used in the
subform. Identify if you have established any relationships (Relationships
window) between fields in the tables.

--

Ken Snell
<MS ACCESS MVP>

Thank you for your reply.
The main form and the subform are derived directly from their associated
tables. I'm new at this, I don't know exactly how to describe them to
you.
Is there a way to export the list of fields from the tables, or is there
somewhere I can email the database to you directly?
I think you're right that I need another filtering field, or else I
haven't
linked the two tables correctly. I've used just the Dmax function to get
the
details of the dates being used, but I'm none the wiser with that data
either.
Thanks and kind regards

In order for us to assist, we'll need more details about the two
tables --
what are the fields, what are the data types of each field, etc. for the
two
tables. Also, provide us with the SQL statements of the main form's
RecordSource query and the subform's RecordSource query.

If you're seeing erratic results, it's likely because you need at least
one
more filtering field in the DMax function, but can't suggest what it
might
be until we know more about the tables and forms.

--

Ken Snell
<MS ACCESS MVP>




Hi Ken, I saw this post and got excited, I thought I had a solution to
a
very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table (M_Income),
which is displayed in a continuous subform on a form named (F_Sites).
The
link field is Site_ID between the M_Sites and M_Income tables. I've
used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#"
&
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally
given
by
BurtArkin.

Are you able to give me any other suggestions as to where I might be
going
wrong here? Any help would be greatly appreciated, I feel like I'm so
close
here!

:

I've looked at the database that you emailed to me. I wasn't
understanding
the context in which you were using the continuous forms view of the
form. I
was understanding that it was a standalone form -- but you're using it
as
a
subform. Therefore, there is a linking field between the main form and
the
subform that also must be included as a criterion in the DMax
function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate
value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff
textbox
to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

--

Ken Snell
<MS ACCESS MVP>


< snipped >

 

[duckylegs]

sorry, I forgot to put in the dates that results from the DMax on it's own

Site 100
Collection_Date Result
30/3/07 25 5/3/07
5/3/07 -25 30/3/07
4/1/07 -85 30/3/07
1/1/07 blank

Site 101

30/3/07 25 5/3/07
5/3/07 -25 30/3/07
1/1/07 blank

Site 102

1/5/07 123 29/12/06
30/3/07 25 5/3/07
5/3/07 -57 1/5/07
2/2/07 35 29/12/06
29/12/06 blank

There must be some logic here, but it's escaping me ...

List the table structures this way:

Table1 Name
Field1 Name (indicate if primary key or foreign key, if either; give
data type)
Field2 Name (as above)
Field3 Name (as above)
etc.

Identify which table is used in the main form and which is used in the
subform. Identify if you have established any relationships (Relationships
window) between fields in the tables.

--

Ken Snell
<MS ACCESS MVP>

Thank you for your reply.
The main form and the subform are derived directly from their associated
tables. I'm new at this, I don't know exactly how to describe them to
you.
Is there a way to export the list of fields from the tables, or is there
somewhere I can email the database to you directly?
I think you're right that I need another filtering field, or else I
haven't
linked the two tables correctly. I've used just the Dmax function to get
the
details of the dates being used, but I'm none the wiser with that data
either.
Thanks and kind regards

In order for us to assist, we'll need more details about the two
tables --
what are the fields, what are the data types of each field, etc. for the
two
tables. Also, provide us with the SQL statements of the main form's
RecordSource query and the subform's RecordSource query.

If you're seeing erratic results, it's likely because you need at least
one
more filtering field in the DMax function, but can't suggest what it
might
be until we know more about the tables and forms.

--

Ken Snell
<MS ACCESS MVP>




Hi Ken, I saw this post and got excited, I thought I had a solution to
a
very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table (M_Income),
which is displayed in a continuous subform on a form named (F_Sites).
The
link field is Site_ID between the M_Sites and M_Income tables. I've
used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#"
&
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally
given
by
BurtArkin.

Are you able to give me any other suggestions as to where I might be
going
wrong here? Any help would be greatly appreciated, I feel like I'm so
close
here!

:

I've looked at the database that you emailed to me. I wasn't
understanding
the context in which you were using the continuous forms view of the
form. I
was understanding that it was a standalone form -- but you're using it
as
a
subform. Therefore, there is a linking field between the main form and
the
subform that also must be included as a criterion in the DMax
function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate
value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff
textbox
to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

--

Ken Snell
<MS ACCESS MVP>


< snipped >

 

[Ken Snell \(MVP\)]

Ahhhh... you're using a non-US date format in the DMax function. Jet is
designed to use US format for date values unless you provide it with a
completely nonambiguous date format that cannot be misinterpreted. In your
case, you're trying to use European-type format, and Jet is interpreting the
resulting date as a US format; hence, the calculations are wrong.

Change your expression to this (note the change in the second argument of
the Format function):

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#" &
Format([Collection_Date],"mm\/dd\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

--

Ken Snell
<MS ACCESS MVP>

Main Table - M_Sites

Site_ID - Autonumber - Indexed No duplicates
Site_No Number - Primary Key
Building_Name - Text
Building_Street_Number - Text
Building_Suburb - Text
Date_Installed - Date
Date_Removed - Date
and various other fields that don't relate to this problem, I think

SubForm Table - M_Income

Income_ID - Autonumber - Indexed no duplicates
Site_ID - FK - Number - Indexed - duplicates ok
Collection_Date - Date - Indexed - duplciates ok

*Site_ID and Collection_Date are concatenated primary key - to prevent
entry
of duplicates

Income_Month_ID - number
Income - currency
Commission_Date - date
Commission_Paid - currency

There is a one to many relationship between M_Sites and M_Income, with
Site_ID as the field joining the two tables.

The following are a sample of the results I'm getting -

Site 100
Collection_Date Result
30/3/07 25 - correct
5/3/07 -25
4/1/07 -85
1/1/07 blank

Site 101

30/3/07 25 - correct
5/3/07 -25
1/1/07 blank

Site 102

1/5/07 123
30/3/07 25 - correct
5/3/07 -57
2/2/07 35 - correct
29/12/06 blank

Hope this makes sense, thanks for your time.

List the table structures this way:

Table1 Name
Field1 Name (indicate if primary key or foreign key, if either; give
data type)
Field2 Name (as above)
Field3 Name (as above)
etc.

Identify which table is used in the main form and which is used in the
subform. Identify if you have established any relationships
(Relationships
window) between fields in the tables.

--

Ken Snell
<MS ACCESS MVP>

Thank you for your reply.
The main form and the subform are derived directly from their
associated
tables. I'm new at this, I don't know exactly how to describe them to
you.
Is there a way to export the list of fields from the tables, or is
there
somewhere I can email the database to you directly?
I think you're right that I need another filtering field, or else I
haven't
linked the two tables correctly. I've used just the Dmax function to
get
the
details of the dates being used, but I'm none the wiser with that data
either.
Thanks and kind regards




:

In order for us to assist, we'll need more details about the two
tables --
what are the fields, what are the data types of each field, etc. for
the
two
tables. Also, provide us with the SQL statements of the main form's
RecordSource query and the subform's RecordSource query.

If you're seeing erratic results, it's likely because you need at
least
one
more filtering field in the DMax function, but can't suggest what it
might
be until we know more about the tables and forms.

--

Ken Snell
<MS ACCESS MVP>




Hi Ken, I saw this post and got excited, I thought I had a solution
to
a
very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table
(M_Income),
which is displayed in a continuous subform on a form named
(F_Sites).
The
link field is Site_ID between the M_Sites and M_Income tables. I've
used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#"
&
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally
given
by
BurtArkin.

Are you able to give me any other suggestions as to where I might be
going
wrong here? Any help would be greatly appreciated, I feel like I'm
so
close
here!

:

I've looked at the database that you emailed to me. I wasn't
understanding
the context in which you were using the continuous forms view of
the
form. I
was understanding that it was a standalone form -- but you're using
it
as
a
subform. Therefore, there is a linking field between the main form
and
the
subform that also must be included as a criterion in the DMax
function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate
value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff
textbox
to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

--

Ken Snell
<MS ACCESS MVP>


< snipped >

 

[duckylegs]

Ken, what can I say, you're a legend. Thank you sooo much for that, I might
be able to sleep tonight now.

Works like a charm!

Ahhhh... you're using a non-US date format in the DMax function. Jet is
designed to use US format for date values unless you provide it with a
completely nonambiguous date format that cannot be misinterpreted. In your
case, you're trying to use European-type format, and Jet is interpreting the
resulting date as a US format; hence, the calculations are wrong.

Change your expression to this (note the change in the second argument of
the Format function):

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#" &
Format([Collection_Date],"mm\/dd\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

--

Ken Snell
<MS ACCESS MVP>

Main Table - M_Sites

Site_ID - Autonumber - Indexed No duplicates
Site_No Number - Primary Key
Building_Name - Text
Building_Street_Number - Text
Building_Suburb - Text
Date_Installed - Date
Date_Removed - Date
and various other fields that don't relate to this problem, I think

SubForm Table - M_Income

Income_ID - Autonumber - Indexed no duplicates
Site_ID - FK - Number - Indexed - duplicates ok
Collection_Date - Date - Indexed - duplciates ok

*Site_ID and Collection_Date are concatenated primary key - to prevent
entry
of duplicates

Income_Month_ID - number
Income - currency
Commission_Date - date
Commission_Paid - currency

There is a one to many relationship between M_Sites and M_Income, with
Site_ID as the field joining the two tables.

The following are a sample of the results I'm getting -

Site 100
Collection_Date Result
30/3/07 25 - correct
5/3/07 -25
4/1/07 -85
1/1/07 blank

Site 101

30/3/07 25 - correct
5/3/07 -25
1/1/07 blank

Site 102

1/5/07 123
30/3/07 25 - correct
5/3/07 -57
2/2/07 35 - correct
29/12/06 blank

Hope this makes sense, thanks for your time.

List the table structures this way:

Table1 Name
Field1 Name (indicate if primary key or foreign key, if either; give
data type)
Field2 Name (as above)
Field3 Name (as above)
etc.

Identify which table is used in the main form and which is used in the
subform. Identify if you have established any relationships
(Relationships
window) between fields in the tables.

--

Ken Snell
<MS ACCESS MVP>


Thank you for your reply.
The main form and the subform are derived directly from their
associated
tables. I'm new at this, I don't know exactly how to describe them to
you.
Is there a way to export the list of fields from the tables, or is
there
somewhere I can email the database to you directly?
I think you're right that I need another filtering field, or else I
haven't
linked the two tables correctly. I've used just the Dmax function to
get
the
details of the dates being used, but I'm none the wiser with that data
either.
Thanks and kind regards




:

In order for us to assist, we'll need more details about the two
tables --
what are the fields, what are the data types of each field, etc. for
the
two
tables. Also, provide us with the SQL statements of the main form's
RecordSource query and the subform's RecordSource query.

If you're seeing erratic results, it's likely because you need at
least
one
more filtering field in the DMax function, but can't suggest what it
might
be until we know more about the tables and forms.

--

Ken Snell
<MS ACCESS MVP>




Hi Ken, I saw this post and got excited, I thought I had a solution
to
a
very
similar problem I've been struggling with.

I've I have a series of dates and income amounts in a table
(M_Income),
which is displayed in a continuous subform on a form named
(F_Sites).
The
link field is Site_ID between the M_Sites and M_Income tables. I've
used
your code from this post as follows:

=IIf(Len([Collection_Date] &
"")>0,DateDiff("d",DMax("Collection_Date","M_Income","Collection_Date<#"
&
Format([Collection_Date],"dd\/mm\/yyyy") & "# And Site_ID=" &
[Site_ID]),[Collection_Date]),Null)

But I'm getting pretty eratic results, similar to those originally
given
by
BurtArkin.

Are you able to give me any other suggestions as to where I might be
going
wrong here? Any help would be greatly appreciated, I feel like I'm
so
close
here!

:

I've looked at the database that you emailed to me. I wasn't
understanding
the context in which you were using the continuous forms view of
the
form. I
was understanding that it was a standalone form -- but you're using
it
as
a
subform. Therefore, there is a linking field between the main form
and
the
subform that also must be included as a criterion in the DMax
function's
third argument's string.

Additionally, for a record that does not yet have a ChronTransDate
value,
the #Error value shows in the textbox.

To fix both problems, change the Control Source for the TxtDiff
textbox
to
this:

=IIf(Len([ChronTransDate] &
"")>0,DateDiff("d",DMax("ChronTransDate","ChronTransfusion","ChronTransDate<#"
& Format([ChronTransDate],"mm\/dd\/yyyy") & "# And PatientID=" &
[PatientID]),[ChronTransDate]),Null)

--

Ken Snell
<MS ACCESS MVP>


< snipped >

 

[Ken Snell \(MVP\)]

You're welcome.