Rows/Columns Looping

[mrszw]

I have the following table in my access database, how do i identify the latest month with value?

I am thinking of using VBA to do a Do until loop starting from the last row, and identifying the row where the value is not 0, after that, i will be able to identify the corresponding month
However, i have no idea how to do this in access vba, pls advise

Would appreciate any advises. Thanks!

ValueMonth0Jan0Feb0Mar1707Apr1825May0Jun0Jul0Aug0Sep0Oct0Nov0Dec

 

[gemma-the-husky]

i dont understand the row you show as

ValueMonth0Jan0Feb0Mar1707Apr1825May0Jun0Jul0Aug0Sep0Oct0Nov0Dec

but in general, simply have a query, sorting the data in descending data order

the top item will be the latest item, and a dlookup (among other techniques) will retrieve any aspect of this item.

 

[mrszw]

sry, the table is meant to be as such


i am not very sure about the sorting and dlookup method, because i am not looking for the month with the highest value.
What i am looking to do is to identify the latest month that have a value that is not 0.

Pls advise if my understanding is incorrect

Is there any way to loop through table rows/columns using VBA in access? Much like how excel VBA allows looping through range/cells.

 

[gemma-the-husky]

it depends what you are actually storing in your table, but the trouble is that this is not normalised, and comes from spreadsheet thinking

what you should realy have is a normalised 2 column table

Jan 0
Feb 12
Mar 26
apr 0
May 0

etc

or better still, the month end (or start) date

(UK style)
1/1/09 0
1/2/09 12
1/3/09 26
1/4/09 0
1/5/09 0

then all this is really easy.

--------------
what does your data represent?

 

[mrszw]

i am not sure how normalize could this table becomes.

The original table before was a 12 cols table detailing the months (Jan - Dec). On the advise of RuralGuy, i "normalize" it by using a union query to transpose the table into a 2 columns table shown below.



However, i am still unsure on how to identify the latest month that has a value..

This table contains running month by month data where a new col(month) will be populated with value monthly.
I am currently using a form and combo box to select that particular month, but would like to automatically locate the latest month with values.

 

[gemma-the-husky]

surely you ought to use a control variable to find the next month for updating - and not jkust search for a non-blank field.

more efficient, and far safer anyway