how to get the field value in VBA with the help of SQL statement and assign this value to a Variable.... and then use that in the code...?
In which appliation R U writing VBA code? MS-Access or excel?
Where is the table from which u want to read the fields from? Is it an Excel Spreadsheet or an Access Database or some other DB?
Please make some clarity in your problem and will help to solve it out precisely.
well it is MS Access 2003 and what i need is to pick the largest integer value of the field and pass this value to a variable.
more datelines are as under:
TABLE NAME: CRID_INDEX
TABLE COLUMNS: CRID. CRID_NUM and CRID_TYPE.
SELECT THE RECORD WHERE CRID_NUM HAVE A LARGEST VALUE AND ASSIGN THIS VALUE TO A VARIABLE CALLED Vtemp AS INTEGER.
HOPE THIS HELP
USE THE OBJECT CurrentDb.
Public Sub GetMaxCRID() Dim Vtemp As Integer On Error GoTo GetMaxCRID_Error 'Here first we check whether or not if any record exists in the table If CurrentDb.OpenRecordset("SELECT COUNT(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0) > 0 Then Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0) Else Vtemp = 0 End If 'Now you have the maximum number in the field CRID_NUM GetMaxCRID_Done: Exit Sub GetMaxCRID_Error: MsgBox "An error has occured in procedure GetMaxCRID." & vbCrLf & vbCrLf & "Error No: " & Err.Number & vbCrLf & Err.Description Resume GetMaxCRID_Done End Sub
Ops excuse me mr. shaikh would you please tell me one more thing why do you write Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0) this "(0)" at the end of the above statement...
Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0)
Here (0) is to return the value of the first field in the query and store it into Vtemp. If say u have more number of fields and u want to get the value of the third field in the sequence of recordset values u have to write (2) like
x = CurrentDb.OpenRecordset("SELECT sName, sLastName, sAge FROM EMPLOYEE WHERE sEmpId = 23")(2)
This will return the value of field age into variable x
OK good i got it...