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.
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.
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
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.