=iff statement using date parameters

[patchesohouli]

Hi,



I am trying to create a formula in an existing query. If the date value referenced (lets say 07/13/2018) is less than or equal to today's Date() the system needs to perform the calculation of a specific multiplier being applied to a field in the table.

So for example, here is the basis of what I am trying to accomplish minus the important syntax:
=iff(Today’s Date <= '07/13/2018’, [PP Deduction] * 13

else
=iff(Today’s Date <= ‘07/27/2018’,[PP Deduction] * 12
And then continue this on for 12 other date values that have a different multiplier value.

Is this possible?

Thanks

[ranman256]

you can put these dates into a table:
TargDate, Multi
07/13/2018, 13
07/27/2018, 12
etc

then your query would use it to pull the Multiplier and do your math.
Is this what you need or do the dates change too much to do this?

[Gicu]

Try =Iif([Date1]<=Date,[PP_Deduction]*13,iif([Date2]<=Date,[PP_Deduction]*12,IIf([Date3]<=Date,[PP_Deduction]*11,IIF([Date4]<=Date,[PP_Deduction]*10,........))))))))))))))

Cheers,
Vlad

[patchesohouli]

Thanks for your assistance. Vlad - I tried the formula, and keep getting a 'enter parameter value' when running the formula on the date 7/13/2018. Is there a specific date function I need to do?

[patchesohouli]

Ranman - that was actually the first thing I tired doing, making a seperate table with the date and multiplier in columns. I could not figure out how to make the query reference those values in the table however. Any suggestions? Thanks so much!

[Gicu]

Can you post what you tried?
Vlad

[pbaldy]

I would go with ranman's solution, but have both start and end dates in the table. Then you can just join to that table in your query (non-equi join). Vlad's solution could work, but I can't recall what the nesting limit it and that may hit it. The table would be more flexible anyway.

[patchesohouli]

Hi Vlad,

I am trying to use the Iif statement with one multiplier just to see if that functions, trying the following:

=Iif([07/13/2018]<=Date,[PP_Deduction]*13,"Test")

Thanks

[pbaldy]

Vlad looks to be offline. The syntax would be:

=Iif(#07/13/2018# <= Date(),[PP_Deduction]*13,"Test")

[Gicu]

Thanks Paul!

patchesohouli, my original formula assumed you would have date fields in your table(s) that you would like to compare with today's date (returned by the Date() function). That is why I used the field delimiters (the square brakets). If you want to compare hard-coded dates simply enclose them in # as shown by Paul above. I believe the max number of nested IIf statements is around 14 so it should work for you, if not you can use the Switch function(https://www.techonthenet.com/access/...ced/switch.php) or even better a custom VBA function. If you provide us with more info regarding your specific issue we would be in a much better position to help. Are the dates static, are they record specific, etc.

Cheers,
Vlad

[patchesohouli]

Thank you everyone for your quick replies! This was really helpful for my understanding. The =Iif statement is working perfectly, though I am going to look at the 'Switch Function' to try and understand that method as well.

Thanks!

[patchesohouli]

I am trying to adjust part of the formula for another application to look BETWEEN a range of numbers. I tried the following formula and many iterations of syntax, but am having difficulty getting it to work:

=Iif(Date()>=#07/13/2018# and <=#07/29/2018#),[Flex Med/PP]*13,"Test")

Does anyone know what is incorrect in this statement? Thanks

[pbaldy]

I would still recommend a table, but:

=Iif(Date()>=#07/13/2018# and Date() <=#07/29/2018#),[Flex Med/PP]*13,"Test")

[patchesohouli]

I am getting a 'The expression you entered has a function containing the wrong number of arguments' prompt when I enter the above code. Any ideas? Thanks!

[pbaldy]

I didn't notice you had an extra parentheses in there. Try:

=Iif(Date()>=#07/13/2018# and Date() <=#07/29/2018#,[Flex Med/PP]*13,"Test")