Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    62

    Unanswered: Dlookup / Switch / or combo box!!

    Hello all!
    I've come to a bit of a problem with a query and not sure which to choose from the above so any advice would be great. I'll try and explain what I want..

    tblanimals
    fieldname Strain
    fieldname Age
    fieldname etc...

    At the end of the year I need to do accounts and the way it is produced means that each entry gets converted to a number. Most of the entries are converted by a combo box, set the column with the number in as it is entered in the form so that is fine. I then use a query to sort out the columns as I need them, groups then and spit out a report.

    However, on this report and for the main query I need the fieldname Strain to produce one field in the query as the Strain and another field in the query as a number. So I used switch i.e.

    Switch(tblanimals!strain = "Freisan";1; tblanimals!strain = "Jersey";1; tblanimals!strain = "Black";3 and so on.

    This works fine. However, if I need to add more criteria then it's not very easy and I think there is a limit to the number of criteria I can put in.

    So then I thought I would make a combo box like before, but I can get two different query fields from one field source.

    So then I looked into Dlookup to do the following:

    DLookup("[Number]", "[tbllookup_strain]","[tbllookup_strain]![Strain]" = " [tblanimals]![Strain]")

    So the only way I can get it to work is with Switch but if I had a combo box I could add in more as I go....what to do!?!

    thanks in advance....I have browsed the forum also but I think it is making me more confused...and sorry this is so long!
    Sue

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is data. Don't put it in code - get it into a table. You are then just using a SQL join to return the related data.
    http://weblogs.sqlteam.com/jeffs/arc...2/10/9002.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Posts
    62
    thanks for this. I've looked at your post and it is very interesting.

    I've made a table with the criteria set out. I'm having trouble putting in my query.

    I have made the following:

    tbllookup
    fieldname Strain
    Fieldname number

    query
    fieldname tblanimals.Strain
    fieldname tblanimals.grouptitle

    SELECT tblanimals.Strain, lookup.Number
    FROM tblanimals INNER JOIN lookup ON tblanimals.Strain = lookup_strain.Strain;

    The query comes out but the number is not linked to the group title. Which I need. How can I do that? Otherwise the numbers don't fit with each grouptitle.
    thanks
    Sue

  4. #4
    Join Date
    Apr 2004
    Posts
    62
    I@ve sorted it now thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •