Date Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Everyone!

I have two date fields:-
1. Shows the data that an application was data entered
2. the other shows the date that a file has moved to a new status.

I have set up a formula that does a simple count of the days in between the
Data entry date and the new status date. If the dates are the same for both
fields it returns a value of 0 (zero) which it should do. however I need it
to cound a zero as 1. Is there a way to make it count the zero as a 1, but
physically count the correct amount of days if the amount of days between the
two dates is greater than 0???

Thanks
 
depending on where and how you're doing the calculation, you can probably
use either an IIf() function or a VBA If...Then...Else statement to say, in
essence:

If the result of the formula equals zero (0), then display/return one (1),
otherwise display/return the result of the formula.

see Access Help for details on the use and syntax of the IIf() function, and
VBA Help for use of the If statement.

hth
 
Hello Tina

The Calculation is being carried out in the data entry field. I have a field
called "DateDataEntered" and the other called "DateOfDecision"

There is another field called "TurnAroundTime" which calculates the
difference in the two other fields using a basic sum
[DateOfDecision]-[DateDataEntered]

I tried to add the following IIF statement to the above calculation and it
did not work:-

IIF([DateOfDecision]-[DateDataEntered])="0","1")

What am I doing wrong???
 
The Calculation is being carried out in the data entry field.

it's not clear what you mean by that. in a form you can't perform a
calculation "in" a control that's bound to a field in the underlying table
or query. by definition, a calculated control is unbound - meaning any data
displayed in it is NOT directly saved into a field.

if you're performing the calculation in a query, and using the query as the
form's RecordSource, then you *can* bind the query's calculated field to a
control in the form. but again, that's for display purposes only - the value
can't be saved directly into a table field. (and that's not a problem,
because with rare exceptions, calculated values should NOT be saved into a
table.)

at any rate, i would replace the [date1] - [date2] expression with a
DateDiff() function, and use that in the IIf() function. (btw, your posted
IIf() function is incomplete. there is no value or expression supplied for
the Else argument.) try something along the lines of

IIf(DateDiff("d", [DateDataEntered], [DateOfDecision]) <= 1, 1,
DateDiff("d", [DateDataEntered], [DateOfDecision]))

the above expression all goes on one line. see Access Help for details on
the DateDiff() function.

hth


Sam said:
Hello Tina

The Calculation is being carried out in the data entry field. I have a field
called "DateDataEntered" and the other called "DateOfDecision"

There is another field called "TurnAroundTime" which calculates the
difference in the two other fields using a basic sum
[DateOfDecision]-[DateDataEntered]

I tried to add the following IIF statement to the above calculation and it
did not work:-

IIF([DateOfDecision]-[DateDataEntered])="0","1")

What am I doing wrong???
tina said:
depending on where and how you're doing the calculation, you can probably
use either an IIf() function or a VBA If...Then...Else statement to say, in
essence:

If the result of the formula equals zero (0), then display/return one (1),
otherwise display/return the result of the formula.

see Access Help for details on the use and syntax of the IIf() function, and
VBA Help for use of the If statement.

hth


between
the need
it between
the
Click to expand...
Click to expand...
 
Back
Top