Microsoft Access Calculated Fields

[Miguelito Bain]

hi everybody.

i'm trying to find out what you can and can't do with/in calculated
fields. any help, pointers, and/or advice would be greatly
appreciated. i'm a newbie, but i want to learn, and i've tried a lot
before posting.

here's my situation.

i have a simple form with dates on it. i have an indate and outdate
text box. i retrieve these dates via a query. the query is bound to my
form, frmdates. i have a 3rd text box that calculates the number of
days between the indate and outdate. this is called days. i can also
enter dates manually in these 2 text boxes, and the difference is
calculated in the days text box.

right now, this is how i've got it set up. in my query, i created the
calculated field, days.

Days: [OutDate]-[InDate])

this works fine. but where i run into trouble is when the indate and
the outdate are the same. for example

indate 2/20/2004
outdate 2/20/2004

so naturally, days = 0. but i want this to be equal to 1 because i
want to capture that it was there for that 1 day. every time days = 0,
i want it to equal 1.

i've tried to write if/then statements in the field cell in the design
view of my query, but they haven't worked. here are a few of my
examples,

i really thought the 1st one would work in the field cell of my
query...
IF([OutDate]-[InDate]=0,Days:1,Days:[OutDate]-[InDate])
IF([OutDate]-[InDate]=0,tblCaseStat.Days=1,tblCaseStat.Days=
[OutDate]-[InDate])
Days: IF([OutDate]-[InDate]=0,Days:1,Days: [OutDate]-[InDate])
IF([OutDate]=[InDate],Days:1,Days:[OutDate]-[InDate])

as you can see, i tried a few variations because i wasn't sure if
access would accept the way my code was written.

i also tried adding code to some of the events on both the form and
text boxes. for example, the on current, before update, after update
events.

i've looked through my books, but their if/then examples deal with
text, not numbers. so i don't think i need the quotes with numbers,
right.

can you use calculated fields like this in expressions. do you just
refer to them like fields from your table. do you have to do the
calculation 1st before you can refer to it.

i guess it's the colon that throws me off. can you use a colon like
this or an equals sign.

IF([OutDate]-[InDate]=0,Days:1,Days:[OutDate]-[InDate])

should these be colons or equals signs.

thanks,

miguel

[Allen Browne]

You could use a text box with ControlSource of:
=IIF([OutDate]-[InDate]=0, 1, [OutDate]-[InDate])
Note the use of Immedate If.

But if you want to count the same date as a day, you may want to count both
days inclusive? That would mean that if InDate and OutDate were 1 day apart,
you would count both days (so the answer would be 2)?

You may also want to use DateDiff(). Particularly if there are time
components in the field, there can be a difference. For example, DateDiff()
returns 1 for:
? DateDiff("d", #2/23/2004 1:00am#, #2/24/2004 11:00pm#)
1
whereas subtracting the two fields returns almost 2:
? #2/24/2004 11:00pm# - #2/23/2004 1:00am#
1.91666666667152

For general suggestions on ensuring Access understands your calculated
fields, see:
Calcualted fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miguelito Bain" <mi***********@hotmail.com> wrote in message
news:1b**************************@posting.google.c om...

i'm trying to find out what you can and can't do with/in calculated
fields. any help, pointers, and/or advice would be greatly
appreciated. i'm a newbie, but i want to learn, and i've tried a lot
before posting.

here's my situation.

i have a simple form with dates on it. i have an indate and outdate
text box. i retrieve these dates via a query. the query is bound to my
form, frmdates. i have a 3rd text box that calculates the number of
days between the indate and outdate. this is called days. i can also
enter dates manually in these 2 text boxes, and the difference is
calculated in the days text box.

right now, this is how i've got it set up. in my query, i created the
calculated field, days.

Days: [OutDate]-[InDate])

this works fine. but where i run into trouble is when the indate and
the outdate are the same. for example

indate 2/20/2004
outdate 2/20/2004

so naturally, days = 0. but i want this to be equal to 1 because i
want to capture that it was there for that 1 day. every time days = 0,
i want it to equal 1.

i've tried to write if/then statements in the field cell in the design
view of my query, but they haven't worked. here are a few of my
examples,

i really thought the 1st one would work in the field cell of my
query...
IF([OutDate]-[InDate]=0,Days:1,Days:[OutDate]-[InDate])
IF([OutDate]-[InDate]=0,tblCaseStat.Days=1,tblCaseStat.Days=
[OutDate]-[InDate])
Days: IF([OutDate]-[InDate]=0,Days:1,Days: [OutDate]-[InDate])
IF([OutDate]=[InDate],Days:1,Days:[OutDate]-[InDate])

as you can see, i tried a few variations because i wasn't sure if
access would accept the way my code was written.

i also tried adding code to some of the events on both the form and
text boxes. for example, the on current, before update, after update
events.

i've looked through my books, but their if/then examples deal with
text, not numbers. so i don't think i need the quotes with numbers,
right.

can you use calculated fields like this in expressions. do you just
refer to them like fields from your table. do you have to do the
calculation 1st before you can refer to it.

i guess it's the colon that throws me off. can you use a colon like
this or an equals sign.

IF([OutDate]-[InDate]=0,Days:1,Days:[OutDate]-[InDate])

should these be colons or equals signs.