bit of a toughy, i think...

[PJB]

What I'm trying to do is get a sum for a field(PaymentAmount) and group them by apartment(aprtmentID) and property(PropertyID) and tenant(CurrentTenant), which i've got working fine

Private Sub cmdPrint_Click()
Dim DateFrom As String
Dim DateTo As String
Dim PropertyID As Long
Dim Address As String

PropertyID = fmrProperty.Data1.Recordset!PropertyID
Address = fmrProperty.Data1.Recordset!Address

DateFrom = DTPicker1
DateTo = DTPicker2
sqlstring = "Select DISTINCTROW [AptIncome].[aptID], [AptIncome].[PropertyID], [AptIncome].[CurrentTenant], Sum([AptIncome].[PaymentAmount]) AS [Sum Of PaymentAmount]From AptIncome Where PropertyID = " & PropertyID & " GROUP BY [AptIncome].[aptID], [AptIncome].[PropertyID], [AptIncome].[CurrentTenant]"

With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = sqlstring
.Execute
End With

With rs

.CursorLocation = adUseClient
.Open cmd
End With

But when i try to add in something similiar to this


PaymentDate Between #" & DateFrom & "# and #" & DateTo & "#

(selecting the appropriate field in the SELECT portion of my statement and putting it in the GROUP BY portion)
It starts breaking it down by pay dates again rather then summing them
not sure where to go from there....



oops... sorry about the extremely long sqlstring

[FrancisC]

I'm not sure exactly what your final SQL looks like, but if you select the date, it will group by individual date.

You should be able to filter for certain dates without selecting your date and you would then be fine...

[PJB]

sorry, i should have posted that i figured it out, the SQL statement is turning out to be as long as my arm though....