Calculating Elapsed Time in the Table Fields

[vgsdrm2020]

Good Morning: I have been trying for about 8 hours to calculate elapsed time between two fields into a third field in Access 2007. Unfortunately, the expression
IIf(IsNull)([Final Suspense]),DateDiff("d",[Routing Status Date],Date()),DateDiff("d",[Routing Status Date],[Final Suspense]))
is not working. Image enclosed.


I need to know how to do this correctly by this afternoon, so I apologize for the flail. I have two products I must produce by 1300. I appreciate the help!

[ranman256]

dont other with the IF and the ISNULL in the function.
in the query set the criteria in the [FINAL SUSPENSE] field where NOT IS NULL.
this removes any nulls.

so the field will now be DATEDIFF("d",[FINAL SUSPENSE],date())

[ItsMe]

It seems you are using Calculated fields in the table. This is probably the issue. Maybe you can create some code behind a form and collect the info from the calculated field into a Variable. Then you can use the variable for your IIF or If Then Else ...

[vgsdrm2020]

I'm sorry, I am on a work computer and have limited coding capability
OK: so get the difference between my routing status date and final suspense, I do use the formula in a calculated field? I'm not trying to create a form, but if I have to? I want to see my results on my table so I can export to Excel when done.

[ItsMe]

It seems simple enough. You need the difference between two dates. The question is where are these two dates? Where is Routing Status Date and where is Final Suspense?

I ask, where, because if one of these is a calculated field, it complicates things. Can you provide the table name(s) and field data types for these two fields. If one is a calculated field, how and where is it getting its raw data from? What are the types, names, etc.

[vgsdrm2020]

Except for what I am trying to do, all data is entered manually by me (Yes I choose the date and format) but routing status date and final suspense are just dates. Table name is OI Index
So no, none are calculated

[ItsMe]

OK, I think I understand. What you can do is create an alias in a query. Start new. Create a new query for testing. Add your two date fields. Then, create an alias. The following syntax should work, you just need to use this syntax with a new alias.

IIf(IsNull([Final Suspense]), DateDiff("d", [Routing Status Date], Date()), DateDiff("d", [Routing Status Date], [Final Suspense]))

To create a new alias type a name for your alias in a new filed in the design grid at the bottom of your query designer. So to the right of Final Suspense, for instance, type MyAlias:

Right after the colon of your alias, paste the IIf statement.