Display a pattern of Grid Addresses in MS Access

[robboll]

Given (A 10 X 10 Grid) as follows:

---A--B--C--D--E--F--G--H--I--J
1|ab-ac-ad-ae-af-ag-ah-ai-aj-ak
2|al-am-an-ao-ap-aq-ar-as-at-au
3|av-aw-ax-ay-az-ba-bb-bc-bd-be
4|bf-bg-bh-bi-bj-bk-bl-bm-bn-bo
5|bp-bq-br-bs-bt-bu-bv-bw-bx-by
6|bz-ca-cb-cc-cd-ce-cf-cg-ch-ci
7|cj-ck-cl-cm-cn-co-cp-cq-cr-cs
8|ct-cu-cv-cw-cx-cy-cz-da-db-dc
9|dd-de-df-dg-dh-di-dj-dk-dl-dm
0|dn-do-dp-dq-dr-ds-dt-du-dv-dw

I am looking for a function that, when you select a specific grid
(e.g., cd), it displays the entirety of the grid values in a spiral
pattern around the starting grid as follows:

cd
bt
bu
ce
co
cn
cm
cc
bs
bi
bj
bk
bl
bv
cf
..
..
..

The function should also account for the edge of the grid. For
example, if you select (e.g., by) it should display the results:

by
bo
ci
ch
bx
bn
bd
be
cs
cr
cq
cg
bw
bm
bc
as
at
au
oc
..
..
..

Does anyone have an idea of how to go about this using VBA?

Thanks for any assistance.

RBollinger

 

[Duane Hookom]

This sounds like an interesting exercise in programming but perhaps one that
might take a while. You might convince someone that their time investment is
worth it if there was a reasonable purpose to this.

 

[David C. Holley]

I would implement this using an Excel worksheet. (Implementing this in
Access would be MUCH, MUCH more difficult, if NOT impossible.) The
nature of Excel, however would easily allow you to obtain the values
above, below and to the right and left of any given value. If you need
to pull the data into Access, it would be simple enough for Access to
reach out to Excel and gather the information. Excel allows cells to be
referenced in to different manners - A1 (Letter for Column/Number for
Row) and R1C1 (Number for Column/Number for Row). Using the R1C1 method,
it becomes a matter of incrementing or decrementing the row and/or
column to obtain the values surrounding the target. To allow for
borders, I would go so far as surrounding the grid with values that are
easily identified as invalids - 99, XX, INV.

 

[Tom Lake]

I would implement this using an Excel worksheet. (Implementing this in
Access would be MUCH, MUCH more difficult, if NOT impossible.) The

Huh? A 2-dimensional array would be just as easy to address as an Excel
worksheet.

DIM Board(1 To 10, 1 To 10)

To access an element of the board such as the 4th element of the 2nd row:

Board(2, 4)

The elements around it are:

Board(1, 3), Board(1, 4), Board(1, 5)
Board(2, 3), Board(2, 5)
Board(3, 3), Board(3, 4), Board(3, 5)

Tom Lake

 

[David C. Holley]

I suggested Excel, because you have to first store the data. Yes a 2-D
Array would work, however I wouldn't want to begin designing a way to
capture the data. The Array would have to be loaded from somewhere.
Additionally, while you could move around the 2-D array you would have
to ensure that the data is loaded in a specific order (which I'm
assuming would be left to right, top to bottom) which would be another
layer of logic. Also, I'm assuming that the data would initially begin
in Excel to begin with to ensure that the values fall in the proper
physical position before even bringing it into Access.

 

[robboll]

I didn't think this would be an easy task using Access. This isn't for
a project, but just a programming exercise. I was able to do it back
in 1996 using xbase (i.e. dbase IV) using an array. I just don't know
the array syntax for MS Access.

RBollinger

 

[David C. Holley]

Dim aMyArray() as Array

and then

ReDim aMyArray(x, y) to get the 'physical' dimensions.

But if you're doing as a programming exercise, I would still go with
using Excel, because you'll have to figure out how to get Access to
reach out and manipulate Excel which IS a real-world exercise.