Split an array field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a linked table to Access that consolidates sales history into 12
entries within a single field. I would like to query this table based on a
date range - say July 2003 through September 2004. In order to do this I need
to sum the proper elements of the field. Is there a fairly simple way to
achieve this without creating new tables where the data is broken out into
individual columns? I am using Access 2K3.
 
See what problems bad design can create? Twelve values stuffed into one
field. Whomever designed that should be stuffed in a bag and thrown in a
field.
Enough soap box, let's solve the problem:

So how are the 12 elements delimited, in other words, how do you pull out
one value and know what it represents?
All you need to do is create a query and break the individual fields out
into the query. For each of the columns, instead of putting a field name in
the query builder, use an expression that will give you the value you want.
 
No, there really isn't a good way to do what you want, but even if there
was, you're FAR better off fixing the data model so that it doesn't store
multiple values in a single field.

Not only are you storing multiple values in a one field, but you're hiding
data (the date to which the particular value is related). You could use the
Split function to break your twelve entries into an array, but how do you
know that the first element of the array corresponds to July, 2003 and the
12th element to September 2004 (especially since July 2003 to September 2004
represents 15 months!)
 
The elements are comma delimited. My filed could have the following data:
1,2,3,4,5,6,7,8,9,10,11,12

Element 1 represents the qty sold of an item in January, Element 2 qty sold
in Feb etc. The year is specified in it own field. So for the last 5 years of
sales I would have 5 records, each record having 12 qty elements. I believe
the table was created this way to save space and increase response time by
limiting the number of records needed to query. The table is linked to my ERP
program which means no changes to the table design.

My question is How to write the query? I'm not sure on how to specify the
elements I need.
 
That at least makes life easier. The Split() takes a delimited value and
loads up an array with eacy individual value. Roughly, and this *is* of
the top of my head...

myArray() = Split([string], ",")

should do it.
 
Here is a function you can call from your query to split the data into months:

Function SplitIt(ByVal lngNdx As Long, ByVal strSplit As String) As Long
Dim varaSplit As Variant

varaSplit = Split(strSplit, ",")
SplitIt = varaSplit(lngNdx)
End Function

You would call it in your query by using expressions instead of fields in
the query builder. For example purposes, we will call the field with the
values in it HIST_VALS

So, Jan: SplitIt(0, HIST_VALS) | Feb: SplitIt(1, HIST_VALS)
and so on for all the months.

This will split the data out into a query for your. That is the easy part.
The fun part will be excluding the months you don't want. That you will
have to do in the criteria row of the query, but you run into the problem as
in your example, you can't exclude January because you have January in one of
the years. I will leave that to you.
 
Klatuu,

Sorry, I'm having some conceptual problems still. I'm fairly new to the
programming aspect so bear with me. I placed the SplitIT function in a module
(named SplitIt) and declared it as a public function. However when I try to
use it in my qyery I get the following message: Undefined Function SplitIt in
Expression

Am I putting the code in the wrong place or am I missing some sort of
declaration to make the code run.

Thanks,
 
Never mind the last post, I got it to work. I guess I didn't put into the
module exactly like Access wanted me too. When I went back & did an insert
Function it worked correctly.
 
Try opening up the VBA editor and in the Immediate Window type
?SplitIt(3, "2,6,7,10,11")

You should get 7 as the return value. (Unless Split() is zero-based in
which case you'll get 10, can't remember.)

The statement above ?SplitIt([]) doesn't work in the Immediate Window,
the problem is with the Sub, if it works the problem will be with how
you're using it in the query WBTW would be SplitIt([position of value],
[list of values])
 
While I see you've solved your problem, I thought I should point out that
you cannot name the module the same as routines that are within it. In other
words, since the function name is SplitIt, the module cannot be named
SplitIt.

Many of us use a naming convention, making all modules start with bas or
mdl, to avoid this problem.
 
Description

Returns a zero-based, one-dimensional array containing a specified number of
substrings.

See Chapter 14 of my road trip novel.

David C. Holley said:
Try opening up the VBA editor and in the Immediate Window type
?SplitIt(3, "2,6,7,10,11")

You should get 7 as the return value. (Unless Split() is zero-based in
which case you'll get 10, can't remember.)

The statement above ?SplitIt([]) doesn't work in the Immediate Window,
the problem is with the Sub, if it works the problem will be with how
you're using it in the query WBTW would be SplitIt([position of value],
[list of values])

Bill said:
Klatuu,

Sorry, I'm having some conceptual problems still. I'm fairly new to the
programming aspect so bear with me. I placed the SplitIT function in a module
(named SplitIt) and declared it as a public function. However when I try to
use it in my qyery I get the following message: Undefined Function SplitIt in
Expression

Am I putting the code in the wrong place or am I missing some sort of
declaration to make the code run.

Thanks,

:
Click to expand...
Click to expand...
 
Change the name of the module. The name space for module names and functions
and subs is shared. I would just add mod to the front of the module name and
make it "modSplitIt"
 
Back
Top