Queries :: Set Criteria On A Query Based On If A Form Is Open Or Not?
Jun 18, 2015
What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.
Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?
I have a subform which is a continuous form displaying a single combobox of trade names so there may be up to 4 or 5.i want to click on a button on the main form which will open a form based on a query that will display all personnel matching all or 1 of the trades in the subform?
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
On I form I have a combo box called cboMobileStatus.Its row source type is "Value List".The row source is: 1;" All";2;"Has";3;"None".I have a query based on table "tblCustomerContacts" and I want to limit the records returned based on the value of the combo box.So if the user selects:
* All (1) I want all records returned. * Has (2) I only want records that have a mobile number returned * None (3) I only want records that do not have a mobile number returned
The field mobile is text (to keep the leading 0)I thought of using the Switch function in the criteria of the query for field Mobile like this:
Code: Switch([Forms]![frmPrintCustomer]![cboMobileStatus]=1,([tblCustomerContacts].[Mobile]) Like "*",[Forms]![frmPrintCustomer]![cboMobileStatus]=2,([tblCustomerContacts].[Mobile]) Is Not Null,[Forms]![frmPrintCustomer]![cboMobileStatus]=3,([tblCustomerContacts].[Mobile]) Is Null)
but the query does not like it!Am I on the right track, and if so, how should I modify the code?
I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
Is there a way in access, through vba or any other means, which would allow me to either enable or disable criteria in a query based on the value of textbox??
Like i have a query that displays bookings customer has made between two dates, so i made a query and in the starting and ending date fields i get the value from form. In case if the use does not enter ending date, i would like to run the query with starting date only that displays booking made after the starting date.
Currently the starting date criteria is set to a textbox of form, and so is ending date. So if customer selects 1st April 2015 as starting date and 30th April 2015 as ending date the query should display the bookings between 1st and 30th of April. And if the user enters only 1st April in starting date it should display bookings starting from 1st April onward.
I want to create a query based on 3 combo boxes but have it so that if the field in the second or third combo box is not populated the query still runs.
Right now i have the Criteria set for the three columns that i wish to sort by as seen below.
This gives me the correct query result but im forced to make a selection from each combo box. Is there a way to progamme it so that if I only make a selection from the first combobox and leave the others blank i can still get results in a query?
I have a very simple query on an accounts form to show a running transaction history.
Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.
Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.
Transaction 9 will have obviously have one total, as this is a single post.
11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.
I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:
1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)
2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)
I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")
However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.
I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.
I am working on a database that tracks products. The products are divided into groups (Bath Salt, Stick Incense, Candle, etc.) and these are store in a table. The products themselves are stored in another table.
When a new scent is created, it can belong to multiple groups ... for instance, Strwberry Vanilla could be a perfume, a bath salt, a candle, stick incense.
When the form is opened, I want to read the contents of the groups table and create a check box for each entry.
I do not really want to use a list/combo box for this task ... would prefer to be able to check in a list of boxes to specify what groups to create entries for when a new product is entered.
Hi all, I have a query which populates a form called EditPatientFrm, which asks the user to enter the patient UniqueID in order to pull up the record they want to edit from the TblPatient. However, I want the user to now be able to input just one of three criteria in order to pull up the record to be edited:
UniqueID ChartNumber PatientLastName and PatientFirstName (two fields)
since they may not have the UniqueID readily available to them.
Can anyone show me how to do this? I have tried to find answer to this one under queries and forms- no avail. Sure it's a simple thing...
I sure am having difficulty with this. I'm trying to have the criteria in my form refer to a text box within one of my forms which is the text as I would write it myself within the query design view.
[calcDestZip5]'s value in QueryWizard form = "55422" OR "55343"
Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!
Oh, and for what it's worth... If, [calcDestZip5]'s value in QueryWizard form = "55422" Then the query runs just fine. It's as if the query doesn't want to accept multiple criteria from another source.
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
Table: "Facility Info" Data in the table: "facility", "city", "date", etc. Query: "Q Facility" Report: "R Facility" Form: "Main Form" is where the data is entered that goes into the "Facility Info" table.
In the "Main Form" there is a dropdown box where I can select the "facility".I would like to add a button to this form that opens my report "R Facility". But this report is a collection of all the facilities and I would like it to just report the ones for the facility that I selected from the dropdown box on my "Main Form".
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I'm trying set the query criteria from a textbox on a form. It's a Status field in the query. 1 = Open, 2 = Closed. If I set the criteria to "1" it shows all open, "2" and it shows all closed and "1" or "2" it shows all. The problem I'm having is setting these in the form. I've set the query to pull the value from the form. I can get the Open or Closed to work but not the All. My textbox shows exactly how the criteria should read "1" or "2" but doesn't show any results.
I've got a continuous form based on a query.Each of the fields have a search box below it (in the footer) which should ideally filter the query.I'm starting with the FirstName field.In the form's query, I've set the criteria to the following for the FirstName field:
Code: Like "*" & [Forms]![frmStudentDetails]![txtSearchFirstName] & "*" frmStudentDetails is the form name. txtSearchFirstName is the search box's name (in the footer). The AfterUpdate event for txtSearchFirstName is:
Code: Me.Requery
But, it doesn't work; when I switch to form view, it displays an error. The Microsoft Access database engine does not recognize '[Forms]![frmStudent Details]![txtSearchFirstName]' as a valid field name or expression.
I am trying to set up a query by form with multiple criteria.For the majority of the criteria I'm using the format: Like [Form]![formName].[txtInputboxName] &"*" .
I copied the Like (...) &"*" from someone else in order to allow for multiple, optional criteria, which does do the trick, however I don't understand why.
I now want to set up a criteria on my query to search for values between two input values to gives the records in the range e.g between 50 and 100 Following the above format, I want to put something that achieves this:
Between Like [Form]![formName].[txtInputboxName1] &"*" AND Like [Form]![formName].[txtInputboxName2] &"*"
with Inputbox1 being 50 and Inputbox2 being 100, however that code doesn't work and I don't know how to get around it as it keeps giving the 'incorrect syntax' error.
I have a table called estimates and a field in that report is called RFA and this is a checkbox. How can i open a report i created (called EReport) for all rhe records in this table that have the RFA field checked (therefore true).
Am not getting a value from a form text box when using in the criteria line in my query. Am referencing like [Forms]![FormName]![FormLabelName]. If I copy the data in the form and paste it into the query, it works fine, but if I just reference the form, I get no results.
I have table.[reconciled] tat allows for a check box. i want to enter in the criteria filed in query design that i only want to see the unchecked or false entries. i have tried writing the criteria multiple ways and i cannot get it to filter out on my sub form query.
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code: =IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
This works for the records with fields that have data.
Code: Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
