Days Between Calls by ID

[pyxi_styx via AccessMonster.com]

I have looked at over 20 answers to similar questions, but am unable to find
a working solution to my issue.
I have a report based upon a table Calls. The used fields are CustomerID,
CallNum, CallDate, CallTotal, and DaysBetween (calculated field).
What I need is for every customer call visit, a calculate between the last
visit (to the same customer, but not necessarily the same call number) and
this line item one.

I.e.
CustomerID CallNum CallDate CallTotal DaysBetween
FlStU 24324 1/15/09 $4,554 0
FlStU 24324 1/20/09 $342 5
FlStU 25421 2/1/09 $2,443 12
USF 24948 1/4/09 $974.15 0
USF 26485 2/4/09 $96.91 31
USF 26485 2/6/09 $421 2

In the DaysBetween Control source, I have
=DateDiff("d",NZ(DMax("[CallDate]","[CompanyID] = " & [CompanyID] & " AND
[CallDate] < #" & [CallDate] & "#"),[CallDate]),[CallDate])

Please oh please help... this is killing me!

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "DaysBetweenQuery.mdb" which illustrates how to do this. You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445.



--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Marshall Barton]

I have looked at over 20 answers to similar questions, but am unable to find
a working solution to my issue.
I have a report based upon a table Calls. The used fields are CustomerID,
CallNum, CallDate, CallTotal, and DaysBetween (calculated field).
What I need is for every customer call visit, a calculate between the last
visit (to the same customer, but not necessarily the same call number) and
this line item one.

I.e.
CustomerID CallNum CallDate CallTotal DaysBetween
FlStU 24324 1/15/09 $4,554 0
FlStU 24324 1/20/09 $342 5
FlStU 25421 2/1/09 $2,443 12
USF 24948 1/4/09 $974.15 0
USF 26485 2/4/09 $96.91 31
USF 26485 2/6/09 $421 2

In the DaysBetween Control source, I have
=DateDiff("d",NZ(DMax("[CallDate]","[CompanyID] = " & [CompanyID] & " AND
[CallDate] < #" & [CallDate] & "#"),[CallDate]),[CallDate])

It appears from your example that the CompanyID is a Text
field. If so, then it must be exclosed in quotes in the
condition:

. . . ,"CompanyID = '" & CompanyID & "' AND
or
. . . ,"CompanyID = """ & CompanyID & """ AND

Note that, in this case the [ ] are not needed so I left
them out to save typing the excess syntax.

 

[pyxi_styx via AccessMonster.com]

My apologies, CompanyID's row source is actually SELECT [Customer].[CustID],
[Customer].[CustName] FROM Customer ORDER BY [CustName]; It is, in fact, a
numeric field.

Ambyr

I have looked at over 20 answers to similar questions, but am unable to find
a working solution to my issue.

[quoted text clipped - 16 lines]

=DateDiff("d",NZ(DMax("[CallDate]","[CompanyID] = " & [CompanyID] & " AND
[CallDate] < #" & [CallDate] & "#"),[CallDate]),[CallDate])

It appears from your example that the CompanyID is a Text
field. If so, then it must be exclosed in quotes in the
condition:

. . . ,"CompanyID = '" & CompanyID & "' AND
or
. . . ,"CompanyID = """ & CompanyID & """ AND

Note that, in this case the [ ] are not needed so I left
them out to save typing the excess syntax.

 

[pyxi_styx via AccessMonster.com]

I could just squeeze you! That did it!
It's not 100% what I need quite yet, but I can definitely alter it as needed
from here!
You, sir, are fantastic!
Thank you,
Ambyr

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "DaysBetweenQuery.mdb" which illustrates how to do this. You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445.

I have looked at over 20 answers to similar questions, but am unable to
find

[quoted text clipped - 19 lines]

Please oh please help... this is killing me!

 

[Roger Carlson]

Great. Glad to help.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I could just squeeze you! That did it!
It's not 100% what I need quite yet, but I can definitely alter it as
needed
from here!
You, sir, are fantastic!
Thank you,
Ambyr

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "DaysBetweenQuery.mdb" which illustrates how to do this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445.

I have looked at over 20 answers to similar questions, but am unable to
find

[quoted text clipped - 19 lines]

Please oh please help... this is killing me!