Default Current Date for selection 1

[mbr4ndt]

I have a report set up to count the number of entries from a MS SQL database based on today’s date. Currently I am changing the date (x/x/xxxx) on a daily basis via "Select Expert Record". I would like the report to run automatically with the current date without having to manually update it every day but I have been unsuccessful in getting this to work. Any help is greatly appreciated.

 

[WaelYassin]

You can use the special field (CurrentDate), it will return the current date value.

 

[mbr4ndt]

Is there a formula that must be used? I have tried setting the date = CurrentDate but it won't pull any entries from the date. It is looking for an entry with "CurrentDate" as the date.

 

[CR85user]

Currentdate is a special field type which will automatically transpose the correct date for today in place of the field.
//Example criteria
{table.date} in currentdate and {customer.id} = 1

(You will notice it shows in blue text)

The only reason this wouldn't happen is if:

* Your date field is actually a string type and you have put quotes around currentdate.

* Your date field is not being interpreted correctly.

Please give a sample of raw data so we can better advise. I notice that you had single digits for the month and day in the above example. Are they stored in this way in your sql db?

Cheers

'J

CR8.5 / CRXI - Discovering the impossible

 

[mbr4ndt]

The report is just a summary of what was entered for the day. It is simply counting how many entries under the specific date. The application we use writes to the database in single digits for the date. For example todays entries would say 2/26/2009.

 

[lbass]

You need to report back with the datatype of the field--it sounds like it might be a string. Right click on it->browse field and see what the datatype says.

-LB

 

[mbr4ndt]

Yes it shows Type: String, Length: 15.

 

[lbass]

That length doesn't really make sense. You would only need 10 spaces for a date, but you would need more than 15 if there were a time component. Can you please show samples (again browse the field) that shows the variation in the field? Can we assume that the first 10 characters always represent the date?

-LB

 

[mbr4ndt]

Is 15 the default limit? I never changed length settings. The data could be 8-10 characters. For instance 1/1/2009 or 12/12/2009.

 

[lbass]

Create a formula in the field explorer {@date}:

cdate({table.stringdate})

Then go to report->selection formula->record and enter:

{@date} = currentdate

-LB

 

[mbr4ndt]

I tried what you suggested. It shows no errors when I check. When I try to run the report I get a message that says: "Bad date format string". When I click OK it highlights the cdate formula.

 

[lbass]

Can the string be null? Or could you have bad data--with the month field greater than 12 or the day field greater than 31?

-LB

 

[mbr4ndt]

You were correct some null value in the database caused the error. I cleared out everything except for valid dates and it worked fine. Thanks for your help.

 

[anish95]

Hi,
I am trying to convert string to date. In my database date is string field (YYYYMMDD) I want to change into MM/DD/YYYY. Because I want to run a report everyday how many orders are on previous day. Right now I am doing manually.

I created a new formula field called @date and in formula edit enter Cdate({Tablename. filedname}) save and close it.
Then in record selection formula I choosed @date = Currentdate. I got the error Bad Date format sting. Please help me. Thanks!
SKR

 

[dgillz]

try:

numbertodate(val({yourfield}))

If the numbertodate() function does not work you will have to download it.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[lbass]

You could also convert to a date in a formula like this:

stringvar x := {table.stringdate};
date(val(left(x,4)), val(mid(x,5,2)), val(right(x,2)))

-LB

 

[anish95]

Thank you very much lbass. It worked fine. Again thanks for your help.