I am using the following code in a Report. Among other data the report shows the purchase quantity of raw cotton for a particular date. The report shows the correct data when there is a purchase on a given date. For dates there is no purchase the following error message appears:
Run-time error’-214735567(80020009)’:
The value you entered isn’t valid for this field
I want the report to show '0' as value if there is no purchase.
CODE:
Option Compare Database
Option Explicit
Private db As DAO.Database
Private rs As DAO.Recordset
Private rsFiltered As DAO.Recordset
Private strPurchSQL As String
Private TodayDel As Double
Private lngDate As Long
Private intCenter As Integer
Private intSeason As Integer
Private intVty As Integer
Private intFactory As Integer
Private intOperation As Integer
Private Sub InitString()
strPurchSQL = "SELECT DateNo([tblPurchase]![dateOfPurchase]) AS DateNo, tblSeasonCenterVtyJunction.SeasonPertaining, " _
& "tblSeasonCenterVtyJunction.CenterPertaining, tblSeasonCenterVtyJunction.VarietyPertaining, " _
& "tblHeap.Operation, [tblPurchase]![qtyPurchasedPked]+[tblPurchase]![qtyPurchasedLoose] AS QtyPurchased " _
& "FROM (tblFactory INNER JOIN (tblHeap INNER JOIN tblPurchase ON " _
& "tblHeap.heapID = tblPurchase.heapPrepared) ON tblFactory.factoryID = tblHeap.factoryProcessed) " _
& "INNER JOIN tblSeasonCenterVtyJunction ON " _
& "tblHeap.HeapRelatedTo = tblSeasonCenterVtyJunction.SeasonCentVtyID;"
End Sub
Private Sub InitVariables()
lngDate = Me.DPRdtNo
intCenter = Me.centerID
intSeason = Me.SeasonID
intVty = Me.varietyID
intFactory = Me.factoryID
intOperation = Me.OperationID
End Sub
Private Sub Report_Load()
Call InitVariables
Call InitString
Set db = CurrentDb
Set rs = db.OpenRecordset(strPurchSQL)
If Not rs.BOF And Not rs.EOF Then
rs.Filter = "[DateNo]=" & lngDate & "And[SeasonPertaining]=" & intSeason & "And[CenterPertaining]=" & intCenter & "And[VarietyPertaining]=" & intVty & "And[Operation]=" & intOperation
Set rsFiltered = rs.OpenRecordset
Me.KapDelToday = rsFiltered!QtyPurchased
End If
rsFiltered.Close
rs.Close
Set rsFiltered = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
----------------------
Please help the correction to be made.
Regards,
Alex