Re: Decode field and generate continuous subform records
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sun, 15 May 2005 23:03:04 +0800
You could use the Split() function to parse the field into an array in code,
and then append a record to a local related table for each one. That assumes
you can work with a local copy, i.e. the data is not constantly changing.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"BobT" <BobT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:855888FE-C01D-4F25-AFCB-0E356F095359@xxxxxxxxxxxxxxxx
> Thanks for the reply. Unfortuantily the data is originally from an AS400
> and
> will not be changing in structure anytime soon. Also the codes could have
> 1
> three character code or it could have to up about 10 or so. I guess I
> could
> just start witht left$ function and then use mid$ function for the rest
> with
> an if condition. If end of field reach I could insert use a check
> character
> I guess to show that that calculated field is null........
>
> Thanks for the tip. I'll start to work in this direction...
> --
> BT
>
>
> "Allen Browne" wrote:
>
>> The only sensible way to do this is to normalize the data structure at
>> some
>> point, so that you are not storing 3 values in the one field, i.e. you
>> have
>> a related table that has 3 records instead of storing the 3 values in one
>> field. You can then join that to your lookup table and show the lookup
>> value
>> very simply and efficiently.
>>
>> If you cannot do that, and there are always exactly 3 values, you could
>> join
>> the table to itself (based on the primary key), and then parse the
>> Left(),
>> Middle(), and Right() of the field, possibly based on Instr() if they are
>> not fixed width. This ends up yielding the 3 records, and you can then
>> join
>> to the lookup table to get the text you want to display. This approach
>> will
>> be very inefficient (slow to execute).
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "BobT" <BobT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:42082816-C4EA-4936-B9CD-21F02ED2318F@xxxxxxxxxxxxxxxx
>> >I have a field on a main form. It contains a series of three digit
>> >codes
>> > that each references a separate value. I have a table that has these
>> > values
>> > along with the descriptions for each value. What I would like to do is
>> > that
>> > when the main form is opened and a record is chosen on the mainform,
>> > the
>> > field with the series of codes would be decoded and then perform a
>> > lookup
>> > in
>> > a reference table to find the description and then display each record
>> > on
>> > the
>> > subform.
>> >
>> > To do this, I have created two functions. The first function parses
>> > this
>> > field for the 3 values and then next 3 values and so on. A second
>> > function
>> > is then called from the first to perform a dlookup to the reference
>> > table
>> > with descriiptions.......
>> >
>> > How do I create record entries within the subform by displaying the
>> > final
>> > value from my dlookup function?
>> >
>> > Thanks,
>> >
>> > --
>> > BT
>>
>>
>>
.
- References:
- Decode field and generate continuous subform records
- From: BobT
- Re: Decode field and generate continuous subform records
- From: Allen Browne
- Re: Decode field and generate continuous subform records
- From: BobT
- Decode field and generate continuous subform records
- Prev by Date: Re: Help with setting focus
- Next by Date: Locking a field after update
- Previous by thread: Re: Decode field and generate continuous subform records
- Next by thread: Startup Form
- Index(es):
Relevant Pages
|
