If statement with date diff..help please

ciskid

Registered User.
Local time
Today, 15:06
Joined
Jul 24, 2006
Messages
30
Hi Folks

Im in the realms of calculated filelds within queries :eek:

Im now at a stage where I want another calucating field.

I log fauts/problems/issues to a simple database

This has a field called opendate

When the case is closed, which it updates a filed called closedate

The format is dd/mm/y hh:mm

I would like to perform something like the following

If closedate - opendate <24hrs then ouput a 1 in the field , if >24hrs then output a 2

Then I will do a dcount (very happy with dcount) on the vales 1 so I can get an idea of all calls closed in <24hrs and also if I dcount 2 I will see all calls that took over 24hrs too clear.

Once Ive done this , I can have varying fields with >24<48 >48<62 etc

Does that make sense ?

I have another date question.

I would like a another calculating field so that if the case was open and closed on the same day , i.e open at 15/8/2006 and was closed on 15/8/6 then I have a calculated field output a value of 5 or some other value ? (I will then docunt this as its for a report)

Im realy strugling here and would apreciate some pointers ? :confused: :confused:

Many thanks

Jimmy
 
Just thought I would let you know i did the following

SLA: Format([dateclosed]-[dateopen])

This gives me values in days 1.1 2.36 etc

But at least now I can do a Dcount on all calls <=1 and may calls >1 >2 etc

Watch this space.

Still looking for amy good tips from you guys :-)

Cheers

Jimmy
 
Hi Folks

I have been with a calulated filed in my querie.

I have Expr1: ([dateopen]-[dateclosed])

Please remember that my dateopen and dateclosed are formatted as ddmmyy hhmm

Its not as simlistic as I thought for instance here is a snapshot of my table

dateopen dateclosed Expr1

21/07/2006 11:17:06 21/07/2006 11:17:06 0
21/07/2006 11:19:21 21/07/2006 11:19:27 -6.94444461259991E-05
02/08/2006 16:50:41 02/08/2006 16:50:52 -1.27314808196388E-04

So the 1st call was closed in zero seconds and gave a value of 0
So the 2nd call was closed in 6 seconds and gave a value of -6.94444461259991E-05
So the 3rd call was closed in 11 seconds and gave a value of -1.27314808196388E-04


Im way confused in how to get my valuse the way I want....its late and im off to bed :( :( :( :( :( :( :( :(

Please give me a virtual slap and let me knwo the error of my ways
 
You want the DateDiff function...
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

So, to find how many days a case was open...
DateDiff("d", [dateopen], [dateclosed])

Closed on the same day = 0
Closed the next day = 1
etc

or if you want hours,
DateDiff("h", [dateopen], [dateclosed])
then you can filter on <= 24 or whatever
 
Adeptus said:
You want the DateDiff function...
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

So, to find how many days a case was open...
DateDiff("d", [dateopen], [dateclosed])

Closed on the same day = 0
Closed the next day = 1
etc

or if you want hours,
DateDiff("h", [dateopen], [dateclosed])
then you can filter on <= 24 or whatever

Hio Adeptus

Ive been away for a bit and Ive just cheecked out your post.

Brilliant , all sorted now.

can I ask you another question ?

I have an expression and Im trying to find a value that is greater than 1 but less than 2

I cant seem to get it right ?

Can you help me out ?
Cheers

Jimmy
 
You want the DateDiff function...
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

So, to find how many days a case was open...
DateDiff("d", [dateopen], [dateclosed])

If I want :

If (DateDiff("d", [dtDue], [dtOne]) > 30) And (DateDiff("d", [dtDue], [dtOne]) < 60) Then ...

It does not seem to be resolving this. Does anyone know where my problem might be?

I want "if a date difference is between 30 days and 60 days then" ....do.cmd etc....

thanks in advance,
sjl
 
I tried this (blue are lines in question), and it still is not working as I thought it should:

Dim NumDays As Byte
Dim dtOne As Date
Dim dtDue As Date

dtOne = Date 'todays date

Do While Not rst.EOF
dtDue = rst!dtmNPacketDue
'strto = rst!strSMName
strproject = rst!strBrochureName
strprotocol = rst!strNIHProtocolNum
strstudymgr = rst!strSMName


NumDays = DateDiff("d", [dtDue], [dtOne])

If NumDays > 30 And NumDays < 60 Then

DoCmd.SendObject , , , "xein@xxxxx.yyy.gov", , , "Due Date Approaching", _
"The NIEHS packet due date for " & strproject & ", " & strprotocol & ", is: " & vbCrLf & vbCrLf & dtDue _
& vbCrLf & "which is 1-2 months from today. Please make a note of it." _
& vbCrLf & vbCrLf & "IRB coordinator" & vbCrLf & "Elizabeth Dean", False

End If
rst.MoveNext
Loop
 

Users who are viewing this thread

Back
Top Bottom