PS2007 use of 'Like' in formula

[Andrew Dover]

I have a PS2003 instance that I am converting to PS2007. Is seems that the
use of 'Like' which worked fine previously and also works in local field
calculations is not supported in Enterprise Custom fields. FYI the
calculation is:

IIf([Title] Like "*20??Q1*",DateSerial(Year([Project
Start]),3,31),IIf([Title] Like "*20??Q2*",DateSerial(Year([Project
Start]),6,30),IIf([Title] Like "*20??Q3*",DateSerial(Year([Project
Start]),9,30),IIf([Title] Like "*20??Q4*",DateSerial(Year([Project
Start]),12,31),IIf([Title] Like
"*Pre?20??*",DateSerial(Mid([Title],(InStr([Title],"Pre")+4),4),12,31),IIf([Title] Like "*20?? *",DateSerial(Year([Project Start]),12,31),"01/01/00"))))))

Which looks at the title of a plan conforming to a define standard,
identifies within the title the period the plan is covering (if [Title] were
'My Plan 2008Q1 Development' the formula would (or should!) deduce the end of
the plan period as 31-Mar-2008).

Anyone come across this or have a simple resolution?

Thanks

 

[Ben Mc]

Hi Andrew,

At a guess, it would seem that 2003 (and/or local) fields are using VBA
syntax, where as it may be the case that 2007 enterprise uses "Excel-esque"
definitions (which sharepoint fields use). I say this as you are using "IIF"
(inline if) not "IF"

So, you may want to try using that syntax. A while back I found a little
known manual that defines the commands and syntax. It saved my *rse before.

You may find it on your server or own box here: C:\Program Files\Microsoft
Office\Office12\1033\STSLIST.CHM

Cheers,
Ben

 

[Andrew Dover]

Hi Ben Mc,

Seems I may have corrupted the db somehow as all Project Level Enterprise
calcs that I now add are resulting in #ERROR (and I've tried the same calc in
another instance and it works). I will however seek out STSLIST.CHM.

Regards,

Andrew

Hi Andrew,

At a guess, it would seem that 2003 (and/or local) fields are using VBA
syntax, where as it may be the case that 2007 enterprise uses "Excel-esque"
definitions (which sharepoint fields use). I say this as you are using "IIF"
(inline if) not "IF"

So, you may want to try using that syntax. A while back I found a little
known manual that defines the commands and syntax. It saved my *rse before.

You may find it on your server or own box here: C:\Program Files\Microsoft
Office\Office12\1033\STSLIST.CHM

Cheers,
Ben

I have a PS2003 instance that I am converting to PS2007. Is seems that the
use of 'Like' which worked fine previously and also works in local field
calculations is not supported in Enterprise Custom fields. FYI the
calculation is:

IIf([Title] Like "*20??Q1*",DateSerial(Year([Project
Start]),3,31),IIf([Title] Like "*20??Q2*",DateSerial(Year([Project
Start]),6,30),IIf([Title] Like "*20??Q3*",DateSerial(Year([Project
Start]),9,30),IIf([Title] Like "*20??Q4*",DateSerial(Year([Project
Start]),12,31),IIf([Title] Like
"*Pre?20??*",DateSerial(Mid([Title],(InStr([Title],"Pre")+4),4),12,31),IIf([Title] Like "*20?? *",DateSerial(Year([Project Start]),12,31),"01/01/00"))))))

Which looks at the title of a plan conforming to a define standard,
identifies within the title the period the plan is covering (if [Title] were
'My Plan 2008Q1 Development' the formula would (or should!) deduce the end of
the plan period as 31-Mar-2008).

Anyone come across this or have a simple resolution?

Thanks