#Error, nested IIf

[ajak]

Hi

Brand new to Access 2003 and stumbling at the first hurdle. I want a data base which will inform the user when a new task needs to be carried out. I have a column headed "Last Use" (date format, manually entered into table), a column headed, "Frequency" (text format, manually entered into table) and I need a column which will calculate the column Next Use (date format, to be calculated). Entries under "Frequency" should only be "Monthly" or "weekly" but if neither I would like a warning. I figure if I convert the text to a number and then add this to my "Last Use" field it would give me my "Next Use"

I have set a query up "Next Use: IIf([Frequency]="monthly",31,(IIf([Frequency]="weekly",7,"input error"))). But I get #Error if Frequency is anything other than Monthly or Weekly.

Appreciate any assistance.

ajak

[NeoPa]

Ajak, You may want to check out Normalisation and Table structures. It explains why storing calculated fields is rarely a good idea.

Instead, consider calculating them when required. In a query generally.

However you choose to do it, consider using the DateAdd() function. It's very flexible and should do what you need.

[ajak]

Ajak, You may want to check out Normalisation and Table structures. It explains why storing calculated fields is rarely a good idea.

Instead, consider calculating them when required. In a query generally.

However you choose to do it, consider using the DateAdd() function. It's very flexible and should do what you need.

Thank you for your reply NeoPa. It looks like the DateAdd() is what I was trying to achieve but, if my limited understanding is correct, the Frequency needs to be turned from a text to a number to allow the DatAdd() to work and this is where I am having my problem. I will check out the suggested Normalisation and Table structures link in greater detail.

I appreciate you taking the time to post a reply.

ajak

[NeoPa]

I certainly would check it out, but it won't help you with use of the function.

If you have any questions on that you can post them here and I'll help where I can.

[ajak]

I certainly would check it out, but it won't help you with use of the function.

If you have any questions on that you can post them here and I'll help where I can.

Thanks NeoPa. I appreciate the offer. Think I will have to go back to the drawing board.

Kind regards

ajak

[NeoPa]

Ajak, It sounds to me (I may be mistaken of course) that you're almost there with the DateAdd() function. I would check that out in Help and I doubt you'll be too far short. Any clarifications required can be posted in here.