Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

New user in need of help with a query

Status
Not open for further replies.

oldgoldone

Technical User
Apr 24, 2004
13
US
I have this as a field in my query
Date of Service By Quarter: Format$(Service.[Date of Service],'"Quarter "q yyyy')where q gives a number 1-4
is there any way to have it report 1st 2nd 3nd 4th as in
2nd Quarter 2004
 
I can make this work in a report, if that is of any use to you.

Include your date field in the Report Details section, for testing.
Create two unbound text boxes in the Report Details section, called txtQuarterNumber and txtQuarterMessage

Set the Control Source for 'txtQuarterNumber' to be:
Code:
=Format$([YourDateField],"q")
... replace [YourDateField] with the name of the field containing your date.

Now create an event procedure for the On Format event of the Report Details section:
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case txtQuarterNumber.Text

Case Is = "1"
    txtQuarterMessage = "1st Quarter "
Case Is = "2"
    txtQuarterMessage = "2nd Quarter "
Case Is = "3"
    txtQuarterMessage = "3rd Quarter "
Case Is = "4"
    txtQuarterMessage = "4th Quarter "
End Select

txtQuarterMessage = txtQuarterMessage & Format$(Me.YourDateField, "yyyy")

End Sub
When you test this report, you should see something like:

Date txtQuarterNumber txtQuarterMessage
01/01/2004 1 1st Quarter 2004
31/12/2003 4 4th Quarter 2003
10/09/2004 3 3rd Quarter 2004
etc

Finally, remove the date field if you don't need it, and set the Visible property on the 'txtQuarterNumber' field to 'False', which will hide this field.

I hope that this helps.



Bob Stubbs
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top