IIF function equation problem

[MattCrane]

Hi all im new to both this forum and Access so if this should be posted in a different part of the forum please tell me and I will.

I'm working on creating an electronic library system on access as part of a project and im trying to create an iff function which will look at the date when the book is taken out and then add a month to create a return date. However if the day falls on a saturday or sunday it needs to put the due back date as the monday. This is my code so far but I keep getting an error stating 'the expression you entered is missing a closing parenthesis, bracket (]), or vertical bar'

current code- =iif(weekday(date())=1,Dateadd,("M",1,date())+1,iif(weekday(date())=7,dateadd,("M",1,date()+2,dateadd,("M",1,date())))

As i said im new to access (only been using a couple of months so any help would be very helpful.

 

[lespaul]

iif(weekday(date())=1, Dateadd("M",1,date()) + 1,
iif(weekday(date())=7, dateadd("M",1,date()) + 2,
dateadd("M",1,date())))

Try that

Leslie

 

[KornGeek]

First,
Simply counting the "(" and ")" characters, you are in fact missing a closing parenthesis.

Second,
The syntax of this statement looks off to me. You have a comma between the dateadd function and the parameters you are sending it. I'm not 100% certain, but that looks incorrect to me.

 

[KornGeek]

After further analysis, you seem to be missing the ")" that should appear before the "+2" in your code.

Leslie's suggestion looks like it should work.

 

[grnzbra]

Perhaps I'm missing something here, but it looks like everyone is trying to work with what day of the week today is rather than what day of the week one month from today is. It seems like the check should be:

iif(weekday(Dateadd("M",1,date()))) = 1, etc etc

 

[lespaul]

Matt may need to adjust what is being calculated, but what he asked for has been answered.

He was missing a paren, and we fixed that.

which will look at the date when the book is taken out and then add a month to create a return date

Now if I was designing the database, I wouldn't store this date at all (storing a calculated field breaks 3NF rules)! If the book is renewed, then this field in the table will need to be updated. Personally, I would just figure the return date in a query whenever I needed it.



Leslie

 

[MattCrane]

Thanks for the replies the equation now works 'sort of' atleast enough for me to play with. I may attempt to do it in a query as lespaul suggested as the system will need to be able to renew books. Ive got to try and design and make the rest of this system over the weekend and with my limited knowledge ill probably be back on this forum with more questions before too long. Thanks for all the replies