Displaying last months entries only

[JSteele94]

Hello need the help of the guru's again. I'm using CR 10 Professional, and VB.NET

I have a program that stores appointments for customers. I have a CR that shows the appointments by each day. I am looking for a way where only last month's appointments show up on my CR.

Ex:

My report has the appointments grouped into each day, and shows everyday that has an appointment
ex: Monday 10/11/05 then Tuesday 10/12/05

I don't want those to show up when the month ends, how do I do this? Thanks!!

[JSteele94]

No reply's yet? This has to be possible.. If anyone can help, I'd appreciate it!

[si_the_geek]

Are you using an SQL statement to get the data?

If so we can probably make some modifications to the Where clause to do this. (I don't remember Crystal syntax, so we'll need to know what database system you are using).

[JSteele94]

Are you using an SQL statement to get the data?

If so we can probably make some modifications to the Where clause to do this. (I don't remember Crystal syntax, so we'll need to know what database system you are using).

Thank you for the reply. No I am not using a SQL statement. I am using CR 10 Professional and from within there I have two groups. One is grouped by DATE the other is grouped by Techinician. Basicly you only see the Date and under each Date you can see which Technicians have schedules for that day. I am looking for a way to make only the current month's dates show up on my report.

[si_the_geek]

Well in essence you need to create a Where clause for an SQL statement, but as you are letting Crystal create this for you there isn't a way to alter it (as far as I know!).

I haven't used Crystal for years, so I'm afraid I cant remember where to find the relevant options (or even what they are called!).

I think on one of the menus (likely to be the same as where you set the data source) there is an option to select the records that are allowed in the report, if you can't find it let me know what menu items there are! This should bring up a typical Crystal formula window, where you can select fields and so on, in which you should be able to enter something like this:

datefield > '10/1/2005'

If you can get that (or a valid variation of it!) working, then we just need to work out how to calculate the date, rather than hard-code it as above. In VB you could do this: Format(Date, "mm") & "/1/" & Format(Date, "yyyy") or this: Month(Date) & "/1/" & Year(Date)

[brucevde]

you need to create a Where clause for an SQL statement, but as you are letting Crystal create this for you there isn't a way to alter it

Unless, this has changed since version 8.5 it is possible to modify the Where clause of the SQL Statement generated by Crystal. There are two places.

1) Reports -> Report Selection Forumula or Group Selection Formula. Crystal uses these two formulas to generate the Where clause. Note - something entered into this formula may not become part of the Where clause but will be processed after data retrieval. For example, you base record selection on a formula.

2) Database -> Show SQL Query menu. Enter the desired Where clause. I don't recommend this method, simply because I have had problems in the past and probably don't know how to use it properly.

Again, this is version 8.5.

[JSteele94]

I found how to make a SQL expression from within CR 10. Is this where I would put in the code to sort out which month I wanted displayed? If so how do I make it show the current month only, but have it able to change from month to month?

[si_the_geek]

I think that the "report selection formula" as mentioned by bruce is the way to go, I think (but could be wrong) that this is a typical Crystal formula entry box, in which case you could use this to fix it to a pre-determined month:
datefield > '10/1/2005'

However to get the current month you need to use some of the functions that Crystal provides. I have no idea what is available, but i suspect that something like the following would work:

datefield > Month(Date) + "/1/" + Year(Date)

or possibly something like this:
datefield > Format(Date, "mm") + "/1/" + Format(Date, "yyyy")