New user in need of help with a query

[oldgoldone]

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

 

[BobStubbs]

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:

=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:

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