Expression problem

[aneats]

this is my problem...
i havea form, with 4 fields (there are more than 4 but that's beside the point). [Guide Type ID], [GuidDateReceived], [txtRetdBySol] and [TargetDate]

I am trying to populate [TargetDate] and have created the following expression, which doesn't work:

=IIf([txtRetdBySol] Is Null,[GuidDateReceived]+35,IIf([Guide Type ID]="DGMAm",[GuidDateReceived]+35,IIf([Guide Type ID]="DGMAm",[txtRetdBySol]+35,IIf([txtRetdBySol] Is Null,[GuidDateReceived]+14,IIf([Guide Type ID]="DMGM",[GuidDateReceived]+14,IIf([Guide Type ID]="DMGM",[txtRetdBySol]+14,IIf([txtRetdBySol] Is Null,[GuidDateReceived]+14,IIf([Guide Type ID]="SAOLET",[GuidDateReceived]+14,IIf([Guide Type ID]="SAOLET",[txtRetdBySol]+14)))))))))

...So, dependant on the value in [Guide Type ID], a number of days is added to [GuidDateReceived] to give a value in [TargetDate], UNLESS there's a value in [txtRetdBySol], in which case the days are added to this date to give [TargetDate].
it's not working, and i don't know what's going wrong.
help.

 

[Brianwarnock]

I think it should be isnull(etc)

Brian

 

[aneats]

Brian, it's not. isnull throws back an error. the spelling i've used is correct.

 

[Brianwarnock]

Perhaps I wasn't clear

=IIf(IsNull[txtRetdBySol],[GuidDateReceived]+35, etc

To test for null fields you use the function IsNull

Brian

 

[aneats]

I tried this, it doesn't work. Should i use 'And' anywhere in this? i originally used it, but someone told me to take it out????


=IIf(IsNull [txtRetdBySol],[GuidDateReceived]+35,
IIf([Guide Type ID]="DGMAm",[GuidDateReceived]+35,
IIf([Guide Type ID]="DGMAm",[txtRetdBySol]+35,
IIf(IsNull [txtRetdBySol],[GuidDateReceived]+14,
IIf([Guide Type ID]="DMGM",[GuidDateReceived]+14,
IIf([Guide Type ID]="DMGM",[txtRetdBySol]+14,
IIf (IsNull [txtRetdBySol],[GuidDateReceived]+14,
IIf([Guide Type ID]="SAOLET",[GuidDateReceived]+14,
IIf([Guide Type ID]="SAOLET",[txtRetdBySol]+14)))))))))

 

[Brianwarnock]

I tried this, it doesn't work. Should i use 'And' anywhere in this? i originally used it, but someone told me to take it out????

I think AND is an Excel function.

Going by what you said in the 1st post I would have thought that the following is what you want. If it's not then I don't understand what you want.

brian


=IIf(IsNull[txtRetdBySol],[GuidDateReceived]+35,
IIf([Guide Type ID]="DGMAm",[GuidDateReceived]+35,
IIf([Guide Type ID]="DMGM",[GuidDateReceived]+14,
IIf([Guide Type ID]="SAOLET",[txtRetdBySol]+14))))

 

[Brianwarnock]

Actually I've just realised that you are adding days to dates, I'm having a slow day today , you will need to use the DateAdd function eg =IIf(IsNull[txtRetdBySol],Dateadd("d",35,[GuidDateReceived]),etc

 

[aneats]

I tried this:

=IIf(IsNull[txtRetdBySol], Dateadd("d",35,[GuidDateReceived]),
IIf([Guide Type ID]="DGMAm", Dateadd("d",35,[GuidDateReceived]),
IIf([Guide Type ID]="DMGM",Dateadd("d",14,[GuidDateReceived]),
IIf([Guide Type ID]="SAOLET", Dateadd("d",14,[GuidDateReceived])))))

and when i try to run it, it says the following:

'the expression you added contains an invalid syntax'

What i am trying to say is, if there IS a date in [txtRetdBySol], and the [Guide Type ID] value is 'DGMAm', add 35 days to this date. But if there's no date in the field [txtRetdBySol], revert to the [GuidDateReceived], adding 35 days to that.
For [Guide Type ID] values of DMGM or SAOLET, add 14 days instead of 35.

 

[Mile-O]

The IsNull() is a function and, as such, you need to include the argument you put into it within these brackets.

ie. IsNull([txtRetdBySol])

Also, alse these IIfs are too much. Consider using the Switch() function or creating a VBA function you can call which would be less of a time waster than the IIF() function.

 

[Mile-O]

So, for example, you could use the following:

IIf(IsNull([txtRetdBySol]), [GuidDateReceived] + 35, Switch([Guide Type ID] = "DGMAm", [GuidDateReceived] + 35, [Guide Type ID] = "DMGM", [GuidDateReceived] + 14, [Guide Type ID] = "SAOLET", [GuidDateReceived] + 14))

You can just use number instead of the DateAdd() function as days are represented as a whole number. So 1 equals one day. It's faster than using DateAdd() too although you'll need to resort to DateAdd() for all other date calculations.

 

[Brianwarnock]

Hmm, I always forget about the SWITCH function, and you learn something everyday ie no need to use DateAdd for days. In addtion to the error with the IsNull, sorry about the typo, I think a further syntax error may have been due to no final FALSE condition.

Brian

 

[aneats]

Thanks, but it's still not quite working...
if [txtRetdBySol] is null, add 35 to [GuidDateReceived] IF [Guide Type ID] = DGMAm, and if [txtRetdBySol] HAS a value, add 35 to [txtRetdBySol].
this works fine, but what doesn't work is if [Guide Type ID] = SAOLET or DMGM, the same rule needs to be applied, except instead of 35, 14 is added.
What is happening at the minute is that 35 is being added, regardless of whether it's DGMAm, SAOLET, or DMGM.
it's as if the red part is always true, which it isn't

IIf(IsNull([txtRetdBySol]), [GuidDateReceived] + 35, Switch([Guide Type ID] = "DGMAm", [GuidDateReceived] + 35, [Guide Type ID] = "DMGM", [GuidDateReceived] + 14, [Guide Type ID] = "SAOLET", [GuidDateReceived] + 14))

 

[Mile-O]

Post an example database that recreates this error, please.

 

[aneats]

db attached

 

[Mile-O]

Reattach the tables as it's currently linking to tables on your network.

 

[aneats]

Sorry about that

 

[aneats]

Problem solved.
Thanks for all your help.