Query Check

[Parariddle]

I am a heavy Business Objects user and coming back to access has meant changing my syntax. I have written an IIF statement with DateAdd but its returning an error.

I would be grateful if someone could have a look and let me know what I need to change !

Thank you !

NextDue2:IIF( [Completed] =True And [Frequency] ="Daily",DateAdd("d",1, [Next Due Date] ,IIF( [Completed] =True And [Frequency] = "Weekly",DateAdd("w",1, [Next Due Date] ,IIF( [Completed] = True And [Frequency] = "Monthly",DateAdd("M",1, [Next Due Date] ))))

 

[namliam]

It totaly helps if you format your stuff into something readable

NextDue2:
IIF( [Completed] =True And [Frequency] ="Daily"
  ,DateAdd("d",1, [Next Due Date] 
  ,IIF( [Completed] =True And [Frequency] = "Weekly"
    ,DateAdd("w",1, [Next Due Date] 
    ,IIF( [Completed] = True And [Frequency] = "Monthly"
      ,DateAdd("M",1, [Next Due Date] ))))

It is quite obvious to see now that you are missing the ) for each of the dateadd functions.

Good luck!

 

[Rabbie]

The syntax for an IIF statement is
IIF(Condition,Action if True, Action if False)

Looking at your statement you don't seem to have provided an action if false for the last IIF statement. You need to provide this action even if it will never occur. IIF evaluates both the True action and the False action everytime it is used)

This is in addition to the things Namliam has pointed out.

 

[Parariddle]

Thanks for the reply namliam !

I thought the 4 ) at the end would close everything (BO Trate)

I have added the ) to close each DateAdd Function but it seems that the query is bringing back an error.

NextDue2:
IIF( [Completed] =True And [Frequency] ="Daily"
,DateAdd("d",1, [Next Due Date])
,IIF( [Completed] =True And [Frequency] = "Weekly"
,DateAdd("w",1, [Next Due Date])
,IIF( [Completed] = True And [Frequency] = "Monthly"
,DateAdd("M",1, [Next Due Date])

 

[Rabbie]

You still need to add a False action to the last IIF statement

 

[Parariddle]

Thanks Rabbie I replied before I read your first post !

I have added the false and for that section the statement works but for True it is returning #Error ?

Please could you help on what this could be ?

 

[MSAccessRookie]

Thanks for the reply namliam !

I thought the 4 ) at the end would close everything (BO Trate)

I have added the ) to close each DateAdd Function but it seems that the query is bringing back an error.

NextDue2:
IIF( [Completed] =True And [Frequency] ="Daily"
,DateAdd("d",1, [Next Due Date])
,IIF( [Completed] =True And [Frequency] = "Weekly"
,DateAdd("w",1, [Next Due Date])
,IIF( [Completed] = True And [Frequency] = "Monthly"
,DateAdd("M",1, [Next Due Date])

Now you are missing two things:

1. Something to Do if the IIf(Monthly) statement is False
2. Closing Parentheses for the IIf Statements

[COLOR=black][FONT=Verdana]IIF( [Completed] =True And [Frequency] ="Daily", DateAdd("d",1, [Next Due Date])[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   , IIF( [Completed] =True And [Frequency] = "Weekly", DateAdd("w",1, [Next Due Date])[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       , IIF( [Completed] = True And [Frequency] = "Monthly", DateAdd("M",1, [Next Due Date])[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           [B][COLOR=red], {Something to Do if the [COLOR=darkgreen]IIf(Monthly)[/COLOR] statement is False}[/COLOR][COLOR=blue])))[/COLOR][/B][/FONT][/COLOR]

 

[Parariddle]

Thanks Rookie

As above I added your suggestions but the true is still bringing back an error. I have checked the fields are date formatted but am unsure what is happening ?

 

[MSAccessRookie]

Thanks Rookie

As above I added your suggestions but the true is still bringing back an error. I have checked the fields are date formatted but am unsure what is happening ?

In Access, True is supposed to be equal to -1. Try that instead

 

[Parariddle]

Thanks Rookie just tried that but still getting any #Error on the true statement

So I am at a loss of what it could be.

 

[MSAccessRookie]

Thanks Rookie just tried that but still getting any #Error on the true statement

So I am at a loss of what it could be.

Forgot to ask WHICH true statement (there are three of them)

 

[MSAccessRookie]

Thanks Rookie just tried that but still getting any #Error on the true statement

So I am at a loss of what it could be.

I wonder if the precedence of the operators in your conditionals is confusing Access. Try making them look like the example below:

[Completed] =True And [Frequency] ="Daily"
 
Becomes
 
(([Completed] =True) And ([Frequency] ="Daily"))

 

[Parariddle]

Hi Rookie, thanks again !

I tried that and I am now getting blank cells so maybe I have bamboozled access !

 

[boblarson]

I think after 13 posts and it still not resolved that it would be very useful to just have you upload the database.

 

[MSAccessRookie]

Hi Rookie, thanks again !

I tried that and I am now getting blank cells so maybe I have bamboozled access !

If that means you are good, then great! If that means you still have problems, then post the most current form of your query.

Note: to keep the formatting, you can surround text with [ code ] and [ / code ] (removing the spaces). Or you can just click on the # sign at the end of the format bar

 

[MSAccessRookie]

I think after 13 posts and it still not resolved that it would be very useful to just have you upload the database.

I agree. If your situation allows you to do that.

 

[namliam]

I thought the 4 ) at the end would close everything (BO Trate)

It closes everything, I dont know how BO works... but in most languages functions expect a number of parameters to enter it and leave it....

Dateadd(1,2,3)

That is the complete function, without the closing bracket you dont end the function.

A false statement IS NOT required... Its advicable, but NOT required

I would make it #1 readable, #2 logical... You are repeating the same thing atleast 3 times

NextDue2:
IIF( [Completed] =True 
  , iif ([Frequency] ="Daily"
    , DateAdd("d",1, [Next Due Date] )
    , IIF( [Frequency] = "Weekly"
      ,DateAdd("w",1, [Next Due Date]) 
      ,IIF( [Frequency] = "Monthly"
        ,DateAdd("M",1, [Next Due Date] )
        , <something if not Monthly>
      )
    )
  , <something if not completed>
  )

I am sure a "heavy Business Objects" must be used to making things readable, logical and maintainable....
*ok sorry that maybe a bit harsch* but still... when something is not quite working right... the very first thing I do is make it readable! Including indenting it like above.

 

[Parariddle]

Ok so a walk home certainly clears the mind !

Thanks for the thoughts namliam and your right but the query was correct. The problem lied in that frequency was a lookup so it was a number not the text so I have rectified that and it now works.

Thanks to everyone who helped I appreciate all your time and effort and I will hit the scales !