DateAdd Function Help

[Former Member]

Good Morning,

I have a report right now that displays all the appointment times for a doctor. I need to create a report that looks for appointments 2 days ahead of the current date. I.E. If i run the report on a monday, i will get wednesdays appointments.

I have tried going to my select expert and pick appointments.appt_date then picked formula and tried both

appointments.appt_date = 'currentdate' +2

and

appointments.appt_date =dateadd(u201Cdu201D, 2, currentdate)

The bottom formula works if i put it in the details and will return the correct date. But how do i select the records using this formula? Also my appointments field is formatted as 20081223. I suspect that maybe my select statement isnt working because the dates are in this format? If i right click that field and pick format field there isnt a date tab. Sorry to be such a noob, but I could really use some help on this.

Thanks

Ringrim

Edited by: Robert Ingrim on Dec 23, 2008 6:38 PM

[Former Member]

Hi Robert,

Please insert the following formula in dt

numbervar dd;

Numbervar mm;

numbervar yy;

dd:=tonumber(right(totext(appointments.appt_date,"##"),2));

mm:=tonumber(mid(totext(appointments.appt_date,"##"),5,2));

yy:=tonumber(left(totext(appointments.appt_date,"##"),4));

cdate(yy,mm,dd)

Thanks,

Sastry

[Former Member]

Hi Robert,

You could also use this expression in Selection criteria

appointments.appt_date = totext(dateadd("d", 2, currentdate),"yyyyMMdd")

[Former Member]

Sastry

When i paste that code into dt it gives me an error saying i need a ) at appointments.appt_date.

This is the whole forumla correct?

numbervar dd;

Numbervar mm;

numbervar yy;

dd:=tonumber(right(totext(appointments.appt_date,"##"),2));

mm:=tonumber(mid(totext(appointments.appt_date,"##"),5,2));

yy:=tonumber(left(totext(appointments.appt_date,"##"),4));

cdate(yy,mm,dd)

[Former Member]

appointments.appt_date = totext(dateadd("d", 2, currentdate),"yyyyMMdd")

will not work as you will be comparing numeric to text, if appt_date is numeric indeed.

If appt_date is numeric then the easiest way would be to convert the above.

Robert:

In your selection criteria, just use the below:

appointments.appt_date = tonumber(totext(dateadd("d", 2, currentdate),"yyyyMMdd"))

[Former Member]

Hi

Please try this

numbervar dd;

Numbervar mm;

numbervar yy;

dd:=tonumber(right(totext({appointments.appt_date},"##"),2));

mm:=tonumber(mid(totext({appointments.appt_date},"##"),5,2));

yy:=tonumber(left(totext({appointments.appt_date},"##"),4));

cdate(yy,mm,dd)

Thanks,

Sastry