Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem (Access 2003)

    I have a table that has fields that hold a schedule of upcoming class information. (Just about the class, like classrefno, start date, end date, days of class and fees)

    One field in this table [classrefno] is a lookup field, combobox with two columns and is a number field. It looks up a class by ID Number and Class Name from another table (tblClassesByDirector). And this invoked query on this table (for the lookup field) is sorted by classname. A person can look up the classname, but the classref number is placed in the field instead of the class name. I did this in order to use this field in a report later (I needed a number field to do a count on how many students are registered).

    But now....

    I need a way to create a parameter query on this ClassesScheduled table with the ability to type in a classname into the parameter box, but the bound field in the combo box in this field I'm trying to query has a number datatype.

    e.g. my combo box in this table might look like this with the first column being a classid field:

    90 Diesel Engines
    127 MS Office
    136 Auto Mechanics

    At this time, when I do a parameter query on this field in the table, I have to type 90 to get Diesel Engines to show up. How can I make the parameter query look at the second column in this combo box within the table instead of the first?

    Thank you,
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Problem (Access 2003)

    Use a form with a combo box to enter the parameter. The combo box can have two columns, with column widths 0";1" to hide the first (number) column.

    Replace the parameter [Enter Class] (or similar) with a reference to the combo box on the form:

    [Forms]![NameOfTheForm]![NameOfTheComboBox]

    This will only work if the form is open. If you open or use the query while the form is not open, you'll be prompted to enter a value for [Forms]![NameOfTheForm]![NameOfTheComboBox].

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query Problem (Access 2003)

    Short answer: It can't look at the 2nd column in the lookup combo box.

    So, you have to add a join to that lookup table in your parameter query, and add the ClassName field to the query. You can then make it the parameter.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access 2003)

    Easiest...I just made another lookup column next to the existing field and put class name first instead of class no in the combo lookup. So yes, someone has to choose a class number in the first field, tab to the second field and enter the name as well. Double-duty, but it works.
    I tried the form...kept coming up empty.
    The join thing was also off, as this database (which should have been based around classes, was built around directors...albeit I now have a lot of work arounds) and class name was no where else where it could be joined in the query correctly...as the relationships didn't warrant it. But thank you.
    This is the dangdest database I've ever put together...rather patched together...am not proud of it at all, but it works.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Problem (Access 2003)

    In similar situations I have used a seperate table with two fields, for example, Code (90) and Description (Diesel Engines). I then join the table by the Code in the query and use the Description field for the parameter.
    Thanks
    chuck

Posting Permissions

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