Queries :: Using IIF And OR In Query Criteria
Apr 24, 2015
Basically, I have a Combo Box [Forms]![Sales]![Site] which a user can use to select either an exact location or a market area for multiple locations.
I also have a query for sales with a SellingBranch field on.
I want the query to display sales for only specific selling branches as chosen using the above Combo box. However, I'm struggling to get it to display multiple selling locations for one selection. For example I want to tell it that if [Forms]![Sales]![Site] is "Bolton MA" then the query needs to show results for when the SellingBranch is either "Bolton" or "Chorley".
At present I can get it to work to show just Bolton but don't know how to adjust it to show Bolton and Chorley when "Bolton MA" is selected.
IIf([Forms]![DatesSalesLeague]![Site]="BOLTON MA","BOLTON","MANCHESTER")
View Replies
ADVERTISEMENT
Aug 9, 2005
Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check boxes)
Y ACD 2 x x
Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query performs
Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Thanks,
CB
View 1 Replies
View Related
Sep 14, 2007
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
Thanks in advance,
Emily
View 5 Replies
View Related
Jul 10, 2015
i have a query name "Query01". I want to add a criteria ">=#1/5/2015#" in the field of "PaymentDate" using VBA.
View 1 Replies
View Related
Dec 10, 2014
I have a table with a field TDate (dd/mm/yyyy format). A query with calculated fields is lying on this table. I want to put a date criteria in this query, by a combobox in an unbound form, where the date format has to be mmmm/yyyy and has to be updated as new TDates come in.
Trying to pick up values for the combobox from the TDates field (and formatting them), I get a list with several same values. This is of course expected as there are many records on the same month, even on the same day of the month.Is there a way to have this list with unique values for each TDates month/year?
View 3 Replies
View Related
Mar 24, 2014
I have one datatable and i have to list the duplicates from it based on different criterias. It contains invoices, we would like to avoid duplicated payments.I'm struggling with a query. Basically i have a working one, i have to add one more criteria to filter out a couple of rows where the text column is "freight", but anyhow i have tried, it's not working. Without that one line where the not equal is, it's working as it should, when i'm adding that line it ruturns the same as before, but it should give me 108 less lines. The below one is based on an other query result called Duplicates_Rule1.Here is the query:
Code:
SELECT * FROM Duplicates_Rule1
WHERE (((Duplicates_Rule1.BSEG_DocumentNo) In (SELECT [BSEG_DocumentNo] FROM [Duplicates_Rule1] As Tmp
GROUP BY [BSEG_DocumentNo]
HAVING Count(*)=1 ))
AND (Duplicates_Rule1.Check) Is Null)
and (Duplicates_Rule1.BSAK_Text <> "FREIGHT")
ORDER BY Duplicates_Rule1.BSEG_DocumentNo;
View 9 Replies
View Related
Mar 13, 2015
In Access 2007, I put into the table, in a certain field, a certain word field, so I put into design view for that query, in that field, Like "Field" and even though the word field is in that table in that field, it doesn't show it in that particular query?
View 14 Replies
View Related
Jan 10, 2014
I am trying to use criteria for Product column (i.e keep only product A) Total for Sales column to aggregate the values and it seems that is cannot be done simultaneously.
Product Sales
A 34
A 45
B 21
A 11
B 23
Eventually i want to get a table like below
Product Sales
A 90
I get the following warning : you tried to execute a query that does not include the specifies expression as part of an aggregate function.Why is this happening. I don't want to create 2 different queries to achieve it. Is there a way to do it i the same query?
View 8 Replies
View Related
Jul 29, 2015
Currently, I am using the following code to add criterias to a Query before I run it:
Code:
qdf.SQL = "SELECT * from [Offshore_Projects] WHERE [Business Field].Value = " & Tall
Unfortunately, I struggle with adding additional criterias for example something like this:
Code:
qdf.SQL = "SELECT * from [Offshore_Projects] WHERE [Business Field].Value = 3" AND [Offshore_Projects] WHERE [Location] = ""Longyearbyen"""
View 2 Replies
View Related
Aug 10, 2013
It is possible to create a parameter query in Access that will search for records that match the parameter entered by the user AND contain all other records that have that value plus other text.
For instance you may want Access to return all records that begin with the letter Q.
To do this you need to enter the following expression into the criteria row:
Like [Find records beginning with:] & "*" Or Is Null
The user can enter a Q and the criteria concatenates the wildcard character * to this parameter value, to find all text strings that begin with a Q.
By also using Or Is Null in the criteria the user can enter a blank value into the parameter query to return all records, even if this field is blank.
But how do I search if i want all the records with John anywhere in the field???
View 5 Replies
View Related
Apr 10, 2013
I am trying to use a textbox value as a query criteria using the code [Forms]![Home]![mtdDateBox], following the standard structure of referencing a form item. However, I am not returning any records, when it should be giving some. The data in the textbox follows the date format "DD.MM.YYYY" but is used as a string.
Is there something I am missing out? I am using MS Access 2010
View 1 Replies
View Related
Sep 16, 2013
I want to create a query with multiple conditions. Basically if the person Passes any of this trainings they need to show up in my query..how do you do it?
SELECT tblMasterUsers.userid, tblMasterUsers.Licenses, tblMasterUsers.firstname, tblMasterUsers.lastname, tblMasterUsers.email, tblMasterUsers.npn, tblMasterUsers.Region, tblMasterUsers.ABSID, CMPreport2014.[Ahip status], CMPreport2014.[LP Status] AS [AZ Product Training], CMPreport2014.[LP Status1] AS [CA Product Training], CMPreport2014.[LP Status2] AS [OR WA Product Training], CMPreport2014.[LP Status3] AS [Fraud Waster Abuse],
[code]....
View 1 Replies
View Related
Jul 21, 2013
I am trying to select specific records based on input in a query. This criteria currently works to select everything with an ID less than 21 or 31 based on the users entry: <IIf([Enter "Y" for List 1]="Y",21,31)
I now need to change the query so that only certain records are selected based on the users entry. As an example, I would like to select 1, 3, 5 and 7 if they enter Y. And I would like to select 2, 4, 6 and 8 if they dont enter a Y.Here is what I have tried:
In IIf([Enter "Y" for List 1]="Y",(3,5,7,9),(2,4,6,8)).With this criteria, I get this error .The In operator you entered requires parentheses.
View 5 Replies
View Related
Dec 16, 2014
I have a query, and in order for it to run properly, I've set it to look in an unbound combobox on a form to display the necessary data I want, as long as I never close down the form, this is exactly how I want it.However, if I close this form down, and reopen it, I'm asked by messagebox to get the lookup from a different form's combobox, despite saving the correct form's design over and over again, and saving the actual query, more than once, while in design view.when Access changes the criteria, there isn't even a combobox value on the form it changes to.
View 3 Replies
View Related
Feb 14, 2014
I have a query that runs to check if a form has any empty delay hrs.
Code:
SELECT [Tble-wcDelays].ID1, [Tble-wcDelays].LinkingID, [Tble-wcDelays].HoursDelay, [Tble-wcDelays].ReasonDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].ID1, [Tble-wcDelays].LinkingID, [Tble-wcDelays].HoursDelay, [Tble-wcDelays].ReasonDelay
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-RPC]![wetcleanID]) AND (([Tble-wcDelays].HoursDelay) Is Null)) OR ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-RPG]![wetcleanID]) AND (([Tble-wcDelays].HoursDelay) Is Null));
I'm trying to avoid cerating multiple queries that dothe same thing. But if this qurey runs from the form "FRM-RPG" then It errors because it can't find "FRM-RPC" so why does it not over look that frm and go to the 2nd Frm ?
View 3 Replies
View Related
Nov 15, 2013
How can set criteria in select query as Textbox1 or textbox 2 ...textbox 1 and textbox 2 are field on different forms.
View 4 Replies
View Related
Jun 3, 2013
I'm trying to build a query to use as a filter in the DoCmd.OpenForm function.
I have done this several times before, referencing values from forms e.g.
Point To
Field: LeadID
Table: Lead
Criteria: [Forms]![GridDisplay1]![LeadID]
This filter is used in the procedure:
Code : DoCmd.OpenForm "LeadDetails", acNormal, Point_To", , , acDialog, """"
And it works great, I click a button next to the record I want to select (records displayed on continuous form) and it opens the Lead Details form on that particular record.
However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:
Code:
Private Sub brkSearch_Click()
Dim argCount As Integer
On Error GoTo Err_Handler
If IsNull(brkFirstName.Value) And IsNull(brkLastName.Value) And IsNull(brkCompany.Value) Then
MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
[Code] ....
The sub-form then displays basic info such as Name, Company, State, and the "BrokerID" which is the primary key of the "Broker" table. This "BrokerID" is displayed in "Textbox 20" so I set a button next to each result to on_click perform the procedure:
Code:
DoCmd.OpenForm "BrokerDetails", acNormal, "Point_To_Broker2", , , acDialog, """"
"Point_To_Broker2" is set up in the same style as "Point_To":
Field: BrokerID
Table: Broker
Criteria: [Forms]![BrokerSearch]![Text20]
However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record.
View 11 Replies
View Related
Feb 4, 2014
I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.
In my query I have a name field where I put the criteria: Forms!Formname.Textboxname
By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.
Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.
View 7 Replies
View Related
Apr 5, 2013
I use expression on the query field, but after I run it, the field change its format from number to text, how to reformat on the query criteria to change the text to integer format?
View 8 Replies
View Related
May 15, 2015
I want a query selecting records from table1 based upon a value in table2 -
table1 contains thousands of records - fields include VendorNo and VendorName
table2 contains a single record - fields include VendorSearchName (which is a "short name")
I am trying to create a query which lists all VendorNo and VendorName entries where VendorName contains VendorSearchName
eg. VendorSearchName = "Fred" will list VendorNames including "Fred Flinstone", "Big Fred" etc
View 3 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
Jun 3, 2014
I have a simple report which i open from a navigation form, i have a textbox in the report i wish to use as criteria, on start the report will be blank until i enter a name into the textbox and requery. However im not sure how to link this and how to stop access asking for this information before the report is opened?
View 5 Replies
View Related
Mar 20, 2015
I have a table [maintable] that holds print records, with the following fields :
id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.
I want to create a query that will show the following:
Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this
"WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..
So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)
here are all of the criteria fields i want:
1) - papersize=A4
2) - papersize=A4 and Color=True
3) - papersize=A4 and Color=False
4) - papersize=A4 and Duplex=True
5) - papersize=A4 and Duplex=False
6) - papersize=A4 and Color=True and Duplex=True
7) - papersize=A4 and Color=True and Duplex=False
8) - papersize=A4 and Color=False and Duplex=True
9) - papersize=A4 and Color=False and Duplex=False
View 5 Replies
View Related
Nov 29, 2013
I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".
I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.
I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...
SELECT [Dove Data File].*
FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase
WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL
FROM Dove
WHERE [Dove Data File].TowerBase = Dove.TowerBase
AND [Dove Data File].Bells <> Dove.Bells))=True));
But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.
View 1 Replies
View Related
Mar 18, 2014
I need some syntax in setting 2 criterias for a DLookup query.
I've attached a sample db with 2 tables: Main & Timesheet
I need a "combo" query showing (on the same line) all Qty for Transcodes N, 1 & 2 where the Staff number and TSNum is the same.
I'm sure about the logic but the syntax is letting me down.
I can pull in 1 of the criteria E.g.:
OT1: DLookUp("Qty","ExOT1","[Staff] =" & [Staff])
But can for the life of me not script the second criteria in.
So in my result of ExCombo I'm getting Staff 11 showing 4 under OT1 while I know the result does not apply for TSNum 29832 as indicated hence the need for the 2nd criteria.
Since I have to change some of the values to text inside the query it might be best to have a look at the attached db rather than just suggesting the correct syntax .
View 3 Replies
View Related
