Text box in report with calculated dates

  • Thread starter Thread starter Marinis Kostas
  • Start date Start date
M

Marinis Kostas

Goodmorning to all

my situation is this
Report based in a query with a [status] field, [datefield] field
and 3 calculated date fields, 6, 12 and 18 months after the
[datefield].
In the [status] field you can choose between 3 values (6months or
12months or 18months)
Can i have in an unbound text box in my report the calculated
value that the user has entered in the [datefield] depented from
the value in the [status]?
e.g if [datefield]=1/1/08 and [status]=6months then unbound text
field=1/7/08

TIA
 
One method if your Status field always start with number characters is to use
the following expression

= DateAdd("m",Val(Nz([Status],0),[DateField])

More complex method
= DateAdd("m",IIF([Status]="6 months",6, IIF([Status]="12 Months",
12,IIF([Status]="18 months",18,0))),[DateField])

If Status is null both expression should return the value of DateField.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you for the help John
unfortunately, when i put the expressions in an unbound text field, i
receive a syntax error (something for non valid character or comma or text
without quotes)
Status field always start with number characters



John Spencer said:
One method if your Status field always start with number characters is to use
the following expression

= DateAdd("m",Val(Nz([Status],0),[DateField])

More complex method
= DateAdd("m",IIF([Status]="6 months",6, IIF([Status]="12 Months",
12,IIF([Status]="18 months",18,0))),[DateField])

If Status is null both expression should return the value of DateField.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Marinis said:
Goodmorning to all

my situation is this
Report based in a query with a [status] field, [datefield] field
and 3 calculated date fields, 6, 12 and 18 months after the
[datefield].
In the [status] field you can choose between 3 values (6months or
12months or 18months)
Can i have in an unbound text box in my report the calculated
value that the user has entered in the [datefield] depented from
the value in the [status]?
e.g if [datefield]=1/1/08 and [status]=6months then unbound text
field=1/7/08

TIA
Click to expand...
Click to expand...
 
this expression DateAdd("m";IIf([Status]="6 months";6;IIf([Status]="12
months";12;IIf([Status]="18 months";18)));[DateField])
in the query is working fine and i do my job. It doesn't work as a text box
in the report.
Anyway thank you John

Kostas M said:
Thank you for the help John
unfortunately, when i put the expressions in an unbound text field, i
receive a syntax error (something for non valid character or comma or text
without quotes)
Status field always start with number characters



John Spencer said:
One method if your Status field always start with number characters is to use
the following expression

= DateAdd("m",Val(Nz([Status],0),[DateField])

More complex method
= DateAdd("m",IIF([Status]="6 months",6, IIF([Status]="12 Months",
12,IIF([Status]="18 months",18,0))),[DateField])

If Status is null both expression should return the value of DateField.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Marinis said:
Goodmorning to all

my situation is this
Report based in a query with a [status] field, [datefield] field
and 3 calculated date fields, 6, 12 and 18 months after the
[datefield].
In the [status] field you can choose between 3 values (6months or
12months or 18months)
Can i have in an unbound text box in my report the calculated
value that the user has entered in the [datefield] depented from
the value in the [status]?
e.g if [datefield]=1/1/08 and [status]=6months then unbound text
field=1/7/08

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top