Calendar Form

[Alex Motilal]

I got the help to create a Calendar from the YouTube "Creating A Calendar - Introduction - MS Access VBA Intermediate Tutorials"

I modified the information according the Database I created.

The modified LoadArray is as follows:

Private Sub LoadArray()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Dim strSQL As String
Dim i As Integer

strSQL = "SELECT tblSeason.Season, tblCenter.CenterName, tblOperation.Operation, tblVariety.VtyName, Sum([tblKpsPurchase]![KpsLoosePurQtl]+[tblKpsPurchase]![KpsPackedPurQtl]) AS TotalKps, " _


& "tblKpsPurchase.DtOfPurchase, DateNo([DtOfPurchase]) AS DateNumber, " _
& "DLookUp('[LintBudget]','[tblMarketAndBudget]','DateNo([DtOfMkt])=' & DateNo([DtOfPurchase]) & 'And[Center_ID]=' & [CenterID] & 'And[Variety_ID]=' & [VtyID]) AS LintPC, " _
& "DLookUp('[SdRtBudget]','[tblMarketAndBudget]','DateNo([DtOfMkt])=' & DateNo([DtOfPurchase]) & 'And[Center_ID]=' & [CenterID] & 'And[Variety_ID]=' & [VtyID]) AS BudSdRt, " _
& "SdSaleDaysAvgRate([CenterID],[VtyID],DateNo([DtOfPurchase])) AS DaysSeedAVrate, " _
& "tblCenter.CenterID, tblHeap.Operation_ID, tblVariety.VtyID " _
& "FROM tblOperation INNER JOIN (tblVariety INNER JOIN (tblSeason INNER JOIN ((tblCenter INNER JOIN tblHeap ON tblCenter.CenterID = tblHeap.Center_ID) " _
& "INNER JOIN tblKpsPurchase ON tblHeap.HeapID = tblKpsPurchase.Heap_ID) ON tblSeason.SeasonID = tblHeap.Season_ID) ON tblVariety.VtyID = tblHeap.Variety_ID) " _
& "ON tblOperation.OperationID = tblHeap.Operation_ID " _
& "GROUP BY tblSeason.Season, tblCenter.CenterName, tblOperation.Operation, tblVariety.VtyName, tblKpsPurchase.DtOfPurchase, DateNo([DtOfPurchase]), " _
& "DLookUp('[LintBudget]','[tblMarketAndBudget]','DateNo([DtOfMkt])=' & DateNo([DtOfPurchase]) & 'And[Center_ID]=' & [CenterID] & 'And[Variety_ID]=' & [VtyID]), " _
& "DLookUp('[SdRtBudget]','[tblMarketAndBudget]','DateNo([DtOfMkt])=' & DateNo([DtOfPurchase]) & 'And[Center_ID]=' & [CenterID] & 'And[Variety_ID]=' & [VtyID]), tblCenter.CenterID, tblHeap.Operation_ID, tblVariety.VtyID " _
& "ORDER BY tblKpsPurchase.DtOfPurchase; "
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

If Not rs.BOF And Not rs.EOF Then 'This line ensures that the recordset is populated

For i = LBound(myArray) To UBound(myArray) 'Loops through the Array using dates for the filter

If myArray(i, 1) Then

rs.Filter = "[DtOfPurchase]=" & myArray(i, 0) & "" 'Filter recordset with array dates

Set rsFiltered = rs.OpenRecordset 'Open up new recordset based on filter

Do While (Not rsFiltered.EOF) 'Loop through new recordset
'Adds text to the 3rd column of the array
myArray(i, 2) = myArray(i, 2) & vbNewLine _
& "Oper: " & rsFiltered!Operation & vbCrLf _
& "Cent: " & rsFiltered!centerName & vbCrLf _
& rsFiltered!VtyName & ": " _
& rsFiltered!TotalKps & " Qtl" & vbCrLf _
& "Bud Li%: " & rsFiltered!LintPC & vbCrLf _
& "Bud Sd Rt: " & rsFiltered!BudSdRt & vbCrLf _
& "Avg Sd Rt: " & rsFiltered!DaysSeedAVrate

rsFiltered.MoveNext
Loop

End If

Next i
End If

rsFiltered.Close
rs.Close

Set rsFiltered = Nothing
Set rs = Nothing
Set db = Nothing

End Sub

What I need is to create a ComboBox named "Centre" on the Form to select the CenterID and the Calendar should display the information only for the Center selected. I need to know how the strSQL should written with WHERE or HAVING Clause.

Thanks.

[CJ_London]

in a group by query WHERE is used when you are filtering the underlying data. HAVING is used where you want to filter on a sum, count or other aggregation

So you query might be

SELECT...
FROM...
WHERE tblCenter.CenterName='abcd'
GROUP BY....
HAVING Sum([tblKpsPurchase]![KpsLoosePurQtl]+[tblKpsPurchase]![KpsPackedPurQtl])<>0
ORDER By...

However using domain functions in queries is very slow

Also, it helps others to help you to use the code tags to preserve indentation and make your code easier to read

[June7]

…
& "WHERE CenterID = " & Me.Centre & _
& " ORDER BY tblKpsPurchase.DtOfPurchase; "


Need spaces before and after each And in the DLookup expressions.

[Alex Motilal]

Both the solutions not working. A Message box appears stating that there is an error and asks the form to reload.

[ranman256]

Queries do not need Dlookup. Just join to the lookup table.

[June7]

And exactly what is the error message?

Ooops, WHERE clause must be before the GROUP BY clause.

[Alex Motilal]

Dear June7,

The Error Message: "There has been an error. Please reload the form"

[June7]

Even after the last suggested correction?

Not much of an error message. I am not familiar with it. If you want to provide db for analysis, follow instructions at bottom of my post. Although if it is not English might not be able to do much with it.