Closest to date

[auburnfan]

I am using CR 9.
What I need to do is find a way to bring up an entry that is closest to current date. So it will find the last journal entry. Any suggestions?
thanks

 

[Turkbear]

Hi,
You will need to 'tell' the database/report what 'closest' means, so I suspect you would need to create a formula that tests various datediffs and use the minimum as the resulting selection criteria.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[damonkimble]

If you are using a command, you can write the query to have a max(date_field) <= your date parameter (current or otherwise). Your date parameter would have to be a command parameter.

If you aren't using a command, you could order by date desc and suppress everything other than the first record.

damon

 

[auburnfan]

ALright, how do I suppress all of the entries other then the first one?

 

[Madawc]

Group by entry code. Use Report > Record Sort Expert to put the group in date sequence. Suppress the details. Show the group header or footer, which will be the latest, depending whether date order is ascending or descending.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Or you can go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.group})

This assumes that you want the most recent date per some group.

Or you could create a command:

select table.`groupfield`, max(table.`date`) as maxdate
from `table`table
group by table.`groupfield`

Link that to the main table on the group field, and then in the record selection formula use:

{table.date} = {command.maxdate}

In the first scenario, any inserted summaries would include non-group selected summaries. Using the command allows you to return only the records that are the most recent, and you can therefore insert summaries safely.

-LB

 

[auburnfan]

Ok
This didnt quite do what I was needing.
The two tables that I am using are
{journal.entrydate} and {journal.entrytime}
There could be multiple entries on a date.
What I need is for it to check for the closest date to current date and then check for the time closest to "11:59PM".
This way it will give my the last journal entry.
I am not very experienced in the formula aspect of reports so I am way over my head.
thanks

 

[lbass]

Create a formula {@datetime}in the formula expert:

cdatetime({journal.entrydate},{journal.entrytime})

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

{@datetime} = maximum({@datetime},{table.groupfield})

Substitute your group field (maybe on account ID?) for {table.groupfield}.

-LB

 

[auburnfan]

I have the journals appearing in ascending order....is there a way to take the first entry and hide the rest? That would seem to be a much more simple way to do this.

 

[synapsevampire]

Put the field in the group footer and suppress the details and the group footer, or put them in descending and place the field in the group header and suppress the details.

this assunmes that you are grouping by whatever entity yo want the latets date for.

One of the difficulties with this type of post is that nothing technical has been shared, so lots of guesswork occurs.

Successful posts tend to include:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[auburnfan]

Alright, I did this, placed it in the group footer but the problem is it is still giving blank spaces in the deatils section for the supressed entries.

 

[synapsevampire]

Suppress the details itself, not the fields.

Right click the details and select Suppress.

-k