Query Field Criteria
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 Replies
ADVERTISEMENT
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
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
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 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
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
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
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
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
Apr 29, 2008
Hello all
I am using Access as a front end (GUI) with a SQL 2005 database and have several linked tables. An user encountered an odd error that I cannot figure out.
He created a query that had criteria on a date. When he tries to run it the query will not do anything. If he closes out of the query and does any other operation he gets the "This Action will reset the current code in break mode." error and will have to close out access before he can do anything else.
I'm found some strange work-arounds but want to fix the problem the correct way. Attached are two examples - the first one causes the error the second one is the work-around that returns the desired results.
Any ideas?
View 1 Replies
View Related
Mar 11, 2014
I have a query which contains figures. i have a search form based on this query and need to search using comparison operators such as <10000 or >500.
Is there a way to pass these value to criteria field in query ?
View 14 Replies
View Related
Dec 17, 2013
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
View 3 Replies
View Related
Jul 24, 2014
I have a form (frmMetrics) with 2 Combo Boxes: "Year" and "Quarter" These are just number fields (which might be my problem?)
I have a date/time field in a query, and I want the criteria to be based off the year and quarter selected in frmMetrics.
View 2 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
