writing syntax

A

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
 
L

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
Click to expand...

Have you considered:

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

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,
 
F

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,
Click to expand...

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])
 
S

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,
Click to expand...

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])
Click to expand...
 
A

aubrey

thanks alot, you guys were a real help.

strive4peace said:
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
Click to expand...

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])
Click to expand...
Click to expand...
 
S

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.

strive4peace said:
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])
Click to expand...
Click to expand...
Click to expand...
 
Top