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 Replies


ADVERTISEMENT

Queries :: Want IF Statement To Show 0 When Null Value

Jun 8, 2014

After hours of playing around i finally have my IIF statement working the proplem is now is that when the Value is null it gives a blank value on my Report. How do i get it to show the value of 0 when it is Null. can i use an isNull withen my IIf statement maybe nested what is best practice?

View 6 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 :: 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 2 Replies View Related

Queries :: WHERE Statement - Enter Dates Into Date Reported Fields For Results To Show

Sep 11, 2013

I use this

'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'

in a query by form.

The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.

How can I change it so that I don't have to enter dates into the date reported fields for results to show?

View 4 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 :: Key Criteria Into Combo Did Not Show Up Any Result

Jan 22, 2014

I set my query criteria to my [Forms]![Form1]![Combo4].

After I key a criteria into combo4 , then I open again the query table ,

It did not show up any result , why ?

No combo4 don't have any event.

View 1 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

Queries :: Left Join Query To Show Records That Fulfill Two Criteria

Mar 25, 2014

I have two queries. The unique key in both queries is GUID for katalogposition.

One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.

The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.

The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.

1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".

I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.

How can I do that? Is there another way to do it? Please see attachment.

View 3 Replies View Related

Queries :: Calculate Time Difference And Show Result Under Certain Criteria In Query

Jul 9, 2013

ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00

This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs

Like Below :

ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00

IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00

I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...

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

What Is The Code Statement I Can Use To Show The Colors Selection Window

Jun 2, 2007

I want the write a code that can be when run shows the Color Selection Window. For example I want the user to select the color of one object in the form by clicking on a button. Then I can store the selected color into variable.

http://cops.msc.tamu.edu/graphics/cset.jpg

Please Help me as soon as possible.

HIGLY APPRECIATIVE,

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

Forms :: Form Control With Iif Statement To Show Specific Records?

Jul 13, 2015

I have a form based on a query in datasheet view. What i would like is to show only those records that fulfill the truepart of iif statment, and do not show the ones that fulfill the false part.

I have the following fileds in the query

month
currency 1 checkbox
currency 2 checkbox
sum

In the form, the control named month contains the following code:

control source: =IIf([checkbox1]=no;[month];0)

So this gives the appropriate months, but also shows the all other records with a 0 in the month field. I would like to get only the records shown that are correct with the truepart of the statement.

View 6 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







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