Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    9

    Question Unanswered: how to retrieve a specific set of data in queries bound by 2 dates???

    What I want to do is to retrieve a specific set of data between 2 dates.

    Aan error showed up "No item found in this collection". I checked the queries already, and there should be some data that exists betweeen the dates that I inputed.

    Is this the problem with my query setting or is there something I lack in the codes?Any suggestion is very much appreciated.

    Following is the code.
    Code:
    Public Sub CashFlowFormat(ByVal strExcelPath As String)
        Dim objXL As Excel.Application, objXLWB As Excel.Workbook, objXLWS As Excel.Worksheet
        Dim objXLWBTemp As Excel.Workbook
        Dim longMaxRow As Long, intMaxCol As Integer
        Dim dbCurrent As DAO.Database, rstTable As DAO.Recordset
        Dim rngCells As Range
        Dim qryCashFlowQuery As QueryDef
        Dim strDateStart As String, strDateEnd As String
        Dim strFileName As String
        Dim j As Long, k As Long, l As Long
            
        'On Error GoTo Err_CashFlowFormat
        
        'Gets the input parameters - start date and end date
        strDateStart = DateValue(InputBox("Pls enter the start date:", "Start Date"))
        strDateEnd = DateValue(InputBox("Pls enter the end date:", "End Date"))
        
        Set dbCurrent = CurrentDb
        
        'Runs the appropriate query and generates the excel file
        Set qryCashFlowQuery = dbCurrent.QueryDefs("Invoice_query")
        qryCashFlowQuery("Pls enter the start date:") = strDateStart
        qryCashFlowQuery("Pls enter the end date:") = strDateEnd
        
        Set rstTable = qryCashFlowQuery.OpenRecordset()
        
        'Sets up the objects for the excel workbook and worksheets by creating a blank worksheet
        Set objXL = New Excel.Application
        objXL.Application.DisplayAlerts = False
        Set objXLWB = objXL.Workbooks.Add
        objXLWB.SaveAs FileName:=strExcelPath, FileFormat:=xlWorkbookNormal
        Set objXLWS = objXLWB.Worksheets(1)
        objXLWS.Name = "CashFlow Forecast '" + Format(Date, "yy")
        objXLWB.Worksheets.Add
        objXLWB.ActiveSheet.Name = "CashFlow Forecast '" + Format(Date, "yy")
        objXLWS.Activate
        objXLWB.ResetColors
        
        'Checks if the recordset is empty. An attempt to copy data from the recordset is carried out
        'only when the recordset is not empty
        If (rstTable.RecordCount > 0) Then
            Set rngCells = objXLWS.Cells(2, 1)
            rngCells.CopyFromRecordset rstTable
            Else
            MsgBox "there isn't any record between the dates."
            GoTo Exit_CashFlowFormat
            
        End If
        
        'Finds the max row and column count of the first worksheet
        longMaxRow = objXL.Cells.find(What:="*", After:=objXL.Range("A1"), _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
        intMaxCol = objXL.Cells.find(What:="*", After:=objXL.Range("A1"), _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious).Column
        
        
                        
    Exit_CashFlowFormat:
        Exit Sub
        
    Err_CashFlowFormat:
        Debug.Print Err.Number
        Debug.Print Err.Description
        If (Not objXL Is Nothing) Then
            objXL.Quit
            
        End If
        
        Set qryCashFlowQuery = Nothing
        Set rngCells = Nothing
        Set dbCurrent = Nothing
        Set rstTable = Nothing
        Set objXL = Nothing
        Set objXLWS = Nothing
        Set objXLWB = Nothing
        MsgBox "An error has occured, pls try again!", vbInformation + vbOKOnly, "Error!"
        Resume Exit_CashFlowFormat
                        
    End Sub
    Last edited by olimin; 03-08-07 at 05:37.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I've never seen "No Item found in this collection"
    I suspect your error message may actually be "Item not found in this collection". That usually means that you are referring to a field not named in the query.

    Another thing that seems odd is that you're passing dates stored as strings to parameters that are actually expecting dates.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rather than looking at the whole piece of code, which line is highlighted when you get the error message? And, could you confirm what the actual error message is?
    Thanks,

  4. #4
    Join Date
    Mar 2007
    Posts
    9
    well, it's actually "Item not found in this collection".

    Another thing that seems odd is that you're passing dates stored as strings to parameters that are actually expecting dates.
    Is there any other way to store date value beside using string? I've seen similiar program to mine that request dates input as its query filter, but it just doesn't work on mine.

    Rather than looking at the whole piece of code, which line is highlighted when you get the error message? And, could you confirm what the actual error message is?
    Thanks,
    when I ran the the procedure, it actually just showed a "Item not found in this collection" message box. It didn't return to the codes and point to the error lines(if any). That's why I'm confused.

  5. #5
    Join Date
    Mar 2007
    Posts
    1
    Pls check Row Name in your query. Yhis error comes when u specify an attribute which is not present in your table/query

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    One method to use when getting user input from an InputBox is to declare the
    variables as Variant. Then test it to make sure they put in the expected format using IsDate. Finally, when passing it to the parameter, use
    Cdate(varDateStart).

    Try setting a breakpoint at the beginning of your code, and stepping through until you find the line where the error occurs. Let us know which line it is.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Mar 2007
    Posts
    9
    Try setting a breakpoint at the beginning of your code, and stepping through until you find the line where the error occurs. Let us know which line it is.
    I put some break points in my codes. It showed that error at this line.

    Code:
    qryCashFlowQuery("Pls enter the end date:") = strDateEnd
    Pls check Row Name in your query. Yhis error comes when u specify an attribute which is not present in your table/query
    urm.. In my query has a column of dates... but it still showed this error.

  8. #8
    Join Date
    Mar 2007
    Posts
    9
    everyone, thanks for all your help as the problem of filtering my query from input box is already solved.

    Instead of asking for input through coding, I set two columns in my query that ask for inputs to filter my query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •