Iif Function

[mari_hitz]

Hi everybody!

I have an issue with a query I would like to perform. I have a table with the field 'Hire Date'. I have performed a datediff between the hire date and today. This returns me how many years the person is in the company.
Depending on the amount of time, the person will have different amounts of vacations day. For example:
- less than 5 years: 15 days
-Between 5 and 10 years: 21 days
-More than 10 years: 30 days.

So I would like to perform an Iif function to return me how many days of vacations has the person depending on the results of the datediff. I have the following:

IIf([DateDiff]< 5, '15', 21')

I tried to keep it simple to see if I could make it work, but it didn't. It says I have entered invalid syntax.

Do you know if I can make it return numbers instead of words and why is it wrong? I am copying the format I have seen in the internet.

Thanks!

 

[pbaldy]

Not a great idea to name the field the same as the function, but you missed the single quote before 21.

 

[MSAccessRookie]

Hi everybody!

I have an issue with a query I would like to perform. I have a table with the field 'Hire Date'. I have performed a datediff between the hire date and today. This returns me how many years the person is in the company.
Depending on the amount of time, the person will have different amounts of vacations day. For example:
- less than 5 years: 15 days
-Between 5 and 10 years: 21 days
-More than 10 years: 30 days.

So I would like to perform an Iif function to return me how many days of vacations has the person depending on the results of the datediff. I have the following:

IIf([DateDiff]< 5, '15', 21')

I tried to keep it simple to see if I could make it work, but it didn't. It says I have entered invalid syntax.

Do you know if I can make it return numbers instead of words and why is it wrong? I am copying the format I have seen in the internet.

Thanks!

• Why is it wrong?
  • As pbaldy already pointed out, a String has a Quote on both sides and you left a Quote off.
• Do you know if I can make it return numbers instead of words?
  • Removing the Quotes will return Numeric values.

 

[mari_hitz]

Thanks everyone! I have managed to perform the function just fine.

I would like to add a second question.
I have a table where people can upload days on which they have worked overtime. Generally on public holidays some people work, so I have created this table so people can add the days they have worked. It is a table that has "First Name", "Last Name", "Date Taken", this last one has a date picker. I have created a query and I have added a field to it with a "Sum" function of the "Date Taken" field, but it returns me a odd value. I would like to sum all the days the person has worked in added it to the vacations days that is allowed to take according to the experience.
Is this possible?

 

[pbaldy]

Well, you can't really sum dates. If I asked what July 26th plus July 27th was, you'd say 2, but you're really adding days, not dates, in your head. Try counting instead. If you want to sum, you have to sum an interval of time, like days, hours, etc.