Is It Possible To Return No Value When An If Statement Is True?
Sep 22, 2006
This should be really simple, but I am stumbling on the criteria. I have a combo box which returns "Y" or "N". I'm using this value as criteria for a field called [Planned=Y/N]. This field stores either a "Y" or a "N" to indicate that a network outage was planned or not. If the combo box is "Y" I want to return all values(Y and N). If the combo box is "N" I only want to return values of "N". I've tried the following in the criteria without the desired affect:
IIf([Forms]![frmOutagemenu]![cboflag]="Y","","N") - only returns records when "N" is selected.
IIf([Forms]![frmOutagemenu]![cboflag]="Y",null,"N") - only returns records when "N" is selected.
IIf([Forms]![frmOutagemenu]![cboflag]="Y",In("Y","N"),"N") - too complicated for Access to decode.
Is there another way to return nothing for the criteria when "Y" is selected?
I have a complex IF Statement within VBA. When I step thru the code the if statement variables should return true but instead treats it as False. See '>>>> this happens on the 2nd Pass of a Do Loop Statement, the First Loop the if Statement Returns True
Variable Values:
Record 1 contractNo: 00001634 nfld: 33.40% nTier6: 30.00%
Record 2 contractNo: 00001634 nfld: 137.52% nTier6: 28.50%
so the 2nd if should be true but it acts as false an moves to next If statement.
Code:
If nfld = Format(0, "Percent") Then nOvrAmt = 0 BkOvrCalc = nOvrAmt GoTo cont: '>>> ElseIf nfld > nTier6 Then nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields("T6E").Value
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.
Hi, the syntax is of iif is that iif(case, true, false) right. In my case I have role_ID to filter so my iif statement is something like iif([role id] = "1", "2" or "3") But it does not work, I don't know why iif([role id] = "1", "2") It works. So I think OR statement is something wrong.What should I do in this case?
Can I generate an IIf statement with two-true values? I have these two statements but don't know how to combine them:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;””) IIf((Weekday(date);1)=vbSaturday or vbSunday;y;””)
I have at field with a date and will like the true statement to be “x” if the data is Monday-Friday and “y” if the date are a Saturday or Sunday and if the date field is blank, I want to have a blank field here as well.
I have tried:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;y)
In a form, the value of any field may determine if the other field will be true or false. For example in my form, inventory, if value in code is equal to 2 then the Field Table will be automatically false.
I have a report where my customer wants to be able to input a value and then be able to select whether the report shows values above or below that value. I have a combo box that has 2 values (1=Less than or equal to, 2=greater than or equal to). There is a text box where he inputs the rate to compare against (e.g. $75). When he hits submit, the application stores the values of the combo box and text box into global variable and then I have a public function that can be used to retrieve the values. GetHRate() gets the value to compare against and GetHRateCompare() gets the value to indicate <=(1) or >=(2).
In my query for criteria for rate I have this expression: IIf(GetHRateCompare()=1,"'<=",">=") & GetHrate()
I've tried every combination of double quotes, single quotes, no quotes moving the GetHRate inside the IIf statement and nothing seems to work.
If I just hardcode <=GetHrate() into the criteria it works perfect but that does not achieve my goal of letting him select over/under at run time. Here's the full query:
SELECT qryCustomers_AverageTimeByScheduleID.ServiceName, qryCustomers_AverageTimeByScheduleID.ScheduleID, qryCustomers_AverageTimeByScheduleID.SumOfTotalTim e, qryCustomers_AverageTimeByScheduleID.PropertyID, qryCustomers_AverageTimeByScheduleID.PropertyName, qryCustomers_AverageTimeByScheduleID.MonthCount, qryCustomers_AverageTimeByScheduleID.AverageTime,
My first question is how do I append 00001 so I can have a value for the false side of 9123400001 instead of 912341...My second question is why does it prompt me to enter parameters? It also produces all false values from the if statement...What I am trying to do is use a column that has either a Y or a N and using the if statement to correspond with different formulas depending on the Y or N.This what I have so far.
I am trying to write an expression that will result in a date in the format (mm/dd/yyy). I want to display the curent day if it is before 11:00 am based on the system time, and the next day if it is after 11:00 am. Below is the expression that I currently am working with... but it is not working.
I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField] "kick the ball" "return the pass"
my query result should looks like this
[Orig] [new] "kick the ball" "kick" "kick the ball" "the" "kick the ball" "ball" "return the pass" "return" "return the pass" "the" "return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
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?
The above formula works as is in the field I have it in, but need it performed only if [Label5] is marked yes. I guess what I need is what goes in front of the above formula. I've looked through the forum, but it is so huge I had no luck finding what I needed.
If [Label5] is no, I need it to go to [Label6] and if [Label6] is yes, perform another calc like the one above. If [Lable6] is no, I need an error message.
Hope this is clear. Any help is greatly appreciated.
Hi all very simple question i'm sure but had a look through the forums and couldn't find anything.
On a form i have 2 buttons, one for new data and one for editing data. When either of these buttons are clicked a few text boxes pop up and immediately display data (The first record of the recordset). For the edit button this fine, however how do i get the text boxes to display a new record and not allow the user to head backwards through the recordset only onto a new record??
Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.
criteria: [Enter date - mm/dd] Or >#1/1/2001#
When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.
I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.
I have a form that has code tied to the 'on open' event that is going to be accessed by users where we want them to only have access to certain fields which we want them to fill out. The fileds that will be locked will change based on the field called 'Item Number'. The code will be long because there are 30 different Item Numbers and about 10 to 20 fields that we will disable based on the Item Number. The code is like:
Dim Item_Number As String If Me.Item_Number = "32000" Then Me.Batch_Lot_Number.Enabled = False End If
This is all great except that the disable makes the field kind of obscure by the color it gives it. I don't want to use the lock property because that doesn't give you a visual clue that its locked.
Is there a way to change the color of the field background using VBA?
I may be missing something here, but can any one help?
I am using a Iif statment in a query to filter records as follows:
If([value from a form]= "all holidays",("weeks hols" or "days hols"),"not a holiday")
This is returning a to complex to calculate error - I think it is a Syntax error but could be wrong but if I enter The string "week hols" or "days Hol" as the criteria without the Iif statment that works fine.
Can anyone help? I have a table with the following: Booked with a y/n field Person Name with a text field So... Yes(True) Jim Yes(True) Jim No(False) Jim
What I need is to be able to show a query that shows if the checkbox is ticked (True) and the Name is Jim total up how many true items there are. So from the above I can see that Jim has 2 true items. I would then like this shown in a text box is this possible? Also, it is not always going to be a person called Jim so... (From above) Yes(True) Jim Yes(True) Jim No(False) Jim Yes(True) Jack
So how would this go on a query in a sub form? So Name Jim would show 2 and Jack would show 1? At the moment is is grouping the name and I dont want that. Any ideas Cheers
I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!
Hi! When it rains, it pours, right? I'm wondering if any of you know of a way to count true statements...
What I mean is, I have a query where I have a series of 5 different OR statements, meaning I want to see records with either a min. ed. number, or a years of experience number, etc...
The query returns every record with at least one of the criteria being met. What I need is to count the statements that are true for one record. Let's say I have someone who meets all of the OR criteria, I need a way to count the number of criteria that they met. Is there a way to do this? Maybe with a crosstab query??? Thanks!!
I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
