Results 1 to 3 of 3
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    Value to populate in Report

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I don't see a reason for ANY of this code.

    just open a query,
    by clicking
    or
    docmd.openquery

  3. #3
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks Ranman
    The said report is being generated by functions created in Module.
    What I posted is for academic interest. If there is any solution to return 0 in the code, for the date for which there is no purchase , please inform.
    Regards,
    Alex

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-09-2015, 09:02 AM
  2. Populate Unbound Fields on Report
    By RyanP in forum Reports
    Replies: 6
    Last Post: 06-24-2015, 08:12 PM
  3. Replies: 3
    Last Post: 03-11-2015, 12:36 PM
  4. Populate Text in Report Header
    By libraccess in forum Programming
    Replies: 10
    Last Post: 06-28-2013, 11:49 PM
  5. Populate textboxes on a report from a query
    By dccjr in forum Programming
    Replies: 3
    Last Post: 05-14-2013, 07:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums