writing syntax

[aubrey]

can you write a syntax that says if text in one field is "x" take the date
from another filed add 45 days and populate a third field

any help would be greatly appreciated

 

[louisjohnphillips]

can you write a syntax that says if text in one field is "x" take the date
from another filed add 45 days and populate a third field

any help would be greatly appreciated

Have you considered:

Update MyTable set FutureDate = SomeDate + 45 where XField = 'x';

 

[strive4peace]

Hi Aubry,

yes, if you are talking about a form ...

if so, what are the fieldnames?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[aubrey]

Hey Crystal,

the three fields are "Department Decision", "Decision Date" and "Closed Date"
-i want the syntax to say when Department Decision says "X" take the date
that's already in "Decision Date" field, add 45 days and populate "Closed
Date" field.

Thanks again,

 

[fredg]

Hey Crystal,

the three fields are "Department Decision", "Decision Date" and "Closed Date"
-i want the syntax to say when Department Decision says "X" take the date
that's already in "Decision Date" field, add 45 days and populate "Closed
Date" field.

Thanks again,

This is a query newsgroup. I'll assume you wish to do this is a query.
What do you wish to put in [ClosedDate] if the value is not "x"?
Nothing?

ClosedDate:IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)

You can then used [ClosedDate] on any form or report that uses this
query as it's record source.
Note: since [ClosedDate] is a calculated date there is no need to
actually store the [ClosedDate] value in any table. Any time you need
the [ClosedDate] value, simply re-calculate it, as above.

Also, in place of [DecisionDate] + 45
you could use DateAdd("d",45,[DecisionDate])

 

[strive4peace]

Hi Aubry,

adding on to what Fred said ...

since you can calculate this value ANYTIME, it is best not to store it,
but just to calculate it when you need it

~~~

to summarize:

in a query (what Fred said):
ClosedDate: IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)

on a form (textbox control, for instance)

ControlSource:
=IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)
Name:
ClosedDate

~~~

in case DecisionDate is not filled out, you may want to modify the
equation to something like this:

IIf([DepartmentDecision]="x" and not
IsNull([DecisionDate]),[DecisionDate] + 45,Null)


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hey Crystal,

the three fields are "Department Decision", "Decision Date" and "Closed Date"
-i want the syntax to say when Department Decision says "X" take the date
that's already in "Decision Date" field, add 45 days and populate "Closed
Date" field.

Thanks again,

This is a query newsgroup. I'll assume you wish to do this is a query.
What do you wish to put in [ClosedDate] if the value is not "x"?
Nothing?

ClosedDate:IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)

You can then used [ClosedDate] on any form or report that uses this
query as it's record source.
Note: since [ClosedDate] is a calculated date there is no need to
actually store the [ClosedDate] value in any table. Any time you need
the [ClosedDate] value, simply re-calculate it, as above.

Also, in place of [DecisionDate] + 45
you could use DateAdd("d",45,[DecisionDate])

 

[aubrey]

thanks alot, you guys were a real help.

Hi Aubry,

adding on to what Fred said ...

since you can calculate this value ANYTIME, it is best not to store it,
but just to calculate it when you need it

~~~

to summarize:

in a query (what Fred said):
ClosedDate: IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)

on a form (textbox control, for instance)

ControlSource:
=IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)
Name:
ClosedDate

~~~

in case DecisionDate is not filled out, you may want to modify the
equation to something like this:

IIf([DepartmentDecision]="x" and not
IsNull([DecisionDate]),[DecisionDate] + 45,Null)


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hey Crystal,

the three fields are "Department Decision", "Decision Date" and "Closed Date"
-i want the syntax to say when Department Decision says "X" take the date
that's already in "Decision Date" field, add 45 days and populate "Closed
Date" field.

Thanks again,

:

Hi Aubry,

yes, if you are talking about a form ...

if so, what are the fieldnames?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

aubrey wrote:
can you write a syntax that says if text in one field is "x" take the date
from another filed add 45 days and populate a third field

any help would be greatly appreciated

This is a query newsgroup. I'll assume you wish to do this is a query.
What do you wish to put in [ClosedDate] if the value is not "x"?
Nothing?

ClosedDate:IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)

You can then used [ClosedDate] on any form or report that uses this
query as it's record source.
Note: since [ClosedDate] is a calculated date there is no need to
actually store the [ClosedDate] value in any table. Any time you need
the [ClosedDate] value, simply re-calculate it, as above.

Also, in place of [DecisionDate] + 45
you could use DateAdd("d",45,[DecisionDate])

 

[strive4peace]

you're welcome, aubry happy to help


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

thanks alot, you guys were a real help.

Hi Aubry,

adding on to what Fred said ...

since you can calculate this value ANYTIME, it is best not to store it,
but just to calculate it when you need it

~~~

to summarize:

in a query (what Fred said):
ClosedDate: IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)

on a form (textbox control, for instance)

ControlSource:
=IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)
Name:
ClosedDate

~~~

in case DecisionDate is not filled out, you may want to modify the
equation to something like this:

IIf([DepartmentDecision]="x" and not
IsNull([DecisionDate]),[DecisionDate] + 45,Null)


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

On Thu, 3 Jul 2008 06:03:01 -0700, aubrey wrote:

Hey Crystal,

the three fields are "Department Decision", "Decision Date" and "Closed Date"
-i want the syntax to say when Department Decision says "X" take the date
that's already in "Decision Date" field, add 45 days and populate "Closed
Date" field.

Thanks again,

:

Hi Aubry,

yes, if you are talking about a form ...

if so, what are the fieldnames?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

aubrey wrote:
can you write a syntax that says if text in one field is "x" take the date
from another filed add 45 days and populate a third field

any help would be greatly appreciated

This is a query newsgroup. I'll assume you wish to do this is a query.
What do you wish to put in [ClosedDate] if the value is not "x"?
Nothing?

ClosedDate:IIf([DepartmentDecision]="x",[DecisionDate] + 45,Null)

You can then used [ClosedDate] on any form or report that uses this
query as it's record source.
Note: since [ClosedDate] is a calculated date there is no need to
actually store the [ClosedDate] value in any table. Any time you need
the [ClosedDate] value, simply re-calculate it, as above.

Also, in place of [DecisionDate] + 45
you could use DateAdd("d",45,[DecisionDate])