Filter Formula Needed

[Leesha]

Hi,

I have a query that filters based on [year] that is on frmSwitchboard. This works fine as long as the year is present in the query. If the year isn't present, the results are null. What I need is, if the year isn't present, the the next most recent year would be filtered. For example, if 2012 isn't present in the query, and the next most current year is 2010, the the query should return results for 2010. I'm not sure if this is even possible.

Thanks,
Leesha

[HansV]

I don't think you can do that in a query, at least not easily.
Instead, you could use code to determine whether there are records for the specified year, for example by using DCount.
If DCount returns 0, try [year] + 1, then [year] - 1, etc., until records are found.

If you need help with this, please provide more detailed information (names of tables and queries involved).

[Leesha]

Thanks Hans. Where would I put this code? In the report as it loads and if so which event?

Thanks!
Leesha

[HansV]

I'd put it in the On Click event procedure of a command button on a form that is used to open the report.

[Leesha]

Ok, will give it a shot. Thanks!

[Leesha]

I couldn't get Dcount to work (probably because I've never used it before) so I tried using DLookup and this worked the way I needed it to. There will probably be times when I need it to loop till the year is correct but I wasn't sure how to code it.



Me.txtYear = Year(Me.txtTo)

If IsNull(DLookup("Year", "qrySalePriceYear")) Then
Me.txtYear = Year(Me.txtTo) - 1
DoCmd.OpenReport "rptInventoryYeartoYearReport2010to2014", acViewPreview

ElseIf Not IsNull(DLookup("Year", "qrySalePriceYear")) Then

DoCmd.OpenReport "rptInventoryYeartoYearReport2010to2014", acViewPreview
End If

Thanks!
Leesha

[HansV]

You can place the DoCmd.OpenReport line after the End If:

Code: Select all

    Me.txtYear = Year(Me.txtTo)
    If IsNull(DLookup("Year", "qrySalePriceYear")) Then
        Me.txtYear = Me.txtYear - 1
    End If
    DoCmd.OpenReport "rptInventoryYeartoYearReport2010to2014", acViewPreview

[Leesha]

Hi Hans,
I tried moving the docmd but it still opens with no info and doesn't loop till the query is not null.

Leesha

[HansV]

Do you always want to go back in time until you find records, or do you want to search both forwards and backwards from the year specified in txtTo?

[Leesha]

I would always need to go back in time till the query isn't null.

[HansV]

Let's say the command button is named cmdReport.

Code: Select all

Private Sub cmdReport_Click()
    Dim n As Long
    For n = 0 To 25
        ' Set year
        Me.txtYear = Year(Me.txtTo) - n
        ' Test whether there are records
        If DCount("*", "qrySalePriceYear") > 0 Then
            ' Found! Open report
            DoCmd.OpenReport "rptInventoryYeartoYearReport2010to2014", acViewPreview
            Exit Sub
        End If
    Next n
    ' If we get here, no year with data was found
    MsgBox "No data for the previous 25 years!", vbExclamation
End Sub

This version goes back at most 25 years before the year specified in txtTo; if that is too much or too little you can easily modify the code by replacing 25 with the desired number of years.

[Leesha]

Thanks Hans! Works perfectly!
Leesha