Sumif in MS Access??

[Dklein]

Hello,

I am importing data from MS Outlook into MS Access via the linked table funtion to ensure real-time updates of the mailbox. I then want to do a sum on the Date Received and Date modified fields to see how much time has elapsed between the two, to produce Service levels etc... but i'm stuck!!

I would appreciate any assistance please on the best way to do this etc.....

THANKS IN ADVANCE!!!

Dklein

 

[PaulBricker]

Use the DateDiff Fucntion in an Access Query.
ElaspedTimeateDiff("d",[DateReceived],[DateModified])

This will return the number in days. You can get it in almost any increment you want.

Paul

 

[Dklein]

Thanks Paul,

I've still got problems applying the function. at the moment the data contained in the MODIFIED and the in the RECEIVED fields are "DD/MM/YYY HH:MM:SS". I was hoping to go down to seconds level and show the difference in the two fields in a seperate field on a report.

Could I apply this function to a text box in a report? I really appreciate your help!

Dklein

 

[JoyInOK]

Yes you can, though I would dread having my response time ranked down to the second!
The "d" in PaulBricker's DateDiff formula says "give me the difference in days" You can also ask for the diffence in seconds by substituting an "s" for the "d." Then format the result in months, days, hours, seconds format.

 

[Dklein]

Thanks,

I've tried to put the formula in a text box in a report

DateDiff("s",[Modified],[Created])

but I get an error message saying syntax error (comma) in query expression? could you help at all.

I really appreciate it!! The deadline is drawing near!!

Dave

 

[PaulBricker]

My first thought is that there is something in the data itself that is causing the error. The format should be
29/03/2003 06:36:22 PM

There isn't anything wrong with your DateDiff function that I can see. One other thing is to put an ='s sign in front of the function if it's the control source for a textbox
=DateDiff("s",[Modified],[Created])
but that should cause the error you are getting.

Paul

 

[danvlas]

Check the list separator in Control Panel. I suspect you have something else than comma:

Start-Settings-Control Panel-Regional Settings
Check the Number tab
See what you have in the last box. And use that instead of comma...or change that to comma.

Good luck


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Dklein]

Thanks everyone. It works now. I re-keyed the function and it works now for some reason??

I appreciate you help!!