Bad Date Format String Error

[Netpear]

I'm running a report on Crystal 10 that lists all accounts on a daily basis. The admit date is based on 3 fields, Date1,Date2 and Date3, which represents the month/day/year.

I currently have 3 different parameters; month, day, year, that is prompted to the user each time the report is run.
The results are the list of all accounts in the format of the formula (@adm):
cdate(totext({BADPLPP.LPADT1},"00")+"/"+
totext({BADPLPP.LPADT2},"00")+"/"+
totext(2000+{BADPLPP.LPADT3},"0000"))

I would like to get rid of the parameters and use the formula:{@ADM} = Today-1. As the results of the report are one day behind. I currently have this setup in two other reports and it works perfectly. I have no idea why its not working here.

I checked to see if any null characters were being returned when i used the 3 paramters and the formula worked. When I removed the paramters and placed (@ADM)in the select expert, that is where I got all my errors.

Thanks for the help

 

[lbass]

I think it should be:

cdate(totext(2000+{BADPLPP.LPADT3},"0000")+"/"+totext({BADPLPP.LPADT1},"00")+"/"+
totext({BADPLPP.LPADT2},"00"))

-LB

 

[Netpear]

I still get the exact same error.

 

[lbass]

Please show samples of your fields. They are numbers, right? Can they ever be null?

-LB

 

[Netpear]

lpadt1 lpadt2 lpadt3
1 10 1
2 12 2
3 13 3
4 14 89
5 15 92
6 16 3
7 17 7
8 20 8
9 25 99
10 31 98

They are all numbers and never can be null. When I have the paramters and ask the user for the numbers, it will put the numbers in the formula with no problem. Its when I try to use the formula directly to get a date that gives me the problem.

 

[lbass]

I don't see any parameters in this formula. Please show the formula that uses the parameters. Also, you show numbers in date3 that are in the 80's and 90's--Do you really mean for these to read 2099, etc.? How are you using the formula to "get" a date? Where are you placing this formula?

-LB

 

[Netpear]

In the select expert, I have these 3 parameter fields and they are all formatted as numbers.
{lpadt1} = {?month}
{lpadt2} = {?day}
{lpadt3} = {?year}

Yes, if the year was 89, the formula would read it as 2089, but since those records are not relevant, I can omit those.

Instead of having all of these parameters, my end goal is to remove all the parameters and just have the select expert as:
{@ADM} = Today-1

 

[lbass]

I don't know why the formula is failing. Try this:

date(2000+{BADPLPP.LPADT3,{BADPLPP.LPADT1},{BADPLPP.LPADT2}) = currentdate-1

-LB

 

[Netpear]

When I use this new formula, I get the error: The Month number must be between 1 and 12. What can I do?

 

[Netpear]

Also, if I rearrange the formula, like this:
date({BADPLPP.LPADT1},{BADPLPP.LPADT2},2000+{BADPLPP.LPADT3}) = currentdate-1

I get the error that the year must be between 1 and 9999.

 

[lbass]

Please place your three fields in the detail section and insert minimums and maximums on each field, and then report back with the results.

-LB

 

[Netpear]

The Min for Lpadt1 is 0 and the Max is 12 Month
The Min for Lpadt2 is 0 and the Max is 31 Day
The Min for Lpadt3 is 0 and the Max is 99 Year

 

[lbass]

I would use the following record selection formula:

{BADPLPP.LPADT1} <> 0 and
{BADPLPP.LPADT2} <> 0 and
date(2000+{BADPLPP.LPADT3,{BADPLPP.LPADT1},{BADPLPP.LPADT2}) = currentdate-1

The year should be allowed to be 0 for 2000.

-LB

 

[Netpear]

It works!! Thanks a million.