Retreiving values of a field

[UZI49]

Hi,
I want to retreive the first 5 values of a field programmatically,
the following is the code I have written so far, I dont know what to do next,
Kindly Help,


Dim dbs As DAO.Database
Dim tdf As DAO.Recordset
Dim fld As DAO.Field
Dim a As Variant
Dim size As Variant

Set dbs = CurrentDb
Set tdf = dbs.OpenRecordset("testtab")

With tdf

Debug.Print .RecordCount

Set fld = tdf!Field13

Debug.Print fld.Name

Debug.Print fld.size
Debug.Print fld.Value
Debug.Print fld.Type
a = fld.Value
Debug.Print a
End With

I want to retrieve first 5 values from fld and use them, fld.Value only
retrives the first value from the column fld, what should I do?

Thanks
Zeesh

 

[Ron Weiner]

Zeesh

I think you are working to hard. Try something like this:

Dim db As DAO.Database, rs As DAO.Recordset
Dim strSql as String

strSql = "SELECT TOP 5 Field13 as MyColumn FROM testtab"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
With rs
Do While Not rs.EOF
Debug.Print !MyColumn
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

This solution uses the Sql TOP predicate to bring back only five records
from the testtab table. It also only brings back the column Field13 (which
by the way is a TERRIBLE name). Once the recordset it is opened, the code
prints the value of Field13, then moves to the next record and loops back
until there are no more records.

YOu could add a WHERE clause and an ORDER BY to the Sql statement which
would determine which subset of records are returned from the table. Then
add the code that, actually does whatever it is that you want to do with the
first five values, an error handler, and you should be good to go.

Ron W
www.WorksRite.com

 

[UZI49]

Ron

Sorry for the late reply, Ive been busy and on the road, hence I wasnt able
to reply.
I dont want to use SQL statements over here, instead I want to have complete
access to the records, fields and their data using the DAO objects because of
the nature of work I am doing. I am thinking of something like having access
to the field data and traversing through it using the loops and indexes.

From what I understand, your code is extracting the first 5 records from the
table and groups them into a recordset again, I dont want that, I need to do
something like storing the values in 5 variables and applying my functions on
them. In other cases I need different values from different fields, the
values might not even be sequential, hence I need complete access to the data
enteries of the fields.

You are right about the field name, actually I imported the table from a
text file and the format of that file is sad. Basically the text file
consists of counter information of different BTSs of a GSM network.

Kindly help me out a little further,

Thanx
Zeesh

 

[Ron Weiner]

Zeesh

Without knowing exactly what it is you want to do it is impossible to give
you a solution that covers every conceivable scenario, but I will tell you
the solution I proposed is very flexible. You can easily change the subset
of the 5 records returned by changing the SELECT clause and adding an
appropriate WHERE and ORDER BY clause to the sql statement. In fact I
believe ultimately this will prove to be the MOST flexible way to approach a
problem like you have outlined.

I am guessing that you have little experience using Set based solutions like
those used by Access's Jet Sql. You might want to play around with the code
I provided, the Access Query designer, and the Access Help facility for a
while to get you feet wet.

The code I provided essentially did the same thing that your sample code
did. I opened a table and outputted the first 5 values it found in that
field. Consider the difference in the values returned by changing the sql
statement to "SELECT TOP 5 Field11 as MyColumn FROM testtab WHERE Field13 >
20 AND field14 = True ORDER BY field7 DESC". By making this one small
change to the Sql statement the same code processes a completely different
set of records from a different field.

Before I can provide additional information I need for you to ask a more
specific question. If you will provide more details, I will be happy to
provide possible solutions. You should provide the structure (in DDL form if
possible) for the tables involved, some sample data (using a bunch of INSERT
INTO statements), and what you expect the output to be, I will try to
provide you with a solution.

In closing I give you the same advice that Ob Wan gave Luke in Star Wars.
"Luke! Use the Power of the FORCE". When using Access the FORCE is without
a doubt its Sql language core. Resistance is Futile.


Ron W
www.WorksRite.com

 

[UZI49]

Ron

Here's what I want to do: extract all the cells of field6, field13 and
field8 which correspond to the cells "(6,1)" from field2, now for each cell
from field8 (which consists of integers from the range of 1-32) of the
resulting table,
do the following:

***** {
if the cell contains "1", select the corresponding cell from field15
if the cell contains "2", select the corresponding cell from field17
if the cell contains "3", select the corresponding cell from field19
if the cell contains "4", select the corresponding cell from field21
and so on...
}


The resulting table would consist of 5 fields namely: field2 (counter
number), field6 (Object name), field8 (no. of neighbours), field13 (counter
description) and a field named "counterString" which will consist of cells
from the ***** procedure (look above).

Now from the resulting table, check if the cells in field6 consists of
redundant data,
if yes then for the redundant data, add their corresponding counterString
field cells to make each cell of field6 to correspond to one unique object
(primary key).

Except for the fourth paragraph, I have done the rest through writing a
query in the design view. Kindly tell me how I can implement the procedure of
the fourth paragraph either programmatically or through queries.

I know a bit about writing SQL queries and from what I figured from your
preceding reply was that you wanted to know the reason why I wanted to do the
above mentioned procedures programmatically using DAO objects when I could do
them through writing queries. The reason is that firstly I don’t know how to
implement the procedure of the fourth paragraph using SQL queries and
secondly and more importantly: I want to implement the procedures
programmatically. I want to have a taste of having access to my data
programmatically (through VBA) using objects.

Please tell me how to implement the procedure of the fourth paragraph on the
resulting table from the procedures of the paragraphs above it and if
possible, also tell me how all this can be done through objects in VBA.

Thanks a lot
Zeesh

 

[Ron Weiner]

Zeesh

If you can provide the structure of the "resulting table" and some sample
data illustrating the redundant data problem, and what the desired output
would look like based on the sample data provided, then I will be happy to
provide you with a query that do the deed.

In the mean time you may want to look at implementing a "Totals" query that
will allow you to GROUP BY on field6 and perform a sum() or some other
aggregate function on the "corresponding counterString" columns. The Sql
might look something like:

SELECT Field6, Sum(Field1) AS SumField1, Sum(Field2) AS SumField2
FROM tblYourTable
GROUP BY Field1

The above query would turn this data:

Field6 Field1 Field2
2 3 4
3 3 4
2 4 5
3 5 6

into this:

Field6 Field1 Field2
2 7 9
3 8 10

If this is not the kind of transformation you are interested in then, I am
completely confused with your explanation.

Ron W