RE: Split an array field
- From: "Bill Phillips" <BillPhillips@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 5 Dec 2005 14:05:02 -0800
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.
"Klatuu" wrote:
> 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.
>
> "Bill Phillips" wrote:
>
> > 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.
> >
> > "Klatuu" wrote:
> >
> > > 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.
> > >
> > > "Bill Phillips" wrote:
> > >
> > > > 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.
.
- Prev by Date: RE: Split an array field
- Next by Date: weird, hard to find Excel-automation error
- Previous by thread: Re: Split an array field
- Next by thread: Re: How to get Autonumber key thru code
- Index(es):
Relevant Pages
|
