data extraction from records of query

  • Thread starter Thread starter ms shakeel
  • Start date Start date
M

ms shakeel

hello sir

thanks in advance for the help

i have a query on the basis of a table,the query results
in 4-records

i want to use tha data in each of records of the
4-resulted records of the select query for performing some
operations like,concatinating them or extracting only the
number from each record of the results of the query etc

the data present in each records of the query are strings

but i have been unable to get each record data from the 4-
resulted records of the select query

since i have said there are 4-records obtained from the
query,how can i get each of the 4-records of the resulted
query in 4-variables like
variable1=data of record1 of 4 resulted records of query
variable2=data of record2 of 4 resulted records of query
variable3=data of record3 of 4 resulted records of query
variable4=data of record4 of 4 resulted records of query

instead of 4 variables if the all the records of the query
can be stored in a array will also be good,but i have been
achieve all this.

plz help me
thank you
shakeel
(e-mail address removed)
 
i want to use tha data in each of records of the
4-resulted records of the select query for performing some
operations like,concatinating them or extracting only the
number from each record of the results of the query etc

the data present in each records of the query are strings

but i have been unable to get each record data from the 4-
resulted records of the select query

since i have said there are 4-records obtained from the
query,how can i get each of the 4-records of the resulted
query in 4-variables like
variable1=data of record1 of 4 resulted records of query
variable2=data of record2 of 4 resulted records of query
variable3=data of record3 of 4 resulted records of query
variable4=data of record4 of 4 resulted records of query
Click to expand...

shakeel,

you can load in code a recordset with the query using the
OpenRecordset method, and then use the GetRows method to transfer the
data in an array. See also Help on GetRows.

This would be better than separate variables anyway, because the query
will not always return exactly 4 records from now on to the end of the
world :-)

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
hello
thanks in advance for help
i have used the method of "OpenRecordSet" for getting the
records but after doing that i am not able to cross check
the contents of the array, i.e i am not able to see the
contents the array in the immediate window;or in other
words plz tell me how to crosscheck the method
of "OpenRecordSet".
also i want to diaplay the result of an operation which i
would be doing in VBA on to a feild of a form. i have been
unsucessful in displaying the result of a simple operation
done in VBA in a feild of a form
plz help me how do i achieve this

thanks
ms shakeel
(e-mail address removed)
 
ms shakeel said:
i have used the method of "OpenRecordSet" for getting the
records but after doing that i am not able to cross check
the contents of the array, i.e i am not able to see the
contents the array in the immediate window;or in other
words plz tell me how to crosscheck the method
of "OpenRecordSet".
Click to expand...

shakeel,

I wrote you about using the GetRows method to copy the recordset data
in an array. You would have found in the Help that it's not a method
of OpenRecordset, but a method of the Recordset object.

This would look something like this:

'Dim a variant to store the array later
Dim varRecords As Variant
Dim rst As DAO.Recordset

'Code to load the recordset...

'GetRows needs the number of records as a parameter,
'so go to the end of the recordset to make sure
'all records are loaded and RecordCount is correct
rst.MoveLast

'Back to the beginning of the recordset
rst.MoveFirst

'Now apply the getRows method for all records
'in the recordset
Set varRecords = rst.GetRows(rst.RecordCount)

This is how varRecords will look after this code line:
- it is a 2-dimensional array, i.e. something like varRecords(x, y)
- the first index, x, designates the fields
- the second index, y, designates the rows (= records)
- both indices start with 0

For ex:
varRecords(1, 0) returns the value of the second field in the first
record

varRecords(0, 9) returns the value of the first field from the 8th
record

Please note: the order of the fields will be the one specified in the
SELECT statement used to open the recordset. If instead a table is
used, then it will be the order of the table fields such as in the
table design view.
The order of records is determined by the ORDER BY clause, if any
used, resp. the sorting order of the table if any defined.

You can calculate the number of rows were returned by GetRows like
this:

intTotalRows = UBound(varRecords, 2) + 1
also i want to diaplay the result of an operation which i
would be doing in VBA on to a feild of a form. i have been
unsucessful in displaying the result of a simple operation
done in VBA in a feild of a form
plz help me how do i achieve this
Click to expand...

This depends on the situation. Is the form control bound (i.e. should
be saved together with the other form data), should the control be
enabled for change? And when do you want to do the operation and what
would be the result, a Yes/No flag, a number?

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top