Report Parameter

[valgore]

Hello, i've been reading a lot of different posts on this and i still cant seem to figure it out. basically i have a report that shows data for the current year broken up by months. i created a crosstab query and i've attached what the report looks like (which is what i want). the numbers at the top are the months (not showing November or December because there wasnt enough room) what i want is to show the data from 6 months before the current month and 6 months after current month. the table that this is pulling from has two different fields, one for month and one for year.


thanks

 

[GinaWhipp]

Have you tried using BETWEEN...

Between Month([YourDate])-6 And Month([YourDate])+6

...for Criteria?

 

[valgore]

i have. the problem i think is that the month's in the report is the month field i have in the table. the Table fields are month, year, and month/year. the month field is just a number which corresponds to the month, and the month year is a combo of the two fields (01/2012, etc) so on the report, the month fields are 01/2012, 02/2012, etc. do i change the fields themselves in the report to some code?

 

[GinaWhipp]

Perhaps it is not seeing them as a Date? Combo boxes have been known to *convert* its values to TEXT. When this happens using CDate() around your date function will fix that. Regardless they both have to match. You can't expect the Month to pull the Month out of Month/Year it won't happen.

 

[valgore]

i figured out how to fix my problem. might not be the prettiest but it works. ill post the code when im done. thanks for your help

 

[GinaWhipp]

HEH, as long as it works... And posting it is bound to help someone else!

 

[valgore]

ok this was my fix. i haven't tested it a whole lot, but it seems to work for the ones i've tested.

Private Sub Report_Open(Cancel As Integer)
    Dim strstartdate As String
    Dim strenddate As String
    Dim month As String
    Dim year As String
    Dim counter As Integer
    counter = 1
    strstartdate = Forms!frmreports!StartDate
    strenddate = Forms!frmreports!EndDate
    month = Left([strstartdate], InStr([strstartdate], "/") - 1)
    year = Right([strstartdate], InStr([strstartdate], "/") + 2)
    Do Until counter = 13
        If month = 13 Then
            month = 1
            year = (year + 1)
            Me.Controls("txtDateLabel" & counter).Caption = (month & "/" & year)
            Me.Controls("txtDate" & counter).ControlSource = (month & "/" & year)
            counter = counter + 1
            month = month + 1
        Else
            Me.Controls("txtDateLabel" & counter).Caption = (month & "/" & year)
            Me.Controls("txtDate" & counter).ControlSource = (month & "/" & year)
            counter = counter + 1
            month = month + 1
        End If
    Loop
    counter = 1
    month = 0
End Sub

edit: this code is good to see a full years worth of data

 

[GinaWhipp]

Pretty and cool! Thanks for sharing!