04-02-14, 20:10 #1Registered User
- Join Date
- Apr 2014
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:
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!
04-02-14, 21:20 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
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:
WHERE (Trips.MonthlyNumber=[Forms]![NavigationPane]![MonthlySelection]) AND (Travelers.GovContractor <> IIf([Forms]![NavigationPane]![ContractorSelection]=3,0,[Forms]![NavigationPane]![ContractorSelection]))Have a nice day!
04-03-14, 03:21 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
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
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 thereI'd rather be riding on the Tiger 800 or the Norton