newbie

[antonov]

hello everybody.... I started making a program in access and I do encounter
some problems with calculations and stuff....
I have a field called "in" a second one called "OUT" and a field where I
need a result based on the following:
if (out-in) = >0 but <4 then 0, else ((out-in)+1)*2
how and where can I input this?

thanks

 

[UpRider]

Put this as the data source for the results field.

=iif([out-in] >= 0 or [out-in] < 4,0,([out-in]+1) *2)

UpRider

 

[John Vinson]

hello everybody.... I started making a program in access and I do encounter
some problems with calculations and stuff....
I have a field called "in" a second one called "OUT" and a field where I
need a result based on the following:
if (out-in) = >0 but <4 then 0, else ((out-in)+1)*2
how and where can I input this?

thanks

You can do this in a Query - NOT in a Table (tables should not contain
calculated fields).

Store the field [IN] in your table - but rename it; IN is a reserved
word, used in query definitions - Access will get really confused when
you have a criterion like

WHERE IN IN(3, 4, 5)

Create a Query based on your table and in a vacant Field cell put

Fieldname: IIF([IN] > 0 AND [IN] < 4, 0, ([out] - [in] + 1) * 2)

You can base a Form or Report on this query. Note that the name of the
function is IIF - Immediate If - not just IF().

John W. Vinson[MVP]

 

[antonov]

ehmm... thanks for the suggestions but I keep getting error messages...
I forgot to mention that both IN and OUT are dates (dd/mmm/yy)
is there a different way of calculating dates?

hello everybody.... I started making a program in access and I do
encounter
some problems with calculations and stuff....
I have a field called "in" a second one called "OUT" and a field where I
need a result based on the following:
if (out-in) = >0 but <4 then 0, else ((out-in)+1)*2
how and where can I input this?

thanks

You can do this in a Query - NOT in a Table (tables should not contain
calculated fields).

Store the field [IN] in your table - but rename it; IN is a reserved
word, used in query definitions - Access will get really confused when
you have a criterion like

WHERE IN IN(3, 4, 5)

Create a Query based on your table and in a vacant Field cell put

Fieldname: IIF([IN] > 0 AND [IN] < 4, 0, ([out] - [in] + 1) * 2)

You can base a Form or Report on this query. Note that the name of the
function is IIF - Immediate If - not just IF().

John W. Vinson[MVP]

 

[John Vinson]

ehmm... thanks for the suggestions but I keep getting error messages...
I forgot to mention that both IN and OUT are dates (dd/mmm/yy)

What is the value of August 30 plus September 1?

Your expression makes no sense, as written! Could you explain what
you're trying to ACCOMPLISH with it?

I'm guessing that you need to use the DateDiff() function to calculate
a number of days between two dates, but... I cannot see your database
nor do I understand your problem. You do; I'll be happy to try to help
if you will give some fuller information!

John W. Vinson[MVP]

 

[antonov]

yes, that is correct. I need to see the difference between the 2 dates
including both days in the calculation (i.e. Out= 2-sept-05 In=1-sept-05.
The difference in this case must be 2 as the days are 2). if the difference
between Out and In = 3 or less then 0, else from 4 up to 15 I need to
multiply the whole difference by 2.

 

[John Vinson]

yes, that is correct. I need to see the difference between the 2 dates
including both days in the calculation (i.e. Out= 2-sept-05 In=1-sept-05.
The difference in this case must be 2 as the days are 2). if the difference
between Out and In = 3 or less then 0, else from 4 up to 15 I need to
multiply the whole difference by 2.

OK:

IIF(DateDiff("d", [In], [Out]) <= 3, 0, DateDiff("d", [In], [Out]) *
2)


John W. Vinson[MVP]

 

[antonov]

and.... where do I put it?

yes, that is correct. I need to see the difference between the 2 dates
including both days in the calculation (i.e. Out= 2-sept-05 In=1-sept-05.
The difference in this case must be 2 as the days are 2). if the
difference
between Out and In = 3 or less then 0, else from 4 up to 15 I need to
multiply the whole difference by 2.

OK:

IIF(DateDiff("d", [In], [Out]) <= 3, 0, DateDiff("d", [In], [Out]) *
2)


John W. Vinson[MVP]

 

[John Vinson]

and.... where do I put it?

In a vacant Field cell in the query design grid; or, if you just want
to see it on a Form or Report, in the Control Source property of a
textbox, preceded by an = sign.

John W. Vinson[MVP]

 

[antonov]

I am sorry but I cannot make this formula work....
If I input it in the control source of my form I get all sorts of error
messages but never the result I need to have.

yes, that is correct. I need to see the difference between the 2 dates
including both days in the calculation (i.e. Out= 2-sept-05 In=1-sept-05.
The difference in this case must be 2 as the days are 2). if the
difference
between Out and In = 3 or less then 0, else from 4 up to 15 I need to
multiply the whole difference by 2.

OK:

IIF(DateDiff("d", [In], [Out]) <= 3, 0, DateDiff("d", [In], [Out]) *
2)


John W. Vinson[MVP]

 

[John Vinson]

I am sorry but I cannot make this formula work....
If I input it in the control source of my form I get all sorts of error
messages but never the result I need to have.

Please post the exact text of the formula, and exactly where you're
putting it.

A Form does not HAVE a control source property so your statement must
be incorrect; where *did* you put the expression?

John W. Vinson[MVP]