Calculated Field with conditions

[ppieroudis]

Hello,
I would like to add a formula on the Expiry Date field that will automatically add 1 or 3 years on the Finish Date field depending on the Programme ID.

 

[MarkK]

Hi there.
So what is stopping you from doing that? Where are you stuck?
Mark

 

[ppieroudis]

Hello again,
I tried a few nested iif statements but without any results. It said that there was a syntax error.

 

[MarkK]

What do you think we need to see in order to troubleshoot your syntax error?

 

[ppieroudis]

Sorry for late reply!
This is the formula i used. Is there an alternative to the calculated field?
IIf([PROGRAMME]="ΠΡΩΤΕΣ ΒΟΗΘΕΙΕΣ ΣΤΗΝ ΕΡΓΑΣΙΑ - ΕΠΕΙΓΟΝΤΑ ΠΕΡΙΣΤΑΤΙΚΑ" Or [PROGRAMME]="ΠΡΩΤΕΣ ΒΟΗΘΕΙΕΣ ΣΤΗΝ ΕΡΓΑΣΙΑ",[ISSUE DATE]+1094,IIf([PROGRAMME]="ΚΑΡΠΑ ΚΑΙ ΧΡΗΣΗ ΑΥΤΟΜΑΤΟΥ ΕΞΩΤΕΡΙΚΟΥ ΑΠΙΝΙΔΩΤΗ" Or [PROGRAMME]="ANNUAL REFRESH COURSE" Or [PROGRAMME]="ΕΤΗΣΙΑ ΑΝΑΝΕΩΣΗ ΓΝΩΣΗΣ",[ISSUE DATE]+364,Null))

 

[Minty]

There doesn't appear to be anything obviously wrong with your syntax, is [Issue Date] definitely a date field?

It might be easier if your programme field was a look up to a text descriptor rather than those horrible long strings, but there isn't an actual problem, unless it's the Greek characters that are causing an issue.

 

[ppieroudis]

The programme field is linked with another table and there is a drop down list to avoid mistakes.
Is there an alternative to this calculated field?

 

[ashleedawg]

You say "linked with another table" -- is this expression in a table/query/form?

 

[ppieroudis]

I am sorry if i am using wrong words. I am new in Access. What i mean with link is the the field programme id is linked with relationship with the table programmes. So the programme id is a drop down list and shows all the records in programmes table

 

[Minty]

I think then your [Programme] is an ID not a text field. Did you set it up as a lookup field in your main table - if you did they are a real pain to work with have a read here http://access.mvps.org/access/lookupfields.htm

 

[ashleedawg]

I think then your [Programme] is an ID not a text field.

...or [ISSUE DATE]A quick way to see if that's the problem is, repmove the whole expression and replace it with only [Programme], and run the query. After that try running it with only [ISSUE DATE]+364 and see if that gives an error.

And, perhaps a screen shot or two would help, showind both this expression, and the table.

Also, I've had trouble using spaces in a field name even with the [ ] brackets - it will work fine for some thing but not others.

 

[ppieroudis]

First I would like to thank everyone for helping!

I will take some screenshots and show you what the problem is..

Thank you again

 

[ppieroudis]

IIf([Programme ID]='EFAW-GR' Or [Programme ID]='EFAW-ENG' Or [Programme ID]='EPFA-GR' Or [Programme ID]='EPFA-ENG' Or [Programme ID]='FAW-ENG' Or [Programme ID]='FAW-GR' Or [Programme ID]='MEDTEC-ENG' Or [Programme ID]='MEDTEC-GR' Or [Programme ID]='MG-ENG' Or [Programme ID]='MG-GR' Or [Programme ID]='PFA-GR' Or [Programme ID]='PFA-ENG' Or [Programme ID]='FAWARC-GR' Or [Programme ID]='FAWARC-ENG',[Date Finish]+1095,IIf([Programme ID]='ARC-GR' Or [Programme ID]='ARC-ENG' Or [Programme ID]='CPR-ENG' Or [Programme ID]='CPR-GR',[Date Finish]+364,Null))

After a loooot of hours i came to this !! it finally works

Thanks everyone!

 

[The_Doc_Man]

After looking at this, I see a complex IIF that MIGHT be possible to simplify.

If the table that defines your [Programme ID] can be modified to add a field, add a numeric field to that table that TELLS you how many days to add for that particular program. Then don't use an IIF, just pick up that number of days to add directly.

This further adds the option that you can even add a programme later that would require only 2 years - or that needed 4 years. Or whatever. And, since you are doing it in DAYS, maybe even fractional years.

 

[ppieroudis]

Dear Doc_Man,

I already have this field in the Programmes table. I just don't know how to write the correct formula that you are suggesting...

 

[The_Doc_Man]

I'll try to illustrate using names that I make up.

Let's say you have a [StartDate] as a DATE, [EndDate] as a DATE, and [MaxDays] as a LONG. Normally, you would NOT store all three of these fields together because having both an EndDate and a MaxDays would be redundant. You could have either of them and compute the other in a query. BUT to answer your direct question, ...

UPDATE [EndDate] SET [EndDate] = DateAdd("d", [MaxDays], [StartDate]) WHERE [EndDate] = 0 ;

Then if your newly created programme entry didn't have a date for the end date, you compute one.

If you preferred to have only the start date and number of days and wanted to compute the end date via query,

SELECT [Programme ID], [StartDate], DateAdd("d", [MaxDays], [StartDate]) AS EndDate
FROM ProgrammeTable ;

This would then compute the EndDate for each record as long as you had a start date and that maximum days field that tells you how long the program could run.