nested iifs bringin the dot error

[splreece]

morning all,

could i cast your eyes over the below as I am sure its a quick fix but I can't find the issue in the structure.


CalcTargetDate: IIf( [cmb_GR_servlevel] ="Standard", [dte_datePNGReceived] +10,IIf( [cmb_GR_servlevel] ="Premium", [dte_datePNGReceived] +5,IIf( [cmb_GR_servlevel] ="FastTrack", [dte_datePNGReceived] +2))


I am imply asking that depending on the CMB_GR_SERVLEVEL, I need to calculate the received date +"x".


Any thoughts as I am getting an invalid dot or parenthesis error.

 

[stopher]

Just add a round close bracket on the end ")"

 

[splreece]

thanks for the quick reply... ( I tried it, but same error).

Would It make a difference I I am putting the formula in a calculated field (not an onclick button)

 

[splreece]

I limited to the iif statement to just 1 iif and still coming up with the same error

CalcTargetDate: IIf([cmb_GR_servlevel]="Standard", [dte_datePNGReceived] +10)

 

[plog]

Do you have any Null values for dte_datePNGReceived? Is that field an actual date/time datatype?

 

[Minty]

I limited to the iif statement to just 1 iif and still coming up with the same error

CalcTargetDate: IIf([cmb_GR_servlevel]="Standard", [dte_datePNGReceived] +10)

This doesn't have the right number of arguments. The syntax is

IIf(Logical Expression , Result if True , Result if False)

You are missing the Purple bit. Apply the same formatting to your original statement and see what is missing.

 

[CJ_London]

code would be easier to read like this

CalcTargetDate: [dte_datePNGReceived]+IIf( [cmb_GR_servlevel] ="Standard",10,IIf( [cmb_GR_servlevel] ="Premium", 5,IIf( [cmb_GR_servlevel] ="FastTrack", 2)))

 

[splreece]

Do you have any Null values for dte_datePNGReceived? Is that field an actual date/time datatype?

Thanks again.

The field is date/time type and I only have 2 entries in the database so the date/time I know are filled in for both.

 

[CJ_London]

Also, I presume this is in a query and not the controlsource to a form control? Just concerned about your comment

Would It make a difference I I am putting the formula in a calculated field (not an onclick button)

What exactly is your error description?

 

[Minty]

@CJ - I didn't think the false part was optional in an IIf statement - the last statement would return an error, as the (missing) false part is always evaluated regardless.

 

[splreece]

Also, I presume this is in a query and not the controlsource to a form control? Just concerned about your comment



What exactly is your error description?

Ahh,

It is in the expression of the table field (field is calculated).

Am I going about it the wrong way. Should I be putting it in an onclick or onchange event of the dte_datePNGReceived to change the dte_targetPNGDate field.

 

[Minty]

Calculated table fields are a new feature in Access (2010 onward I believe) , and as such most of us ancient old developers haven't used them.

I've just played with it on small example table and a simple IIf statement works. It is certainly better to have it permanently calculated in the table than relying on events that could be missed to keep it accurate. Most of us would simply calculate it on the fly when required, of possibly create a function for it if it is used in lots of places, and was complicated.

 

[CJ_London]

@CJ - I didn't think the false part was optional in an IIf statement - the last statement would return an error, as the (missing) false part is always evaluated regardless.

I agree - if this is the calculation for a calculated field then a) cmb_GR_servlevel need to be a field in the table and b) needs to be populated.

@Spreece

It is in the expression of the table field (field is calculated).

so this is the expression for a calculated field in a table? Or are you trying to populate a calculated field in a table? via a query? via a form?or what? please explain more clearly what you are trying to do.

It is generally a bad idea to use calculated fields (for one thing they are not indexed, so any criteria based on the calculated value will be slooow) or to store calculated values in tables - just use a query when required to calculate the value

 

[The_Doc_Man]

Given the number of nests, look at the SWITCH function as an alternative to that many IIF calls in a nest.

 

[gemma-the-husky]

try with a simpler function (one date), then keep adding extra arguments.