using dateadd with iff

[Clancy]

I have two text boxes in Access 2002-2003 that contain a date/time and I am
trying to apply a an IIf statement through their control source, but for some
reason they are not giving me the desired result. I can change the order of
my arguments, but usually only the first two work.

The first text box, which is [Date/Time 5] has the control source:
= IIf([Field 1]>="96" Or [Field 1]>"100",DateAdd("h",-[ Date/Time 1]-1,[
Date/Time 2]),IIf([Field 1]<"24",DateAdd("h",-[ Date/Time 1],[ Date/Time
2]),IIf([Field 1]<"96" And [Field 1]>="24",DateAdd("h",[ Date/Time 3],[
Date/Time 4]))))

The second text box, which is [Date/Time 2] has the control source:
= IIf([Field 1]>="96" Or [Field 1]>"100",DateAdd("h",96,[ Date/Time
4]),IIf([Field 1]<"24",DateAdd("h",24,[ Date/Time 4]), IIf([Field 1]<"96" And
[Field 1]>="24",DateAdd("h",[ Date/Time 1],[ Date/Time 5]))))

I had to put the >"96" and >"100" because for some reason without the >"100"
it would give an error with the number was over 100.

 

[John W. Vinson]

I have two text boxes in Access 2002-2003 that contain a date/time and I am
trying to apply a an IIf statement through their control source, but for some
reason they are not giving me the desired result. I can change the order of
my arguments, but usually only the first two work.

The first text box, which is [Date/Time 5] has the control source:
= IIf([Field 1]>="96" Or [Field 1]>"100",DateAdd("h",-[ Date/Time 1]-1,[
Date/Time 2]),IIf([Field 1]<"24",DateAdd("h",-[ Date/Time 1],[ Date/Time
2]),IIf([Field 1]<"96" And [Field 1]>="24",DateAdd("h",[ Date/Time 3],[
Date/Time 4]))))

The second text box, which is [Date/Time 2] has the control source:
= IIf([Field 1]>="96" Or [Field 1]>"100",DateAdd("h",96,[ Date/Time
4]),IIf([Field 1]<"24",DateAdd("h",24,[ Date/Time 4]), IIf([Field 1]<"96" And
[Field 1]>="24",DateAdd("h",[ Date/Time 1],[ Date/Time 5]))))

I had to put the >"96" and >"100" because for some reason without the >"100"
it would give an error with the number was over 100.

If [Field 1] is a Text field, then > or >= operators will NOT work
numerically: the text string "100" is *LESS* than the text string "96",
because it's searching character by character (just as the text string "AXX"
sorts before the text string "ZK").

You have some leading blanks in your other field names - should it be

[Date/Time 1]

instead of

[ Date/Time 1]

perhaps? Also, I've seen many problems when special characters such as / are
used in fieldnames.

Just the fact that you have four (or five?) fields named Date/Time x is of
real concern. You should not have repeating fields like this in your table
design! What does this table, and what do these fields contain? What exactly
are you trying to accomplish? I strongly suspect that a different table design
may make your job easier.

John W. Vinson [MVP]

 

[Marshall Barton]

I have two text boxes in Access 2002-2003 that contain a date/time and I am
trying to apply a an IIf statement through their control source, but for some
reason they are not giving me the desired result. I can change the order of
my arguments, but usually only the first two work.

The first text box, which is [Date/Time 5] has the control source:
= IIf([Field 1]>="96" Or [Field 1]>"100",DateAdd("h",-[ Date/Time 1]-1,[
Date/Time 2]),IIf([Field 1]<"24",DateAdd("h",-[ Date/Time 1],[ Date/Time
2]),IIf([Field 1]<"96" And [Field 1]>="24",DateAdd("h",[ Date/Time 3],[
Date/Time 4]))))

The second text box, which is [Date/Time 2] has the control source:
= IIf([Field 1]>="96" Or [Field 1]>"100",DateAdd("h",96,[ Date/Time
4]),IIf([Field 1]<"24",DateAdd("h",24,[ Date/Time 4]), IIf([Field 1]<"96" And
[Field 1]>="24",DateAdd("h",[ Date/Time 1],[ Date/Time 5]))))

I had to put the >"96" and >"100" because for some reason without the >"100"
it would give an error with the number was over 100.

If fieldx is a numeric type field, then you should not be
comparing it to a string. IOW, get rid of the quotes.
=IIf([Field 1]>=96,DateAdd( . . .