Get value (1 Viewer)

Mihail · Jan 22, 2011

This is my first post heare so...

Hy all !

I know: for you is a very simple question, but for me, a newbe, is unsolved.
Look: I have a table (or a query) with some data.
How extract the value from one cell in a certain field and put it into a vb variabble ? How I "scan" a field cell by cell ?
Hope you understand my english.

Thank you very much !

Rabbie · Jan 22, 2011

Welcome to the forum!

All you need to do is to open a record set containing the data. Then find the required record and then just load the vb variable from the record.fieldname.

Mihail · Jan 23, 2011

Thank you for the reply, Rabbie.
I'll work around.

My first problem is the VBA code for Acces, not the logic way.
I "know" VB and VBA for Excel. Now I wish to programme for Acces.
And I need to learn quickly.

Thank you again !

Rabbie · Jan 23, 2011

VBA for Access is identical to VBA for Excel

lmonroe · May 19, 2011

Rabbie said:
Welcome to the forum!

All you need to do is to open a record set containing the data. Then find the required record and then just load the vb variable from the record.fieldname.

Can you be more specific on how to load a vb variable from a record? I can't seem to figure it out...please help!

Mihail · May 20, 2011

Imonroe !
One working version (I think is not the best) is:

Code:

Dim DB As DAO.Database
    Set DB = CurrentDb()
Dim SQL As String
    SQL = "YourSQL"
Dim Rst As DAO.Recordset
    Set Rst = DB.OpenRecordset(SQL)

'Or base Rst on a table
    Set Rst = DB.OpenRecordset("TableName")
'Now, assuming you have in Rst a field named "FieldToScan", do the following:
    Rst.MoveFirst
    Do While Not Rst.EOF()
        Debug.Print Rst!FieldToScan
        Rst.MoveNext
    Loop
    Rst.Close
'Clean the memory
    Set Rst = Nothing

Good luck !

lmonroe · May 24, 2011

Hmm...is there any way to do this in a report? I'm just trying to return a single value from a table to show in the report.

More specifically, I want to include in my reports that last day that the database was updated. So if there is another way to do this....

Rabbie · May 25, 2011

Write a query to find the latest update date. Open the query with OpenRecordset and then read the value and store in your report.

spikepl · May 25, 2011

I'm just trying to return a single value from a table to show in the report.

use the DLookUp or DMax function to get one value from a table. Look them up in Access documentation

Rabbie · May 25, 2011

spikepl said:
use the DLookUp or DMax function to get one value from a table. Look them up in Access documentation

This is the best solution for this.

lmonroe · May 25, 2011

Thanks for your help guys!

Sounds very useful, but I am having a bit of trouble writing this...

I need it to look in my tblPrioritazationScoreboard
under the column As_of_Date
and return the first value in the column which is a date.

How would I write this with DLookup? I don't understand the arguments very well.

boblarson · May 25, 2011

lmonroe said:
and return the first value in the column which is a date.

What do you mean by this? How do you define the first value in the column which is a date? How do you determine which is the first? The first date entered? The earliest date? It matters because data in the tables are not stored in any particular order. You have to have some means to apply order by using a query. The data isn't stored in the table in a first in type of way, even though it may APPEAR to do so. So, that is important to note and understand. When you "open a table" you are not looking at the table but you are looking at a VIEW (or query) of the data (albeit a system query) and there is no order applied. If you use the Sort Order in the table it only affects the view when you open the table to look. It does not affect the actual table data and what happens when you query it out, use it in forms, reports, or formulas.

lmonroe · May 26, 2011

Ah! Sorry for the confusion! The first value in the column is the ONLY cell in the column that contains data. I was just trying to clarify that the data happened to be a date. (shouldn't matter though right?) I hope that clears that up.

boblarson · May 26, 2011

This should work for you:

Code:

=DMax("[As_of_Date]", "tblPrioritazationScoreboard")

lmonroe · May 26, 2011

It works!!! Thanks so much!!!

Mihail · Jul 17, 2013

This are my first question that ever I asked about Access.
Now I can quote the answer that sent me away from this forum for more than two years:

Rabbie said:
VBA for Access is identical to VBA for Excel

Get value (1 Viewer)

Mihail

Registered User.

Rabbie

Super Moderator

Mihail

Registered User.

Rabbie

Super Moderator

lmonroe

Registered User.

Mihail

Registered User.

lmonroe

Registered User.

Rabbie

Super Moderator

spikepl

Eledittingent Beliped

Rabbie

Super Moderator

lmonroe

Registered User.

boblarson

Smeghead

lmonroe

Registered User.

boblarson

Smeghead

lmonroe

Registered User.

Mihail

Registered User.

Similar threads

Users who are viewing this thread