If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
writing syntax
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 |
#2
|
|||
|
|||
writing syntax
On Jul 2, 6:23*pm, 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 Have you considered: Update MyTable set FutureDate = SomeDate + 45 where XField = 'x'; |
#3
|
|||
|
|||
writing syntax
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 |
#4
|
|||
|
|||
writing syntax
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, "strive4peace" wrote: 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 |
#5
|
|||
|
|||
writing syntax
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, "strive4peace" wrote: 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]) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
writing syntax
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 * fredg wrote: 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, "strive4peace" wrote: 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]) |
#7
|
|||
|
|||
writing syntax
thanks alot, you guys were a real help.
"strive4peace" wrote: 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 * fredg wrote: 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, "strive4peace" wrote: 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]) |
#8
|
|||
|
|||
writing syntax
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 * aubrey wrote: thanks alot, you guys were a real help. "strive4peace" wrote: 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 * fredg wrote: 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, "strive4peace" wrote: 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]) |
Thread Tools | |
Display Modes | |
|
|