Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Unanswered: Help: I Need One Value To Represent The Two Values Below It In A List Box

    I am very new to Access but have learned a lot in a month. I do not understand VBA or SQL for the most part so I may need some explanations to be dumbed down.

    I have a query with one column that has two values—either 1 or 2 or a yes/no check box. I am using both to see if I can get either one to work. A little background - 1 (or checked) would mean this person is a contractor and 2 (or unchecked) would mean a government employee.

    That one column has a criteria linked to a list box in a form using the "Build..." tool. It looks like this in the criteria box:
    [Forms]![NavigationPane]![ContractorSelection]

    That query is linked to a report so when I select either Yes/No or 1/2 in the list box and click the corresponding command button that report when opened will only show the contractors or the government workers. I want a third option in the list box though. Both. How can I make it so if I select that third option in the list box it will include both contractors and government employees?

    I tried having the table column with the corresponding values be formatted to text with the values "1", "2", and "1 or 2." I figured this would work because if I replace "[Forms]![NavigationPane]![ContractorSelection]" in the criteria field with "1 or 2" it works. But if I use the build tool to fetch it from another table, it doesnt. It works for just contractors or government workers, but not both.

    I do not want to have to create one report for both categories and another report for either/or. I'm fine with making another query but since a report can only be connected to one query (I think) I don't see how that can work.

    I'm not sure if it helps, but here is the SQL for the query:

    SELECT Trips.TripID, Travelers.Traveler, Divisions.Division, Trips.DepartureDate, Trips.ReturnDate, Trips.MonthlyNumber, Trips.Destination, Trips.Cost, Trips.[TripCancelled?], Trips.Purpose, Trips.Justification, Travelers.[GovernmentContractor?], Travelers.[GovContractor?]
    FROM Travelers INNER JOIN (Divisions INNER JOIN Trips ON Divisions.[DivisionID] = Trips.[DivisionID]) ON Travelers.[TravelerID] = Trips.[TravelerID]
    WHERE (((Trips.MonthlyNumber)=[Forms]![NavigationPane]![MonthlySelection]) AND ((Travelers.[GovContractor?])=[Forms]![NavigationPane]![ContractorSelection]))
    ORDER BY Trips.DepartureDate;

    A huge thank you to anyone taking the time to help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution.

    1. On the form, replace the CheckBox control [ContractorSelection] with an Option Group control with the same name.

    2. In this Option Group, place 3 Option Button controls, like this:
    a) Name: Option_Contractor, Option Value: 2
    b) Name: Option_governmentemployee, Option Value: 1
    c) Name: Option_Both, Option Value: 3

    3. Change the WHERE clause of the query to:
    Code:
    WHERE (Trips.MonthlyNumber=[Forms]![NavigationPane]![MonthlySelection]) AND 
          (Travelers.GovContractor <> IIf([Forms]![NavigationPane]![ContractorSelection]=3,0,[Forms]![NavigationPane]![ContractorSelection]))
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the boolean (yes/no) datatype to store the values*
    use a checkbox to display the values
    rolling your own version of boolean values is daft, its extra work, its a maintenance problem over time, it makes your code less easy to read and understand (both by you at a later date or if someone else has to pick up your code).

    to select for use in a query there;s several ways
    either omit the term in the query if both are required
    or
    put the bit you need for the where clause as the first (hidden) element of the list box
    your rowsource for the list/.combo box is something like

    AND Travelers.GovContractor = true; drone
    AND Travelers.GovContractor = false; blood sucking leach
    ; all


    then pull the value of the list/combo box into the query as required. the third term is blank as nothing is needed
    your where clause becomes
    strWhereClause = "WHERE (Trips.MonthlyNumber=[Forms]![NavigationPane]! [MonthlySelection]) " & mycombobox.text

    *unless you have or think you may have more than 2 values excluding NULL. Boolean datatype columns can have three values, true (yes), false (no) or NULL assuming you allow NULLS. if you think you may need more than 2 values then use another table to define categories of person and pull the description from there
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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