strFilter = "(Deposits.Account='" & List13 & "') AND (Deposits.Fund='" &
List11 & "') AND (Year([DateDep])= " & (Left(List15.Column(0), 4)) & " )
AND (Month([DateDep])= " & (Right(List15.Column(0), 2)) & " )"
"Gator" wrote:
[Quoted Text] > the error is located in the TabCtl0_Change event at the
> Datepart("yyyy",[DateDep])
>
> What I'm trying to do is click on List13 which queries List15 and a click on
> List15 will query the Deposits_Subform to those dates of that year and month
> in List15.
>
> I will say when I delete the yyyy and mm criteria with just the Fund and
> Account criteria in strFilter, the query works showing only the records of
> the Fund and Account.
>
> Also, I have used a slightly different criteria expression that used the
> Year([DateDep]) and month([DateDep]) and it worked on only the
> 'mm' that were 12,11,10 and not the ones of 09,08,07.....
>
> Any ideas on how to express the criteria to query by year and month?
> Private Sub List13_Click()
>
> If Frame17.Value = 1 Then
> List15.RowSource = "SELECT Format((DateDep),'yyyymm'),
> Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
> List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyymm'),
> Account ORDER BY Format((DateDep),'yyyymm') DESC;"
> Else
> List15.RowSource = "SELECT Format((DateDep),'yyyy'),
> Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
> List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyy'),
> Account ORDER BY Format((DateDep),'yyyy') DESC;"
>
> End If
> End Sub
>
> Private Sub TabCtl0_Change()
> Dim strFilter As String
> With Me.TabCtl0
> strFilter = "(Deposits.Account='" & List13 & "') AND (Deposits.Fund='" &
> List11 & "') AND ((Datepart("yyyy",[DateDep]))='" & (Left(List15.Column(0),
> 4)) & "') AND ((Datepart("mm",[DateDep]))='" & (Mid(List15.Column(0), 4)) &
> "')"
> End With
>
> With Me.Deposits_subform.Form
> .Filter = strFilter
> .FilterOn = True
> .OrderBy = "DateDep DESC"
> .OrderByOn = True
> End With
> End Sub
|