Form Field As Query Criteria
Jul 17, 2005
Hi All
Ok I have a simple problem. I want to entry value of a field as criteria of a query. Ie. I have a field on a form which is EmplyeeCategores. It's simple to use this as a a filter in a query by simply putting Forms![frmMailer2]![EmployeeCategories] in the criteria of the EmployeeCategories column in the query if there was only 1 criteria.
What I mean is, if the value of the field in form was A1 for example, it'll work and the query will bring up all emplyees who are categorised as A1. But I want users to be able to put multiple codes in the form, such as A1 or A2 or C4, and the query to bring up all the employees that fit into all 3 of these categories,
I hope this makes sense. Any help will be much appreciated.
Many thanks
Oz
View Replies
ADVERTISEMENT
Apr 26, 2006
I tried to word the title as accurately as possible.
What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.
I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.
There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?
View 2 Replies
View Related
May 11, 2013
I have a query where I prompt for a Report_Date to create a new field; Report_Date:[ Enter date for report]
Is it possible to use the result from this prompt as a criteria in a different field?
for example, Order_Date based on the criteria of <=[Report_Date]
View 4 Replies
View Related
Oct 4, 2005
I am not sure how to search for this so if there is another thread with this example please direct me in that direction. What I am trying to accomplish in my query is the following:
I am bringing data in from another source in the following table
ID Date1 Date 2
I want a query to do the following based on dates entered on the switchboard in a beginningDate field and EndingDate field
ID Date1 Date2 Final1 FInal2
If Date1 is between beginning and ending date I want it to put a yes in Final1 if Date2 is between beginning and ending date I want it to put a yes in Final2. Both can have yes'.
I tried doing an if statement in my expression as follows: IIF([Date1] between [Forms]![FrmSwitch]![BeginningDate] and [Forms]![FrmSwitch]![EndingDate], "Yes", "") but nothing comes up. My switchboard is always open with the dates in an unbound field.
Any suggestions???
View 5 Replies
View Related
Mar 24, 2005
Hello,
...I'm not sure if I'm making this question more complex or not or if it's even possible? (I would think so) Anyway...What I am trying to do is create a form in which I can input more than one criteria into a field - We will cal it txtSetValues. Then via a button open a query that will use txtCode as the criteria for searching records in a table. The
table is called: tblMASTERTABLE-EmpCount
query is called: qryEmpCount
The table currently only has two field; ORG and Name. The criteria I have in ORG is: Like [Forms]![frmEmpCount]![txtCode]
I currently can find any specific ORG just fine. I can also use the wildcard and that works fine. But how can I set it up so that I can enter in multiples... For example, I would like to find all employees that there org is either; AL001 OR AL010 OR AL100 - The wildcard will 'Almost' do it in some circumstances, but it may gather some orgs that I don't want in this list. I hope I am explaining this in an easily understood fashion?
Thanks,
---roystreet
P.S. Now I know I could just make multiple fields in the form (ie; txtCode2, txtCode3,etc) and then just add those as criteria in the query, but I was hoping that I wouldn't have to do that.
View 1 Replies
View Related
May 30, 2005
Good day everyone.
I'm designing an access system in which I have many reports (Primary Key is reportNumber)
I need a form with a field in which I can speicify a range of reportNumber to print them.
example
I need to print reports from 1000 to 2000
please note than I'm a beginner in this so I need detaild steps.
any help will be very much appreciated!
Thanks and Regards,
CS.
View 1 Replies
View Related
Jul 11, 2006
Somewhat simplistic question, but I can't seem to get it to work correctly.
SELECT Field1, Field2
FROM Table1
WHERE Field1 = [Forms]![Form1]![Text1];
Form has two quieries, named Text0 and Text1. Text0 contains the Field info for the query to search under.
How do I change it so that this will work.
SELECT Field1, Field2
FROM Table1
WHERE [Forms]![Form1]![Text0] = [Forms]![Form1]![Text1];
Presently I am getting nothing but blank queries. I'm sure its some simplistic thing but I can't figure it out atm.
View 4 Replies
View Related
Dec 8, 2006
Hi,
I have a calculated field in my query called "outstanding".
this is how the query looks:
Expr: SubFormTotal
[qty]*[price]
Discount
...[Discount]
Expr:Total
.[SubFormTotal]-[Discount]
Deposit
.[Deposit]
BalanceToDate
.
[B]Expr: Outstanding
..[Total]-[Deposit]-[BalanceToDate]
PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields
what am i doing wrong and how can i solve it?
thanks
View 7 Replies
View Related
Mar 9, 2007
Hi all,I posted something similar to this beforehttp://www.access-programmers.co.uk/forums/showthread.php?t=124289But i didnt get it figured out.Is it possible to use the same field for multiple criteria in a query?the one i would like to base it on is taskID.i just want the total time to be called admin time if taskid=2 and investigative time if taskid<>2.Ive tried it with single and multiple queries in one and am recieving errors with both. help is always appreciated!Woohoo for 100 posts!
View 3 Replies
View Related
Apr 2, 2008
I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?
Details:
I have a table of data for each month with supplier codes in each table, but no date field.
I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.
The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.
Please help
Thanks for reading
View 1 Replies
View Related
Sep 15, 2005
Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)
Russ
View 2 Replies
View Related
Apr 25, 2006
I'm trying to limit the records on a subform via an option group selection. The group has 2 options: optionTrue (option value = 1) and optionFalse (optio value = 2). I have the following code in my query criteria of the true/false field.
IIf([Forms]![FrmHome]![frameProcessed]=1,-1,0)
The false part works, but the true part doesn't. I've tried many variations using true/false, using checkboxes, etc. and nothing works.
The database is SQL Server if that matters.
Any suggestions?
Also, is their a way to have an option for True or False or ALL?
Thanks,
Sup
View 5 Replies
View Related
Jan 12, 2007
Hello,
Was wondering if there is a way without building individual update queries, to update info in one field that has multiple criteria ?
Basically I need to change/update daily multiple ID numbers to new ID numbers, long story on why this needs to be done but for now I need to do it this way.
Example: 12345 update to ABCDE, 6789 update to FGHI, etc. These ID's are all within the same field in the table.
It works fine running each ID one at a time but was wondering if it is possible to do all these updates within one query or code ?
Thanks for any assistance
View 6 Replies
View Related
May 13, 2005
I'm trying to setup a query to pull only the records that have the latest date in Time Scanned for each different serial number.
I have a table with 4 fields: Serial Number, Model Number, Location and Time Scanned. Some records will have the same Serial Number repeated with different Model numbers,locations and times scanned.
I can't figure out how to query only the Last Time Scanned for each different Serial Number. I've tried using "Last" for critria on the time scanned field, but I need that for each different serial number.
It's probably easy...but I'm not able to get it. Thanks!
View 2 Replies
View Related
Apr 18, 2013
Instead of the combo box value being passed into the target query as it should I *always* receive a popup that states "Enter Parameter Value"
I set my filter criteria as such in the target query: "[Forms]![Invoice_Form]![comboFamily]" but for some reason, despite there being a value in that combo box, I am always prompted for a parameter. VBA code associated with a "Generate Invoice" button on the form causes the query and the report to open.
I've attached the database, everything is dummy data.
The suspect form is the "Invoice_Form" the suspect query is the "Invoice_Query" and the suspect report is the "Invoice".
If a number is entered in the "Enter Parameter Value" field that matches the primary key of a family in the "Family" table then a report is generated correctly (as it is currently configured). For some reason it seems that the form with the combo box just cannot be accessed by the query.
View 4 Replies
View Related
Dec 13, 2013
I have a value in an unbound field on a Form, which is 1234 OR 765 OR 356.
In the QBE criteria grid, I used builder to reference this form:
Forms!myform!myunboundfield
The column this is in is for the ID field, which is a number.
However, it is not filtering the data correctly. If I copy the above text and paste it into the QBE grid, then it will work. But when I reference it, it fails. If I change the value to just a number on my unbound field, it works. So the issue seems to be that its bringing across the text as a string and so perhaps effectively puts quotes around it when referencing it.
View 11 Replies
View Related
Dec 16, 2014
I have made a function returning True/False values. I used this function in a query and now it return value Error as well...Is there a way to set criteria to values received in that field (0/-1/#Error). I've tried putting Like 0, Like True with or without quotation mark.Also every workaround comes into play as long as it works.
View 2 Replies
View Related
Jan 10, 2013
I have a form that run a query to select all the students taught by a given faculty member. Once the records are loaded, the faculty enters attendance data, selects a date from a combo box and hits a save button.I change my relationship and now need to be able to store the key for the field in the combobox and not the text.
How do I set the field, table and criteria in the update query to do this. The UD query uses the FacultyStudents query as its record source. Currently the FacultyStudents querry does not have the Key or the date fields since the date is selected by the faculty once their records are loaded. As I see it there is now way to make a join, so I think I either need VBA or SQL, which I am not very good (really bad) at wriitng. Can the necessary Sql be written in the query design view?
View 4 Replies
View Related
Jul 12, 2013
I have a form. On the form I have a button to run a report.The query associated with the report selects all records within a unit (field name (Unit) is used as the selection criteria).Rather than type in the unit name when the report is run, I want to select the unit that is currently shown on the form.
View 4 Replies
View Related
Oct 8, 2007
Hello.
I have a query that is pulling data based on a date in a table. For some records, that date field is empty. For those records, the data should be pulled based on the date of 1/1/06. I tried doing this
=IIf([Signed SLA Received] Is Null,1/1/06,>=[Signed SLA Received])
It doesn't like this at all, it brings back no data. I also tried putting 1/1/06 in quotes, but it didn't make any difference.
This is probably a totally incorrect way to get this done, but I don't know how else to try it.
Thanks!
Stacey
View 3 Replies
View Related
Feb 25, 2014
My problem is as follows, i have created a report that calculates the total volume of FSC Materials. The user picks two dates from Calender controls that the report will range from. However the needs have now changed and i am required to make the report filter further based on user input, the problem i'm facing is that i cannot figure out a way to pass values from different variables to the report separate from another here is the code i would usually use to pass data to a query/report:
Code:
Private Sub MonthlyFSC_Click()
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()
[Code] ....
However i am now trying to do this, but it gives me an error as it is trying to pass the values to one field:
Code:
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()
sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"
[code]....
It is performing incorrectly within the case select and passing the wrong criteria, as it will only display results that meet the default values' criteria. However the date criteria is not be passed either.
View 4 Replies
View Related
Apr 21, 2013
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
Can I do this using only update query?
View 2 Replies
View Related
Jul 17, 2014
I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).
View 14 Replies
View Related
Jun 5, 2015
I have text field that is storing both dates, and "N/A."
I'm trying to to get only records that match this format "mm/dd/yyyy" or "xx/xx/xxxx"
Are there any format functions for query criteria, like must match this format "__/__/____" ?
I know I can just use "<> "N/A"" however I want "<> "N/A" AND Format = "__/__/____"
View 3 Replies
View Related
Mar 24, 2015
I've two tables, QA (Quality Assurance) and Instruments. I'm using form to add data to QA.
There's a field in QA, named InstrumentUsed. The criteria is that InstrmentUsed should only accept value when 'status' field in Instruments table shows 'working'. If status is 'faulty' or 'need repair', it should pop up the related error message and cursor stays on the same field.
I've not starting learning VBA yet, so I'm trying to make use of Macro's.
How to use data from another table as a validation criteria for a field in form?
Pops up a MessageBox and stays on the same field, on the form, unless error is resolved?
View 3 Replies
View Related
Sep 13, 2007
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
View 4 Replies
View Related
