text sheet object - with date expression

[#1]

hi i want to add a text sheet object with a concatenation of text and date. the date is to be returned by an expression that gets the most recent date from a field. I have created a script that works but the date format comes out in numeric format not date.

e.g. my text object script:

='Aged Debtors Report for ' & max( {1} [Sort Date])

returns

Aged Debtors Report for 40367

And it should be

Aged Debtors Report for 08/07/2010

Regards

[#2]

Create a text object and in the text field type in

='Aged Debtors Report for' & today() which will reurn the date everytime it is opened or try

='Aged Debtors Report for' & date(()DD/MM/YYYY)

maybe?

just guessing here sorry

[#3]

Try the following code:

='Aged Debtors Report for ' & date( max( {1} [Sort date]),'DD/MM/YYYY')

[#4]

Hi the folloiwng script worked fine, many thanks.

='Aged Debtors Report for ' & max( Date([Sort Date]))

I was also wondeirng if you might know the answer to the following. If i wanted to add a value from another field called 'additional days' to the max( Date([Sort Date] to get a final date to display in the text object how could i do this?

e.g. if the script

='Aged Debtors Report for ' & max( Date([Sort Date]))

returns

Aged Debtors Report for 08/07/2010

and i wanted toamend script to lets say

='Aged Debtors Report for ' & max( Date([Sort Date])) + additional days

that would hopefully return (if additional days = 4) the following

Aged Debtors Report for 12/07/2010

[syed_muzammil]

Hi,

Try this

='Aged Debtors Report for ' & Date( (num(max([Sort Date]) )+ num([additional days])),'DD/MM/YYYY')

[#6]

This script

='Aged Debtors Report for - ' & Date( (num(max([Sort Date]) )+ num([Days Outstanding])),'DD/MM/YYYY')

returns

Aged Debtors Report for -

Therefore no date is displayed now. The Sort date field is a date and the days outstanding field is a number.

Any suggestions?

[syed_muzammil]

Hi,

Yes that is the reason i used num(). Anyways try removing num from both the feilds.

='Aged Debtors Report for - ' & Date( (max([Sort Date]) + [Days Outstanding]),'DD/MM/YYYY')

or

='Aged Debtors Report for - ' & Date( (max([Sort Date]) + num([Days Outstanding])),'DD/MM/YYYY')

[#8]

Hi many thanks but both these dont work.

I never knew it would be so difficult adding days to a date field.

Any other suggestions?

[nathanfurby]

I think there may be one set of brackets too many in that last expression. Try:

='Aged Debtors Report for - ' & Date(Max([Sort Date]) + [Days Outstanding],'DD/MM/YYYY')

or perhaps the following if you need the NUM() function before [Days Oustanding]:

='Aged Debtors Report for - ' & Date(Max([Sort Date]) + Num([Days Outstanding]),'DD/MM/YYYY')

[syed_muzammil]

='Aged Debtors Report for - ' & Date( (max([Sort Date]) + num([Days Outstanding]),'DD/MM/YYYY')

yup one bracket was extra. Try this now.