Jet Expression syntax

[Dean C]

Where are acceptable expressions for MS Project custom fields defined? Mike
Glen touched on the subject a few years ago, but I can't find any details.

I know that custom fields in Project use the Jet expression format, but have
been unable to find a list of acceptable terms. For example, iif([Actual
Start]="NA","true","false"), results in #ERROR, but so does
iif(iserror([ACT([Actual Start]="NA"),"true","false"). There is a value of
"NA" in the field, but Jet Expression doesn't like it.

 

[Mike Glen]

Hi Dean,

I don't know if there is a consolidated list - you can try searching Help.
However, if you go to
Tools/Customize/Fields... /Custom Fields tab/Formula.. you will see in the
dialog the ability to insert Fields and Functions. If you use these to
build your formulae, you should get the right syntax.

You might like to have a look at my series on Microsoft Project in the
TechTrax ezine, particularly #13 & 15 on customizing fields, at this site:
http://tinyurl.com/2xbhc or this:
http://pubs.logicalexpressions.com/Pub0009/LPMFrame.asp?CMD=ArticleSearch&AUTH=23
(Perhaps you'd care to rate the articles before leaving the site,
Thanks.)

Mike Glen
MS Project MVP


Mike Glen
MS Project MVP
See http://tinyurl.com/2xbhc for Project Tutorials

 

[JulieS]

Hi Dean C,

For the NA issue see MVP Jack Dahlgren's page on calculated fields at:

http://masamiki.com/project/customfieldFAQ.htm

I hope this helps. Let us know how you get along.

Julie

 

[Dean C]

JulieS,
Thanks, the value Jack used is incorrect for a date value of "NA", but it
gave me a starting point for a number. The correct number is 4294967295,
which is (2 to the 32nd power - 1). Jack forgot to subtract one, but did
provide the correct formula.

As I recall, this is the third thing I've learned in this news group and I
learned at least one of the other two from you also. I gave you full credit
even though my real quest was for good Jet expression references.

Access also uses Jet expression and includes an Expression Builder, so I'll
resort to that. I haven't played with databases (other than MSP) since dBase
III, so this should be interesting.

Hi Dean C,

For the NA issue see MVP Jack Dahlgren's page on calculated fields at:

http://masamiki.com/project/customfieldFAQ.htm

I hope this helps. Let us know how you get along.

Julie

Where are acceptable expressions for MS Project custom fields defined?
Mike
Glen touched on the subject a few years ago, but I can't find any
details.

I know that custom fields in Project use the Jet expression format,
but have
been unable to find a list of acceptable terms. For example,
iif([Actual
Start]="NA","true","false"), results in #ERROR, but so does
iif(iserror([ACT([Actual Start]="NA"),"true","false"). There is a
value of
"NA" in the field, but Jet Expression doesn't like it.

 

[JulieS]

Hi Dean C,

You're welcome and thanks for the feedback. Good luck with your quest.

Julie

JulieS,
Thanks, the value Jack used is incorrect for a date value of "NA", but
it
gave me a starting point for a number. The correct number is
4294967295,
which is (2 to the 32nd power - 1). Jack forgot to subtract one, but
did
provide the correct formula.

As I recall, this is the third thing I've learned in this news group
and I
learned at least one of the other two from you also. I gave you full
credit
even though my real quest was for good Jet expression references.

Access also uses Jet expression and includes an Expression Builder, so
I'll
resort to that. I haven't played with databases (other than MSP) since
dBase
III, so this should be interesting.

Hi Dean C,

For the NA issue see MVP Jack Dahlgren's page on calculated fields
at:

http://masamiki.com/project/customfieldFAQ.htm

I hope this helps. Let us know how you get along.

Julie

Where are acceptable expressions for MS Project custom fields
defined?
Mike
Glen touched on the subject a few years ago, but I can't find any
details.

I know that custom fields in Project use the Jet expression format,
but have
been unable to find a list of acceptable terms. For example,
iif([Actual
Start]="NA","true","false"), results in #ERROR, but so does
iif(iserror([ACT([Actual Start]="NA"),"true","false"). There is a
value of
"NA" in the field, but Jet Expression doesn't like it.

 

[Dean C]

I came across this when searching for my posts. I found what I was looking
for quite awhile ago. The answer is to go to help and search for Project
Functions. There is an error in the help file though. In DateAdd, "interval",
is listed as "Integer" when in fact it can only be "s", "m", "h", "d",
"w","M", "q", "y".

Hi Dean C,

You're welcome and thanks for the feedback. Good luck with your quest.

Julie

JulieS,
Thanks, the value Jack used is incorrect for a date value of "NA", but
it
gave me a starting point for a number. The correct number is
4294967295,
which is (2 to the 32nd power - 1). Jack forgot to subtract one, but
did
provide the correct formula.

As I recall, this is the third thing I've learned in this news group
and I
learned at least one of the other two from you also. I gave you full
credit
even though my real quest was for good Jet expression references.

Access also uses Jet expression and includes an Expression Builder, so
I'll
resort to that. I haven't played with databases (other than MSP) since
dBase
III, so this should be interesting.

Hi Dean C,

For the NA issue see MVP Jack Dahlgren's page on calculated fields
at:

http://masamiki.com/project/customfieldFAQ.htm

I hope this helps. Let us know how you get along.

Julie
Where are acceptable expressions for MS Project custom fields
defined?
Mike
Glen touched on the subject a few years ago, but I can't find any
details.

I know that custom fields in Project use the Jet expression format,
but have
been unable to find a list of acceptable terms. For example,
iif([Actual
Start]="NA","true","false"), results in #ERROR, but so does
iif(iserror([ACT([Actual Start]="NA"),"true","false"). There is a
value of
"NA" in the field, but Jet Expression doesn't like it.

 

[JulieS]

Hi Dean,

I'm glad you found the information you were looking for. Thanks also
for pointing out the error in the help file. You're right it's a bit
confusing when it says: "Integer: String expressions that is the
interval of time you want to add."

Julie

I came across this when searching for my posts. I found what I was
looking
for quite awhile ago. The answer is to go to help and search for
Project
Functions. There is an error in the help file though. In DateAdd,
"interval",
is listed as "Integer" when in fact it can only be "s", "m", "h",
"d",
"w","M", "q", "y".

Hi Dean C,

You're welcome and thanks for the feedback. Good luck with your
quest.

Julie

JulieS,
Thanks, the value Jack used is incorrect for a date value of
"NA", but
it
gave me a starting point for a number. The correct number is
4294967295,
which is (2 to the 32nd power - 1). Jack forgot to subtract one,
but
did
provide the correct formula.

As I recall, this is the third thing I've learned in this news
group
and I
learned at least one of the other two from you also. I gave you
full
credit
even though my real quest was for good Jet expression references.

Access also uses Jet expression and includes an Expression
Builder, so
I'll
resort to that. I haven't played with databases (other than MSP)
since
dBase
III, so this should be interesting.

:

Hi Dean C,

For the NA issue see MVP Jack Dahlgren's page on calculated
fields
at:

http://masamiki.com/project/customfieldFAQ.htm

I hope this helps. Let us know how you get along.

Julie
Where are acceptable expressions for MS Project custom fields
defined?
Mike
Glen touched on the subject a few years ago, but I can't find
any
details.

I know that custom fields in Project use the Jet expression
format,
but have
been unable to find a list of acceptable terms. For example,
iif([Actual
Start]="NA","true","false"), results in #ERROR, but so does
iif(iserror([ACT([Actual Start]="NA"),"true","false"). There
is a
value of
"NA" in the field, but Jet Expression doesn't like it.