Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query from Access to Excel (2002 SP3)

    I thought this would be easy but apparently not!

    In the attachment, I have two files. One is a table in Access and the other is a form I need to have populated in Excel. The Access table basically keeps track of receipts for one of our departments and the cashier takes the Excel form and balances her drawer with it. What I would like to do to help her speed up this process, is pull the values for the Cash, Check and CreditCard fields from Access for the CURRENT DATE (today's date) only , have them total and filled in on the appropriate spot on the Excel spreadsheet. The cashier can then print out this form and have it blanked out, ready for the next day.

    Is this feasible because I've been trying for a couple of days and am getting various errors.

    Thanks.
    Pooja

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from Access to Excel (2002 SP3)

    There appear to be two different cells on your form for Credit Card and also for Check. Should both be filled? And with the same amount, or ...?

    (I don't understand the formulas in your form, by the way)

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from Access to Excel (2002 SP3)

    Yes, they should both be filled with the same amount (it's a double-check of sorts). As for the formulas, please disregard them! I can redo the formulas very easily....

    Thanks much!
    Pooja

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from Access to Excel (2002 SP3)

    1) Create a query qryToday in the database that totals the amounts for cash etc. for today. Its SQL is

    SELECT Sum(tblReceipts.Cash) AS SumCash, Sum(tblReceipts.Check) AS SumCheck, Sum(tblReceipts.CreditCard) AS SumCreditCard
    FROM tblReceipts
    WHERE tblReceipts.Date=Date()

    2) Open your workbook.
    Activate the Visual Basic Editor (Alt+F11).
    Set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...
    Insert a module (or use an existing one).
    Enter or paste the following code into it:

    Sub FillFromDatabase()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    ' Modify path as needed
    Set dbs = DAO.DBEngine.OpenDatabase("CBH2OReceiptsTABLEONLY.mdb")
    Set rst = dbs.OpenRecordset("qryToday")
    With Worksheets("CashierForm")
    .Range("F14") = rst!SumCreditCard
    .Range("F16") = rst!SumCheck
    .Range("F22") = rst!SumCash
    .Range("F23") = rst!SumCreditCard
    .Range("F25") = rst!SumCheck
    End With

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Modify the path to the database.
    You can assign the macro to a command button on the sheet, or to a custom toolbar button, etc.

    See attached zip file.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from Access to Excel (2002 SP3)

    Perfect as usual! Someday I want to be able to help you out with an issue!

    Thank YOU MUCH!
    Pooja

Posting Permissions

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