Queries :: Set Criteria By Having Combination Of Three Options - Or Statement

Aug 19, 2013

In my query, I am looking to set the criteria by having the user pick 1 of 3 options, or a combination of those three options. Here's an example:

The fields are Manager, Employee, Job Function. Let's also say that my criteria is David, John, Busy Work. I want to be able to pull results by David, John, Busy Work, or a combination of those three.

My Query for picking 1 of the 3 looks like this:

Field: Manager Employee Job Function
Criteria: David
Or: John
Busy Work

When I try to add additional Or statements, it never works. An example would be in the fourth line of the criteria, I add:

David John

Running the query produces only the results for David. Mixing it up produces only the results from the first item in the criteria (Manager over Employee over Job Function).

View Replies


ADVERTISEMENT

Modules & VBA :: Total Based On Combination Of Criteria - Compound If Statement

Aug 15, 2013

I'm trying to get a total based a combination of criteria. Here's my issue:

If ([Vendor Billed Amount] > 650 $ and $ [Excess Fee Approved] = "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
If ([Vendor Billed Amount] < 650 $ and $ [Excess Fee Approved] <> "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
endif
endif

I think I'm missing an argument with the nested If statement.

View 3 Replies View Related

Queries :: Implement Combination Criteria To Extract Records

Apr 18, 2014

We have the following table.

Code:
ID studentname lectureplace lecturesubject
1 jack A Biology
2 steve A politics
3 jack B math
4 steve A math
5 jack B politics
6 joe A politics
. . . .
. . . .
154 Jane B Geography

We need a query to implement two criteria to extract the following records:

1-Show lectureplace A AND politics lecturesubject
2-Show only those with lectureplace B regardless of the subject

In case the query is run we will have the following result:

Code:
ID studentname lectureplace lecturesubject
2 steve A politics
3 jack B math
5 jack B politics
6 joe A politics

View 1 Replies View Related

Queries :: Combination Of Letters And Numbers = Data Type Mismatch In Criteria Expression

May 16, 2013

I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.

I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)

This query produces the error given in the title of this thread, whilst the following works:

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)

This related query also works and shows a load of -1s and 0s correctly

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND

But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.

View 2 Replies View Related

Queries :: IF Statement With Criteria

Apr 21, 2013

I am working on one access database wherein in query i need to use iif statement, i have one field called Age, in field criteria i need to use,

IIF(weekday(DATE())="2",>"3",>"1")

this if staement is not working i believe that we cant use ">" ... this type of criteria with iif, so what should i do?

my intention is if today is Monday than age filed >3 else it should be >1

View 2 Replies View Related

Queries :: Use Three Criteria In IF Statement

Sep 23, 2013

Here are the criteria I am using:

Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())

Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())

Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1

I am able to successfully set one of the above as a criteria just fine.I am stalled on trying to combine two or more of the above in an iif statement. Here is what I 'think it should look like:

Funded_Date_Period: IIF([funded_date] = "Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1)", "PrevMonth", IIF([funded_Date]= "Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())", "CurrentMonth", IIF([funded_date]="DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())", "CurrentWeek")

In other words, in a separate field, I would like to print a period name such as above.I have not been able to find any reference to connect these long criteria together in one query expression.

My workaround is to use a union query. That works, but it is a little clumsy due to some outer joins which require a separate query. If that is my only solution, I will run with it.

View 4 Replies View Related

Queries :: If Statement In Criteria To Show All

Oct 2, 2014

I'm working on a published sharepoint web access database, writing a criteria expression in data source of a combo box.The field I'm setting criteria is called SharePointAuthor.

I wrote this critea:
IIf(CurrentWebUser(1)="John Doe","*",CurrentWebUser(1))

Trying to show all records when currentwebuser is John Doe, otherwise, show only records created by currentwebuser.Funny thing is that it turns out "John Doe" couldn't see any records, while other users can see their own records as expected.

View 12 Replies View Related

Queries :: IIF Statement In Query Criteria Based On Check Box

Jun 19, 2014

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).

here is my criteria:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","*")

This does not seem to work. I have also tried:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y",Like "*")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","like "*"")

View 14 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

Queries :: Omit Records With Blank Field - Criteria With IFF Statement And Checkbox

Apr 18, 2013

I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.

What I'm trying to do is:

I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.

Here is what I have:

IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)

View 14 Replies View Related

Modules & VBA :: Combination Of Two Queries

Jun 11, 2015

Is it possible to do combination of these two queries?

SELECT * FROM tblGoraZleceniaNowaWyceny WHERE [id_wycena_pre] = Forms!frmWycenyObszarroboczy!ID_wycena_pre[/CODE]

and

Code:
SELECT [Query1].[NumerArkusza], [Query1].[nazwa], [Query1].[id_wycena_pre]
FROM Query1
WHERE ((([Query1].[numerarkusza]) In (SELECT [numerarkusza] FROM [Query1] As Tmp
GROUP BY [numerarkusza]
HAVING Count(*)>1 )));

I need to do this to work with Recordset.

View 11 Replies View Related

Multiple Options To Find Criteria

Jan 19, 2006

I'm building a database with supplier information. Some users prefer to use the supplier names, but it is sometimes hard know how foreign suppliers are spelled, therefore some users prefer to use supplier numbers.

I want my form to show all supplier data based on either the supplier number or the supplier name, which can both be chosen from 2 comboboxes.

In addition, and here's the catch, I would also like the two comboboxes to update eachother. So if I choose supplier 2 in the first box, the other box should automatically show me the name of vendor 2 and vice versa.

I think the example form shows my problem best ..

View 4 Replies View Related

Queries :: Top N For Each Combination Based On Group Averages

Jul 16, 2014

I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.

I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.

I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.

Basically, what this does is show me the average profitablity of the top 10% in each grouping.

WaterSourceType | Crop | ProfitPerBushel

Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel

View 9 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Criteria For An IIF Statement.

Sep 25, 2006

Hi Gent’s

This is my first posting here,

I’m looking to put a criteria on an IIF statement,



Hol_Period: IIf([CAN_Date]="False" Or [CAN_XM]="False" Or [CAN_paper]="False","True","False")



This will output either True or False, True if any of the other 3 fields contain a False and False if all contain a True.

How can I put a criteria on this to show me only True’s, without using another query???

Thank you

Jason

View 3 Replies View Related

Iff Statement For Query Criteria

May 30, 2006

A database I'm creating contains records of events within an academic year. There will be many events in the table, and I want to create a query to show me only those events in the current academic year.

I figure that I need to use some sort of iff statement to make this happen; something which says:

IF the current date is before 31-Aug-CurrentYear
THEN display records in the range 01-Sep-LastYear and 31-Aug-ThisYear
ELSE display records in the range 01-Sep-ThisYear and 31-Aug-NextYear.

I've had a go at writing this, but can't get my code to work. Can anyone help?

Thanks in advance,

Gary

View 8 Replies View Related

IIf Statement In Query Criteria

Aug 28, 2007

Is there a problem with using an IIf statement in the criteria of a query. If ther isn't then is there a problem with using a Between...And statement inside the IIf statement. Or, does anybody see a problem with this IIf statement being in the criteria of a query.

IIf([blinks_test_end].[Blinks]<>[blinks_test_start].[Blinks],([daily_extract].[Reading_Date]) Between [blinks_test_start]![Reading_Date] And [blinks_test_end]![Reading_date],Null)

[blinks_test_end] and [blinks_test_start] are queries that use the table [daily_extract] the query that I am trying to run uses these queries and the table, however, I have nothing joined. The IIf statement is in the criteria of the [daily_extract].[Reading_Date] field.

View 1 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

I have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.

>=DateAdd("m",-12,fom()) And <=fom()

Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.

Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

I have also added the column name to each expression and it still doesnt produce any results.

View 4 Replies View Related

Syntax Errors With Criteria Statement

Nov 29, 2005

I have the following statement entered in the query criteria section.

SELECT MAX(EDRMPeriod) FROM EDRAppC WHERE EDRMPeriod Between DateAdd("yyyy",-1,DateSerial(Year(EDRMPeriod),Month(EDRMPeriod)+1, 1)) AND DateSerial(Year(EDRMPeriod),Month(EDRMPeriod)+1,1) GROUP BY EDRCHPlan

I am getting a syntax error. This statement runs fine and produces the results I want in Queryman.
Thanks for any help.
k

View 1 Replies View Related

IIF Statement Problem For Query Criteria

Jan 24, 2006

I have the following IIF statement in the criteria of a query (design view). There are three options: (1) Confirmed (2) Failed (3) Pending. Options 1 and 2 are working fine however when option 3 is selected, no records are returned... not quite sure if I've written the IIF correctly for option 3.

IIf([Forms]![Pending_Bookings]![Confirmed]=-1,"Booking Confirmed") Or IIf([Forms]![Pending_Bookings]![Failed]=-1,"Failed Booking") Or IIf([Forms]![Pending_Bookings]![Pending]=-1,([Status_Codes].[StatusName])<>"Booking Confirmed"<>"Failed Booking")


any help appreciated..tnx :)

View 7 Replies View Related

Using Square Brackets As Criteria In A LIKE Statement

May 1, 2008

I need to use square brackets as part of the criteria in a LIKE SQL statement. The criteria is:

[ABC123][SR]

ie, find all records which have that as part of a longer text string. How can I amend the SQL statement below to "escape" the square brackets and treat them as part of the string?

SELECT * FROM p_Overall WHERE (((p_Overall.Assigned_Dept)="ICONS_IMAC")) OR (((p_Overall.Brief_Description) Like '*[ABC123][SR]*'));

Help!!!!

Thanks in advance :-)

View 3 Replies View Related

Modules & VBA :: If Statement With Multiple Criteria

Apr 14, 2015

I'm trying to create a report that does the following:

If the term "Other" is selected in the Time1 field, then the Time1 field will not be visible, but the field Other1 field will be visible and if the term "Other" is not selected in Time1 field, then the Time1 field will be visible and the Other1 field will not be. This is what I have for VBA code, but it is not working.

If Not IsNull(Me.Time1) Then
If Me.Time1 = "Other" Then
Me.Time1.Visible = False
Else
Me.Time1.Visible = True

[Code] ......

View 6 Replies View Related

Modules & VBA :: Single Criteria In Where Statement

Jul 15, 2014

If the following works for a single criteria in a where statement.

Code:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblProcedures WHERE ProcedureID = " & lngMoveUpID, dbOpenDynaset)

What would a second criteria look like.

View 6 Replies View Related

Modules & VBA :: Multiple Criteria For Where Statement?

Feb 25, 2015

I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:

Code:
strWhereFinal = strWhere1 & "AND " & strWhere2 & "AND " & strWhere3

You can see that if strwhere1 is NULL, the string will start with "AND [ApplicationStatus] = 1" and will obviously cause a failure.

Is there a simple way to build this string based on the number of search strings that are not null?

View 2 Replies View Related

If Statement With Todays Date Being Criteria

Aug 19, 2011

I'm trying to create a button that posts an order and before it post certain criterias need to be meet. One of them is a date needs to be selected in the order, if not a popup show to explain. The trouble I'm having is referring to todays date in the VBA code. I've been reading and trying different things but not getting anywhere, hopefully someone can fill me in.

This is the code I'm working with.

If [PayOrderMoneyReceivedSubform].[Form]![OrderDate] = TodaysDate Then
dosomething
else
donothing
end if

The part I'm having trouble with is TodaysDate, what should I be putting there?

View 2 Replies View Related

Select Statement Searching On Date Criteria

Feb 23, 2006

Hello everyone! :)

I've been banging my head of a brick wall with this one. Its probably straight forward... but I can't see the solution! A problem shared is a problem halved... or so they say!

Ok I have this table called CustomerComments. In it are the following columns

CustID
Comment
CommentDate

For each custID I want the most recent commentdate and its matching comment from that row... (there can be multilple comments recorded per custID)

Problem is I can't seem to get this to work.. instead its returning all rows. I have run a query that will sort and group this list. The top line of the grouping gives the most recent comment and commentdate per customer.

Does anyone know how I could just get that top line per custID rather than all rows returned?

Thanks for reading this... hope it makes sense!

:)

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved