I am building a database with about 50 nature reserves and all the animals that you can find in these different reserves. I made three major tables, one with the nature reserves, one with the animals, and a third where I created the links between the animals and the reserves. I also managed to build a query which shows me which animals you can find at a certain reserve.
When I make a parameter-query, I have the possibility to type in the name of the reserve, and as a result I get all the animals you can find in that reserve. So far so good.
But what I would like to get for the end-user of this database is the following:
Somehow I would like the end-user to get drop-downlist with the names of the reserves. When he chooses one from the list, the parameterquery should be executed and the result presented in a report.
Is it possible to build a parameter-query in which you do not have to enter the parameter, but you can choose the parameter from a list? Or is there another way to tackle this?
I have a form with option group (two option buttons) and date fields (to select a date range). The form should pull/pass parameters from the query. There's a form button that generates a report based on the query.
Issue: I can't figure it out how to link option buttons and date range to the query so when the button is clicked it generates the report with chosen criteria. The form is for the user to enter parameters.
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
I am using an Access database for a foreign language dictionary project.
One of the forms I use is populated by a query (qryLemmaTable) to retrieve information from tblLEMMA.
This form works precisely as I intend it to work, but there is a glitch on it that is driving me mad.
The form in question has an option group with 11 radio buttons that correspond to the parts of speech associated with each word in tblLEMMA.
When I click on the button optAdjective I apply a filter and the only records that are displayed are adjectives. (The same applies for Nouns, Verbs, Prepositions etc.)
Here is the glitch: even though the radio buttons apply the filter correctly, no “black spot” appears in the button. Other signals on the screen let me know what word type is selected, so I can accomplish what I want, but I want the “black spots” too!
Perhaps this will be a clue: For each radio button I use the following code in the GotFocus event. Me.FilterOn = False Me.Filter = "wordtype = 'A'" ‘(or N, etc) Me.FilterOn = True
And this for the Lost Focus event Me.FilterOn = False
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.* FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct] WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
I have several comboboxes on forms that are based on a query which checks the selection of an option group from other form (all are open in same tab container). However, the query stills prompt me for parameter, which tells me it's not pulling the value though there is a selection already made in the option group, and I'm sure the path is correct.
Does anyone know how to make sure that the query "remembers" the value? Would it be better done using a public variable? (I don't know if SQL is capable of accessing a public variable, though)
SQL: SELECT tblCounty.CountyID, tblCounty.County FROM tblCounty WHERE (((IIf([Forms]![frmMainForm].[Form]![frmOption]![optOptions]=4,[tblCounty].[OfficeID]>0,[tblCounty].[OfficeID]=[Forms]![frmMainForm].[Form]![frmOption]![optOptions] Or [tblCounty].[OfficeID]=4))=True)) ORDER BY tblCounty.County;
(The logic is that if you select a office location, the combobox will then filter to only counties that office covers plus two generic placeholders in "4", or allow you to select all available counties if you choose so).
I am developing an Event Management DB for business training courses. I have an attendees table with a link to CompanyNames. I wish to be able to produce an attendees report based on the CompanyName ie all attendees from the selected company. I have done this by using a dropdown of companynames on a Criteria selection form. This works well. What I would also like to do is print the same list without the companyName criteria. I am aware I could make another query but was hoping that maybe there is a way of setting up the criteria in the companyName field so that it could accept either a company name or ALL company names.
I had to modify how I was storing data on one of my tables. I changed checkboxes to an option group with the values ranging from 1 to 4.I need to count each of these entries for a report which will run them into another formula.I am trying to use the below - obv I'm doing something wrong.
Code: Police: Sum(IIf([OType],1,0))
And this is the original SQL for my queries if needed:
qry_BLT_RCCounts1
Code: SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth FROM tblBlotter
I have a main form named as FOTOFILM and a child form named as FOTOFILM_Subform ( Datasheet view, and datasource is a query builted on FotoFilm_table). they don't have relationship but the datasource for both is the same (FotoFilm_table).On the main form FOTOFILM, I have an option group named optChoose and 5 options within. Options are;
The last week, The last month The last 3 months The last 6 months the last 12 months
I would like to query / filter FOTOFILM_Subform by clicking any option.The field name ( to be filtered) IstekTrh (Date/Time-ShortDate)
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
Is there any way to control the "show" criteria in a query from option groups... i.e... a check box for someone to say if they want to view items in the query or not? (But not to affect the actualy query data in any way).
Hello, I am not sure what cateogory to choose for this question but I would greatly appreciate your help.
I have 5 buttons in my option group: 1. very often 2. often 3. occasionally 4. rarely 5. never
I have to produce a report using description of each button in my report, not numeric value. I heard something about global variable but I am not sure how to do it.
I have a query that i need to run from a form. From form I need to pass criteria
1 for canada 2 for us 1 and 2 for us and Canada
The criteria needs to go into field Pricing Type. If user selects 1 then only Pricing Type 1 will display. If user selects 2 than only Pricing Type 2 should display. I user selects both , Pricing Type 1 and 2 should be listed.
We have a query that calculates the cost total of three different types of costs between three different parameters. I now need to run a query that prompts the user for the days currency conversion rate to create a new report. I don't want to permanently convert the original fields, it is purely a monthly report where when the report is run is shows both British £ and USD.
I used an option group to classify a record in my database. It works great for this purpose, but I am at the point now of creating a query, and I would like to query based on these classifications (or options). Is this possible to do?
Also, for the option buttons, the value is numeric. When I create the Query (or a report off of that query) I would like the actual text values to display, not the data value Access is using.
I have a query that has 2 fields of currency labour rates (current rate and baseline rate), but sometimes there are some blanks in either of the fields and is causing my text field which has a Sum formula (Rates x Hours) to #ERROR and throws out my other calcs. The only way to find out which blank line items are throwing my query numbers out is to manually type in "Is Null" in the criteria.
However, I would like to keep this process fully automated so I tried to create 2 lots of option buttons for my form, one for each of the 2 fields of currency labour rates and after I click on either option button and then run my query it will show all the lines that have blanks. I was able to link 2 text boxes to the option buttons (Select Case - Case 1 - Me.txtBox1.Value = "Is Null") and then link the text boxes to the criteria field using [Forms]![frm_labour]![txtBox1]. However, none of this seems to be driving my criteria in my query.
how i would filter a form using an option button. E.g i want to be able to click an option button that is called Filter user and it will filter the query based on who is logged in? and a Filter all option that will clear the filter and show all records?
I need to pass a criteria to a query from my option group control to my query.
It contains three options 1,2 and 3.
If option 3 then Pricing Type 1 and 2
How do I make the code below working?
IIf([Forms]![FrmUserSelection]![PricingType] Like 3,([dbo_AGPricingDiscounts].[PricingType])="2" Or ([dbo_AGPricingDiscounts].[PricingType])="1",",[Forms]![FrmUserSelection]![PricingType]")
I have a table called "Workorders" that I have created a split form for and have customised it so that it had combo boxes etc to enable the user to change the record data.I then needed to add some existing queries to the form so that I could calculate some totals from records in a few other tables. This all works fine EXCEPT that I can no longer change any of the fields in the records.
I am sure that it is something to do with the fact that the 3 queries I added to the form use the "Grouped by" option and based upon my Googling, this is probably the issue.Here is one of the queries:
SELECT DISTINCTROW [Workorder Labor].WorkorderID, Sum([BillableHours]*[BillingRate]) AS [Labor Total] FROM [Workorder Labor] GROUP BY [Workorder Labor].WorkorderID;
I need to have the totals and I also need to have the ability to change the data..
How can I check the Parameter query interval is out of range? Details:
I have a table with Date column. With parameter query(by Date field) I extract records between two dates. How could I make a check when taping in Inputboxes, am I or Not Between the Last and the First Date?
I'm writing a query which when run will prompt the user to enter criteria. In this case it is a workers id (like 000UA or 000UB)
My problem is I would like to have the ability to enter more than one criteria. What I want to do is to have the ability to enter several worker id's in the criteria field, like 000UA, 000UB, 000UC, 000UD, 000VA, 000VB, 000VE, 000VF, 000WA, 00WB
Is this possible and can someone explain to me how to do it?
