Re: Decode field and generate continuous subform records

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
>
>
>
.

Relevant Pages

  • Re: Abbreviation List Tables Design, aka OTLT
    ... each lookup "file" is a separate item in a master file of files. ... > just un-normalized OTLT. ... Your ID can be the name of the file with the codes being associated ... It is almost always dismissed as a terrible design by ...
    (comp.databases.pick)
  • Re: nss_ldap and openldap on the same server.
    ... Look for Status codes and Actions ... Reading getgrouplistturns out the following: ... The getgrent() and getgrent_rfunctions make no attempt to suppress duplicate information if multiple sources are specified in nsswitch.conf. ... So after following the way through all man pages, it turns out that the behavior is fully correct as a lookup is done to find out all groups to which the specified slapd user belongs to. ...
    (freebsd-questions)
  • Re: Abbreviation List Tables Design, aka OTLT
    ... instance of any type of lookup code is a separate item in a file. ... Maybe that's just un-normalized OTLT. ... How do you select all states vs all status codes? ... It is almost always dismissed as a terrible design by relational or SQL folks, likely for good reasons if you are thinking in terms of constraints on attributes. ...
    (comp.databases.pick)
  • Re: "code" tables?
    ... "We are planning on creating 'account opening sources' lookup table to ... 'account source' codes and corresponding descriptions. ... The desire to conceal complexity is not the same as the desire to remove ...
    (comp.databases)
  • Re: nss_ldap and openldap on the same server.
    ... Look for Status codes and Actions ... Reading getgrouplistturns out the following: ... The getgrent() and getgrent_rfunctions make no attempt to suppress ... behavior is fully correct as a lookup is done to find out all groups to ...
    (freebsd-questions)