time between dates

  • Thread starter Thread starter michael c
  • Start date Start date
M

michael c

Any suggestions for this would be fantastic. I have a
query that shows accounts and order dates. I'm trying to
create a report based on this query that will show the
time elapsed between order dates. Any thoughts? Thanks!!
 
See DateAdd() in the Online Help File.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Any suggestions for this would be fantastic. I have a
query that shows accounts and order dates. I'm trying to
create a report based on this query that will show the
time elapsed between order dates. Any thoughts? Thanks!!
Click to expand...

Take a look at DateDiff().

You don't say how your tables are structured, where the order dates
are stored, how the query is set up, or whether you want time in
seconds or years, so my answer has to be a bit terse... if you want
more details, please post more details!
 
I'm trying to figure out the time between each account's
orders. So, for example, the value in the
TimeElapsedFromLastOrder column for account #123 in the
second row above would be 6 days.
Click to expand...

Thanks, that helps a lot!

You'll need to use the DMax() function to find the largest previous
order date for that account. This gets a bit snarky if there *was* no
previous order, but try something like this as a calculated field in
your query:

DateDiff("d", NZ(DMax("[OrderDateM]", "yourtablename", "[SAP#] = " &
[SAP#] & " AND [OrderDateM] < #" & [OrderDateM] & "#"), [OrderDateM]),
[OrderDateM])

Working from the inside out, the DMax() function will return the most
recent prior order date; if there is none, NZ will return the current
record's order date. DateDiff will then calculate the number of days
between the most recent prior order and the current record's order
date. There are some other ways you can treat the first order (i.e.
use #1/1/1900# in the NZ function to get a ridiculously large number
of days, or an IIF); the way I suggest will return 0 for the interval
if there is no prior order.
 
Hi,



If you really have a consecutive Row value, bring the table twice in
the designer, drag table1_1.Row in the grid, add the criteria:

= [table1].[Row] + 1


Then,


TimeElapsedFromLastOrder: table1_1.OrderDatem - table1.OrderDatem


should do the trick. Note that doing so, I do not proceed "by sap#".


Unfortunately, if you probably don't have such a nice number, or if you
have to consider "for the same SAP", then, better try:

SELECT b.OrderDatem - a.OrderDatem As timeElapsedFromLastOrder
FROM myTable As a INNER JOIN myTable As b ON a.sap =b.sap
WHERE b.OrderDatem =(
SELECT MIN( c.OrderDatem)
FROM myTable As c
WHERE c.OrderDatem >
a.OrderDatem
AND c.sap = a. sap
)




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top