Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Posts
    9

    Unanswered: Including DateAdd in IIf function

    Ive searched the web and found many help sites, but none that covers my 'problem' specifically. I am new to Access and I apologize in advance if my terms are incorrect or confusing.

    I need to write an IIf statement that includes the DateAdd in order to add hours or days to a date entered into the main form.

    Something along these lines:

    IIf([*****]=3,DateAdd([Date],'h',24),IIf([*****]=4,DateAdd([Date],'h',48))

    Where ***** is a field from the input form
    Where [Date] is from the input form


    I need to return the original date + hours based on ***** value to a new field.

    My description might be poor, but at least I know what I mean. :P

    Thanks for any help you guys can give me!

    Darren

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it looks like nothing more complicated than a missing ")"


    ?? izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It looks to me like you are also missing you second false condition. If the field is not 3 and it is not 4 then what are you going to do?

  4. #4
    Join Date
    Feb 2005
    Posts
    9
    IIf([*****]=3,360,IIf([*****]=4,720))

    In order to test the return of different values, I wrote this and it works. If the condition of _false_ is not set, it simply leaves it blank and doesnt affect the outcome. The before-mentioned code was written with this mentality. I have tried it both ways, and neither work (I dont think the false needs to be there).

    I have not been successful adding DateAdd() to an IIf statement. Can it be done?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are both right about the false
    ...but you still have a missing ")" in the first example you posted.

    Code:
    IIf([*****]=3,				'if
    	DateAdd([Date],'h',24),			'true
    	IIf([*****]=4,				'elseif
    		DateAdd([Date],'h',48)			'true
    							'optional else ??? missing ???
    	)					'endif
    					'endif missing
    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Feb 2005
    Posts
    9
    I appreciate the help Izy and Im sorry if I seem dense but...

    Can you put the ( in my originally posted code? I cant understand the code you posted......sorry...

    IIf([*****]=3,DateAdd([Date],'h',24),IIf([*****]=4,DateAdd([Date],'h',48))

    Thanks!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and why not include the optional else as zero so everything is under your control, rather than leaving the system to imagine it's own Null/""/0 ?

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    stick it on the end.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2005
    Posts
    9
    thanks Izy for the help and the patience. I will give it a shot.

  10. #10
    Join Date
    Feb 2005
    Posts
    9
    For each record contain 3 and 4 in the ***** field, it is returning #Error

    That is more than it would do before and I thank you.

    Let me break this down to undrstand more:

    IIf([*****]=3,DateAdd([Time and Date],'d',1),IIf([*****]=4,DateAdd([Time and Date],'d',2)))

    Time and Date is a field from the main data table that was input by me in __:__:__ __ __/__/____ format.

    ***** is a field in the main data table that was inputted by me.

    So if ***** is 3, it should add 1 day to the value in the Time and Date field and output a new value. Is my thinking correct? I am going crazy looking at all this!

    The formula can include a _false_ if it needs to. I am not opposed, its just less for me to look at when getting this thing to work.

    Thanks

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    we are both mad!
    dateadd is the wrong way round!

    dateadd("d", 99, now())

    izy
    Last edited by izyrider; 02-16-05 at 14:17.
    currently using SS 2008R2

  12. #12
    Join Date
    Feb 2005
    Posts
    9
    *BLUSHES*

    It worked! the right code helped a ton. :P

    Izy, I appreciate you helping me get this working.

    I have to add 1 and 2, then 5-12 to that statement, and hopefully, I put in enough )...............

    THANKS!!!!!!!

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yeah, well it was both of us being dumb!

    meanwhile, iif(a, b, iif(c, d, iif(e, f, iif(g, h, iif......etc.....)))))))))) is a really ugly expression.

    every element in each of the nested iif() will be evaluated before a result is returned (iif is different from inline if-then-else in this respect).

    if (i have a mildly suspicious mind) this is some sort of homework, correctly nesting 10 iif's is a good exercise in matching parenthesis but it is not going to be brilliant code. needs some more thinking!

    best of luck, izy
    currently using SS 2008R2

  14. #14
    Join Date
    Feb 2005
    Posts
    9
    Izy, if there is a better way to write that or code it, please help me! Im not above help at all! I have worked in Access for roughly 36 hours. This isnt really homework per se, but it is a personal db that I am making for my own use.

    Im sure it can be done in VB in the code part of Access (hell I dont even know the terms!) and I would love to learn that part. When you posted that code earlier, I *think* thats the format you are talking about, but Im truly lost with it.

    i couldnt make an If = blah blah Then blah blah work at all.

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    check out the switch() function and see if it speaks to you.

    (you will need VB-help which in turn means you need a form (blank form will do) and from there the code window)

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •