Expressions In Access forms

[enigma54]

Hi,

I have been stuck messing with IIf expressions for the past week with no success. Wonder if anyone can help me out!

I have been given the onerous task of converting many of the spreadsheets we hold here at work to a database format (Access 2007). Therein I have encountered a problem!

I have a spreadsheet that currently records outstanding items. There is a formula to calculate the priority in which the items should be handled:

=IF(K5="O",IF((I5-TODAY())>5,"Priority 2","Priority 1"),"")

K5 Is the status of the item (O=Open, C=Closed, H=On Hold)
I5 Is the date the item is due/needed

The above formula has the following result:

Any action within 7 days of the due date = Priority 1
Any action with more than 7 days to the due date = Priority 2
Actions on Hold = H
Actions Closed = C

I would like to produce the same result in a form (autogenerates once all the required information is available) but unfortunately I do not know how. I would very much appreciate any help on this.

Thanks a lot

Dave

 

[DCrake]

=IIF([Status]="O",IIF(([DateField]-Date())>5,"Priority 2","Priority 1"),"")

Remember to use your field names

 

[enigma54]

Wow! Thanks very much for the speedy reply

I shall try it at once

 

[enigma54]

Hi again,

Everything seems fine (syntax etc). I opened my form, right clicked on the 'Priority' Field and clicked Build Event> Expression

Pasted the following command in (updated with my table field values)

=IIf([StatusOfQuery]="Open",IIf(([DateDue]-Date())>5,"Priority 2","Priority 1"),"")

Then saved the form and started entering the data.

I may be being totally stupid but the expression doesnt seem to want to trigger. Is there anything maybe you could suggest I check?

I have included the database if you need to see...

Thanks a lot

Dave

Edit:

Just noticed that you have Access 2003. Apologies, I have re-uploaded the attachment in .mdb format

 

[DCrake]

Take a look at this

 

[enigma54]

Ahhh,

Wonderful

It now works like a charm with just the simple addition of a macro! I have learnt a lot from this. Thanks very much for your efforts.