Results 1 to 4 of 4

Thread: How to use Excel Functions in Query

  1. #1
    Join Date
    Dec 2008
    Posts
    51

    How to use Excel Functions in Query

    You would do it in a similar manner except that in Access it's necessary to name the field rather than give the cell number. Your Excel cell would read 60*H18, 60*H19, 60*H20. It would be beneficial that anyone suggest me how to use Excel Functions in Query.

  2. #2
    Join Date
    Oct 2008
    Posts
    114

    Re: How to use Excel Functions in Query

    The string variable strSQL is filled with an SQL statement that performs the equivalent of an Access Make-Table query. Option Compare Database Public myIPT As String Option ExplicitThe SQL statement copies two fields (Firstname and Lastname) from the source table (tblStaff) into a new table (tblTemp). I haven't included a "WHERE" clause containing criteria because I want all the records copied.

  3. #3
    Join Date
    Feb 2006
    Posts
    172

    Re: How to use Excel Functions in Query

    The next example uses the Recordset property and the Recordset object to synchronize a recordset with the form's current record. This step opens the definition of my new table - think of it as opening the table in design view - and creates a new field (called RandomNumber) of the correct data type for the data I am going to put into it When a company name is selected from a combo box, the FindFirst method is used to locate the record for that company, causing the form to display the found record.

    Code:
    Sub SupplierID_AfterUpdate()
        Dim rst As DAO.Recordset
        Dim strSearchName As String
    
        Set rst = Me.Recordset
        strSearchName = CStr(Me!SupplierID)
        rst.FindFirst "SupplierID = " & strSearchName
        If rst.NoMatch Then
            MsgBox "Record not found"
        End If
        rst.Close
    End Sub

  4. #4
    Join Date
    Nov 2008
    Posts
    58

    Re: How to use Excel Functions in Query

    Unlike using the RecordsetClone property, changing which record is currently in the recordset returned by the form's Recordset property also sets the current record of the form. The new table is opened as a table-type recordset giving me access to the records it contains and allowing me to edit them. The statement rst.MoveFirst makes sure that the first record is selected before initiating a loop that will move through all the records in the table.

Similar Threads

  1. Working with NPV functions on Microsoft Excel
    By Irritator in forum MS Office Support
    Replies: 5
    Last Post: 17-02-2012, 06:45 PM
  2. Formulas and functions of MS Excel 2010
    By VIP KING in forum Guides & Tutorials
    Replies: 17
    Last Post: 04-03-2011, 10:57 AM
  3. What is Excel Statistical Functions: POISSON?
    By Steyn in forum Windows Software
    Replies: 4
    Last Post: 13-03-2010, 02:17 AM
  4. How to study Functions in Excel 2007?
    By Scaramouche in forum Windows Software
    Replies: 4
    Last Post: 05-06-2009, 03:05 PM
  5. Functions in Excel
    By Stephanatic in forum Windows Software
    Replies: 3
    Last Post: 04-10-2008, 12:40 PM

Posting Permissions

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