Report dates

[oldgoldone]

I am grouping my date by quarter. I am asking the user to enter what quarter "Q2 2004" my problem is in my report I want it to show up as 2004 2nd Qtr can this be done if so can anybody help me?

 

[scriverb]

How are you prompting the user to enter their selection? If it is free format then this is not a good idea. I would create a modal popup form with a combobox. The RowSource for the combobox would be a query of your table listing all of the legitimate report selections along with how you want them to be displayed in the report.

Once selected the values from the combobox could be used to make your recordselection for the report as well as a direct reference to the display text boxes in the report.

Post back if you need more assistance.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[oldgoldone]

I get what you are saying about the use of a combo box but Would that would bring up every date of service all 6897 of them I have them entered in the table in short date format and then i use a query to group them into quarters

 

[scriverb]

No. Create a parameter table, let's call it tblQuarters. Have a text field for how you want them to pick the quarter, one for what you want it to look like in the report, and two date fields QtrBeginDate and QtrEndDate. Enter all of the quarters that you want the users to be able to pick.

Now create a query pulling in all four fields. You now have a column reference for the report display as well as a begin and end date for the selected quarter. Those can now be used as a reference in your queries WHERE clause by referencing the FORMS![formname]![comboname].column([RED]X[/RED]). The red X being the column of each of the two dates. Remember that you must refer to them starting at Zero(0).

Post back if you have more questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[oldgoldone]

Thanks alot with your help, you guy that answer these post are great!! I put this into the field name in my Query and it seem to work great "Date of Service By Quarter: Format$(Service.[Date of Service],'q"Quarter "yyyy')