IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac
anonymous
excel.misc
78881 articles. 
5 followers.
4 Replies
245 Views
Could it be that IFF should be IF? Bernard "mac" <anonymous@discussions.microsoft.com> wrote in message news:ED4FCA74-F315-453A-8813-A7DE22AC74D8@microsoft.com... > I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. > > I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried. > > IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0) > > Can anyone give me some help on what I've done wrong? I just get #NAME in the box. > > Thanks for the help in advance, > > Mac
bliengme1Mac,
IIF is VB, Just use IF
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"mac" <anonymous@discussions.microsoft.com> wrote in message
news:ED4FCA74-F315-453A-8813-A7DE22AC74D8@microsoft.com...
> I have a timesheet template that allows a user to enter their Time In,
Time out for Lunch, Time back In, Time Out for the day, then calculates the
time differences to give total number of hours worked.
>
> I am trying to make it so that if the total time for the day is Negative,
it assigns zero as the default. Here is the statement that I tried.
>
> IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0)
>
> Can anyone give me some help on what I've done wrong? I just get #NAME in
the box.
>
> Thanks for the help in advance,
>
> Mac
bob.phillips1Hi Mac replace IIF with IF rest looks okay Frank mac wrote: > I have a timesheet template that allows a user to enter their Time > In, Time out for Lunch, Time back In, Time Out for the day, then > calculates the time differences to give total number of hours worked. > > I am trying to make it so that if the total time for the day is > Negative, it assigns zero as the default. Here is the statement that > I tried. > > IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, > ROUND((((E7-E6)+(E4-E3))*24),2),0) > > Can anyone give me some help on what I've done wrong? I just get > #NAME in the box. > > Thanks for the help in advance, > > Mac
frank.kabelUnless I am reading it wrong, as well as using IF instead of IIF, you will need to change the formula to either: =IF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, 0,ROUND((((E7-E6)+(E4-E3))*24),2)) or =IF(ROUND((((E7-E6)+(E4-E3))*24),2)>0, ROUND((((E7-E6)+(E4-E3))*24),2),0) in order to return positive numbers. You could also consider: =Max(ROUND((((E7-E6)+(E4-E3))*24),2),0) but how are you ever going to end up with a negative time for the day? By going over midnight? Regards Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "mac" <anonymous@discussions.microsoft.com> wrote in message news:ED4FCA74-F315-453A-8813-A7DE22AC74D8@microsoft.com... > I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. > > I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried. > > IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0) > > Can anyone give me some help on what I've done wrong? I just get #NAME in the box. > > Thanks for the help in advance, > > Mac
sandymann
Similar Artilces:
if, and statement
I need to do an if and statement something like this- if A1=1 and A2x(A1)1>10 then no more than 10, but if A2=2 and A2xA1>25 then no more than 25 so if A2= 12 and A1=1 then the result will be 10 but if A2= 12 and A1=2 then the result will be 24 but if A2= 15 and A1=2 then the reult will be 25 I know it is a lot, but you can guys figure it out. Try this: =MIN((A1=1)*A2,10)+MIN(2*(A1=2)*A2,25) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------...
Downloading statement shows items to review but I can't find them (2007)!
The last few days, when I download my checking account statementm it succeeds and shows items to review (4 yesterday, 5 today) but the downloaded items are not showing up in the account. The last item in the register is from 11/21 (5 days ago). I have the current vesion (MSM Deluxe 2007, Version 16...1024). I've been using MSM for many years. Anyone have any ideas on this? Thanks, Gary In microsoft.public.money, GarDavis wrote: >The last few days, when I download my checking account statementm it >succeeds and shows items to review (4 yesterday, 5 today) but the >downloaded ...
Macro and If Statement
Here's the situation: I have a worksheet that will have an "X" in either cell F3 or G3 or H3. What I would like to do (via a macro), is to examine F3, G3 and H3, and if there is an "X" in anyone of those cells, to run another specific macro. For example: Run a macro (for example called "totalsummary")to determine... If there is an "X" in F3, then run a macro called "summaryEL"; If there is an "X" in G3, then run a macro called "summaryHT"; If there is an "X" in H3, then run a macro called "summaryFA&quo...
IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...
IIF(AND) statement
Hi I have a number of things I need to test in the query. I need to use IIF statement with AND (for example in Excel you can do it),. Example I need this IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2") how to do it in access? -- Greatly appreciated Eva On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote: > Hi > I have a number of things I need to test in the query. I need to use IIF > statement with AND (for example in Excel you can do it),. > Example > I need this > IIf (and(cat="H...
IF statement question
Is it possible to test for more than 2 different conditions? ...example total number of hours paid hours 5 hrs 5 hrs 10 hrs 9.5 hrs 12 hrs 10 mins 12 hrs IF "total number of hours" <6 this will be "paid hours" ..... IF "total number of hours" >6 but <12 "paid hours" = "total number of hours" -.5 IF "total number of hours" >12 "paid hours" = "total number of hours" -.1 -----= Poste...
iIF clause
Can you help me build the right IIf field in the query with the following conditions : The field DDU consissts of : DDU :[exworks]*2+0,4+0,01 To the above expression i must also add 0,001 if size = 205, etc according to the following table : 205 0.001 60 0.001 20 0.009 1 1.32 4 0,32 0,5 1,67 However Acces does not accept my query, obvioulsy i have errors : DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001) Will you help me ? ...
What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...
IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the IIF is false I would like the text (mainly 1 or 2 words) not to be underlined. How do I end the IIF so the text is not underlined? Thanks. John I am not sure how you mean, but if you want this underlined based on the true or false then use the properties box and choose the text decoration item and write an expression in there. "JohnE" <JohnE@discussions.microsoft.com> wrote in message news:5C6EEFBC-0DC0-41C0-A35D-3A01209E568A@microsoft.com... > Hello. Doing a report that uses an IIF...
Money 2004 does not see downloaded bank statement...
I just upgraded (a couple of weeks ago) from 2003 to 2004. I am running Windows XP Pro. In 2003, I used to use Internet Explorer, go to my bank, select my date range, and download my transactions for "Money 99 or higher". If I had Money 2003 open, it would automatically see the downloaded file and import the transactions. I just did the EXACT same thing - about 10 times - in Money 2004 and it does NOT see the downloaded file or import the transactions. The download happens so fast that I don't know where the browser saved off the file. A quick search of the hard drive d...
Changing Background Color in "IF" statement
Greetings, Is there a way to color cell background from within an "IF" statement ? Thanks, -Paul Hi not with a formula. But use 'Format - Conditional Format' for this -- Regards Frank Kabel Frankfurt, Germany Paul Pruchnik wrote: > Greetings, > > Is there a way to color cell background from within an "IF" statement > ? > > Thanks, > > -Paul Paul Not without using Conditional Formatting or VBA. Formulas alone cannot change cell formats. You can place an "IF" statement in CF "formula is" Or perhaps just a &quo...
Add qualifier to andif statement
I have a spreadsheet formua that I'd like to add a test to, for name in another column. The formula is =SUMIF($F$3:$F$218,">="&A3,$G$3:$G$218) -SUMIF($F$3:$F$218,">"&B2,$G$3:$G$218) In this case I want to test a name in column H and only count G if name is equal So far I know a bunch of ways....that won't work..LOL....can someone help me out thanks. Steve "litngldy" wrote: > I have a spreadsheet formua that I'd like to add a test to, for name in > another column. > The formula is =SUMIF($F$3:$F$218,">="...
Sum(IIF statement
I have a large table that I want to query 2 things from. Please be patient because I am pretty new at this. I need to count the number of times that something appears in some fields in the table. Below is what I have tried (keep in mind there are many more fields to count)... test: [Exp1]=Sum(IIf([Account number] Is Not Null,1,0)+(IIf([Address] Is Not Null,1,0)) I would like my output to look like: KEY TEST 1 2 2 0 3 5 etc... And then I want to look in each of those same fields and if there is data concatenate it into one field on the query output and...
help with nested iif statement
I have a table and I want to create a query that has a new field " Type of Shipment" base on a field "Item Category" If the item category field equals ZDIR, ZTRN ...etc up to 23 different item categories the "Type of Shipment field will equal DIRECT else I would like it to return "Stock" Any help will be greatly appreciated. Thanks Hi, for the iif statement. What you can do is: IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','') Having a long nested if is a headache. What i can suggest ...
IIF with condition
A criteria "<100" in a numeric field is successful. Using it in an IIF statement as IIF(1=1,<100) dosen't work. What is the proper syntax? Dave Dave wrote: > A criteria "<100" in a numeric field is successful. Using it in an IIF > statement as IIF(1=1,<100) dosen't work. What is the proper syntax? > > Dave > What are you trying to do? "<100" in a criteria, for the field [MoneyBet] is saying, give me all records that have a number less than 100, in [MoneyBet]. an iff statement is: iff(criteria, , answer if criteria is...
Search statement not working
I have a search form and this code is on the keypress of a textbox Me.RecordSource = "SELECT * FROM [qrysearchcomplaint] WHERE [txtcomplainant] like ""*" & Me.txtSearch.Value & "*"" or [txtbusinessname] like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" & Me.txtSearch.Value & "*"" Or [txtrefnbr] Like "" * " & Me.txtSearch.Value & " * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * &quo...
Statements Not Printing Properly
Take the following example. Customer A has invoice from November 2004 which was paid in January 2005. We run an aging as of December 31, 2004. This places the Invoice in 31-60 aging bucket. Print an historical TB for this customer as of Dec 31, 2004 and it prints that invoices in 31-60. Print a statement for that customer using 12/31/2004 with options 'Print Past Due Balance,' 'Show Applied Payments' and 'Exclude Fully Applied Payments'] and it says no transactions to print. If I unmark 'Print Past Due Balance,' the invoice prints on the statement a...
If Statement in a macro
I want to put the following formula into a macro, what commands do use? If Z1=1, goto A1, if z1=100 goto A100. Can someone help please? Thank you -- colinfraser@prosealuk.co ----------------------------------------------------------------------- colinfraser@prosealuk.com's Profile: http://www.officehelp.in/member.php?userid=523 View this thread: http://www.officehelp.in/showthread.php?t=127235 Posted from - http://www.officehelp.i If Range("Z1").Value = 1 Then Application.Goto Range("A1") ElseIf Range("Z1").Value = 100 Then Appli...
Multi-value paramter syntax...help
How do I write the syntax for a stored procedure that has one parameter but accepts multiple values. I am running a report from reporting services that can pass multiple values or pass all values to the stored procedure stored procedure 1: exec getcolor @color = blue....this runs fine. stored procedure 2: exec getcolors @colors in('red', 'white', 'blue').........bombs code is create procedure getcolors @colors nvarchar (50) as (select * from hrs_by_activity where color in(@colors) ) need help making this work. thanks in advance O...
How long to you all keep bank & annuity statements?
I realize that this is probably a little off-topic, but as I keep all my msmoney accounts up to date, how long do you all keep statements from banks, stock brokers, annuities, etc? I just opened my filing cabinet and realized that I have records going back MANY years. Do I really need to keep them, or am I being anal? Dick In microsoft.public.money, Dick wrote: >I realize that this is probably a little off-topic, but as I keep all my >msmoney accounts up to date, how long do you all keep statements from banks, >stock brokers, annuities, etc? I would keep the statements fo...
iif and between
hello i have a set of numbers 1-52 in a field. when i query criteria ...between [weekvar] and [weekvar]-13....i get the answer i need with the query: a set of numbers. However, when i put this same condition within an iif statement in the query criteria, it doesn't work and comes up with no results. iif([weekvar]>13, between [weekvar] and [weekvar]-13, 100). The "100" answer will work, but the "between" will not query anything. What i am trying to do is to return a set of numbers while using iif(. I don't understand why it'll work when...
BIG IF statement problem
I can not get this if statement to work any suggestions. =IF(M11>25%,"1%",IF(M11>33%,"4%",IF(M11>40%,"4.5%",IF(M11>45%,"5%",IF(M11>55%,"7%",IF(M11>60%,"7%",IF(M11>65%,"7.5%",IF(M11>70%,"8%",IF(M11>75%,"8.5%",IF(M11>80%,"9%",IF(M11>85%,"10%",IF(M11>90%,"11%",IF(M11>95%,"13%",IF(M11>100%,"15%",IF(M11>110%,"16%",IF(M11>120%,"18%",IF(M11>130%,"19%",IF(M11>140%,"20%"...
IIf
I am creating a report that I want to spell out "has expired on" and the date that I entered in the form if that date is prior to today; or to spell out "will expire on" and the date that I entered in the form if that date is greater than today. I used the following: " Furthermore, the waiver of fees that you have received " & IIf([Forms]![frm_rptDrugFeeLtr].[WvrExpDate]<='=GETDATE()',"has expired on ","will expire on ") & [Forms]![frm_rptDrugFeeLtr].[WvrExpDate] & .... When I put the = in front of GETDATE, i...
IIF Statement
Hello, In my Access 2003 database I have the following IIF statement in one of the modules. DoCmd.OpenQuery IIf(ans = vbYes, "qryGetMWF", "qryGetWF") This works fine but I want to change it so that a 2nd query is run in both conditions. If True I want the following 2 queries run. qryGetMWF and qryTrainingDatesMWF If False. qryGetWF and qryTrainingDatesWF How does the IIF statement need to be changed? Thanks. -- Regards, Chris "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message news:F4AB3547-70C8-46AC-89DB-...
iif question
This should be an easy one for you gurus... I a cell, I have a NOW() function that keeps updating everytime a value changes. =IF(F22 = "","",NOW()) Isn't there a way to capture the date/time and "lock it" so that it doesn't change? I'm keeping track of user input date & time values. tia, -- JMorrell Take a look here: http://www.mcgimpsey.com/excel/timestamp.html In article <5FD7764D-9253-423F-A09B-3E2598D89A61@microsoft.com>, "JMorrell" <JMorrell@discussions.microsoft.com> wrote: > This should be an easy o...
I need to do an if and statement something like this- if A1=1 and A2x(A1)1>10 then no more than 10, but if A2=2 and A2xA1>25 then no more than 25 so if A2= 12 and A1=1 then the result will be 10 but if A2= 12 and A1=2 then the result will be 24 but if A2= 15 and A1=2 then the reult will be 25 I know it is a lot, but you can guys figure it out. Try this: =MIN((A1=1)*A2,10)+MIN(2*(A1=2)*A2,25) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------...
Downloading statement shows items to review but I can't find them (2007)!
The last few days, when I download my checking account statementm it succeeds and shows items to review (4 yesterday, 5 today) but the downloaded items are not showing up in the account. The last item in the register is from 11/21 (5 days ago). I have the current vesion (MSM Deluxe 2007, Version 16...1024). I've been using MSM for many years. Anyone have any ideas on this? Thanks, Gary In microsoft.public.money, GarDavis wrote: >The last few days, when I download my checking account statementm it >succeeds and shows items to review (4 yesterday, 5 today) but the >downloaded ...
Macro and If Statement
Here's the situation: I have a worksheet that will have an "X" in either cell F3 or G3 or H3. What I would like to do (via a macro), is to examine F3, G3 and H3, and if there is an "X" in anyone of those cells, to run another specific macro. For example: Run a macro (for example called "totalsummary")to determine... If there is an "X" in F3, then run a macro called "summaryEL"; If there is an "X" in G3, then run a macro called "summaryHT"; If there is an "X" in H3, then run a macro called "summaryFA&quo...
IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...
IIF(AND) statement
Hi I have a number of things I need to test in the query. I need to use IIF statement with AND (for example in Excel you can do it),. Example I need this IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2") how to do it in access? -- Greatly appreciated Eva On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote: > Hi > I have a number of things I need to test in the query. I need to use IIF > statement with AND (for example in Excel you can do it),. > Example > I need this > IIf (and(cat="H...
IF statement question
Is it possible to test for more than 2 different conditions? ...example total number of hours paid hours 5 hrs 5 hrs 10 hrs 9.5 hrs 12 hrs 10 mins 12 hrs IF "total number of hours" <6 this will be "paid hours" ..... IF "total number of hours" >6 but <12 "paid hours" = "total number of hours" -.5 IF "total number of hours" >12 "paid hours" = "total number of hours" -.1 -----= Poste...
iIF clause
Can you help me build the right IIf field in the query with the following conditions : The field DDU consissts of : DDU :[exworks]*2+0,4+0,01 To the above expression i must also add 0,001 if size = 205, etc according to the following table : 205 0.001 60 0.001 20 0.009 1 1.32 4 0,32 0,5 1,67 However Acces does not accept my query, obvioulsy i have errors : DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001) Will you help me ? ...
What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...
IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the IIF is false I would like the text (mainly 1 or 2 words) not to be underlined. How do I end the IIF so the text is not underlined? Thanks. John I am not sure how you mean, but if you want this underlined based on the true or false then use the properties box and choose the text decoration item and write an expression in there. "JohnE" <JohnE@discussions.microsoft.com> wrote in message news:5C6EEFBC-0DC0-41C0-A35D-3A01209E568A@microsoft.com... > Hello. Doing a report that uses an IIF...
Money 2004 does not see downloaded bank statement...
I just upgraded (a couple of weeks ago) from 2003 to 2004. I am running Windows XP Pro. In 2003, I used to use Internet Explorer, go to my bank, select my date range, and download my transactions for "Money 99 or higher". If I had Money 2003 open, it would automatically see the downloaded file and import the transactions. I just did the EXACT same thing - about 10 times - in Money 2004 and it does NOT see the downloaded file or import the transactions. The download happens so fast that I don't know where the browser saved off the file. A quick search of the hard drive d...
Changing Background Color in "IF" statement
Greetings, Is there a way to color cell background from within an "IF" statement ? Thanks, -Paul Hi not with a formula. But use 'Format - Conditional Format' for this -- Regards Frank Kabel Frankfurt, Germany Paul Pruchnik wrote: > Greetings, > > Is there a way to color cell background from within an "IF" statement > ? > > Thanks, > > -Paul Paul Not without using Conditional Formatting or VBA. Formulas alone cannot change cell formats. You can place an "IF" statement in CF "formula is" Or perhaps just a &quo...
Add qualifier to andif statement
I have a spreadsheet formua that I'd like to add a test to, for name in another column. The formula is =SUMIF($F$3:$F$218,">="&A3,$G$3:$G$218) -SUMIF($F$3:$F$218,">"&B2,$G$3:$G$218) In this case I want to test a name in column H and only count G if name is equal So far I know a bunch of ways....that won't work..LOL....can someone help me out thanks. Steve "litngldy" wrote: > I have a spreadsheet formua that I'd like to add a test to, for name in > another column. > The formula is =SUMIF($F$3:$F$218,">="...
Sum(IIF statement
I have a large table that I want to query 2 things from. Please be patient because I am pretty new at this. I need to count the number of times that something appears in some fields in the table. Below is what I have tried (keep in mind there are many more fields to count)... test: [Exp1]=Sum(IIf([Account number] Is Not Null,1,0)+(IIf([Address] Is Not Null,1,0)) I would like my output to look like: KEY TEST 1 2 2 0 3 5 etc... And then I want to look in each of those same fields and if there is data concatenate it into one field on the query output and...
help with nested iif statement
I have a table and I want to create a query that has a new field " Type of Shipment" base on a field "Item Category" If the item category field equals ZDIR, ZTRN ...etc up to 23 different item categories the "Type of Shipment field will equal DIRECT else I would like it to return "Stock" Any help will be greatly appreciated. Thanks Hi, for the iif statement. What you can do is: IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','') Having a long nested if is a headache. What i can suggest ...
IIF with condition
A criteria "<100" in a numeric field is successful. Using it in an IIF statement as IIF(1=1,<100) dosen't work. What is the proper syntax? Dave Dave wrote: > A criteria "<100" in a numeric field is successful. Using it in an IIF > statement as IIF(1=1,<100) dosen't work. What is the proper syntax? > > Dave > What are you trying to do? "<100" in a criteria, for the field [MoneyBet] is saying, give me all records that have a number less than 100, in [MoneyBet]. an iff statement is: iff(criteria, , answer if criteria is...
Search statement not working
I have a search form and this code is on the keypress of a textbox Me.RecordSource = "SELECT * FROM [qrysearchcomplaint] WHERE [txtcomplainant] like ""*" & Me.txtSearch.Value & "*"" or [txtbusinessname] like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" & Me.txtSearch.Value & "*"" Or [txtrefnbr] Like "" * " & Me.txtSearch.Value & " * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * &quo...
Statements Not Printing Properly
Take the following example. Customer A has invoice from November 2004 which was paid in January 2005. We run an aging as of December 31, 2004. This places the Invoice in 31-60 aging bucket. Print an historical TB for this customer as of Dec 31, 2004 and it prints that invoices in 31-60. Print a statement for that customer using 12/31/2004 with options 'Print Past Due Balance,' 'Show Applied Payments' and 'Exclude Fully Applied Payments'] and it says no transactions to print. If I unmark 'Print Past Due Balance,' the invoice prints on the statement a...
If Statement in a macro
I want to put the following formula into a macro, what commands do use? If Z1=1, goto A1, if z1=100 goto A100. Can someone help please? Thank you -- colinfraser@prosealuk.co ----------------------------------------------------------------------- colinfraser@prosealuk.com's Profile: http://www.officehelp.in/member.php?userid=523 View this thread: http://www.officehelp.in/showthread.php?t=127235 Posted from - http://www.officehelp.i If Range("Z1").Value = 1 Then Application.Goto Range("A1") ElseIf Range("Z1").Value = 100 Then Appli...
Multi-value paramter syntax...help
How do I write the syntax for a stored procedure that has one parameter but accepts multiple values. I am running a report from reporting services that can pass multiple values or pass all values to the stored procedure stored procedure 1: exec getcolor @color = blue....this runs fine. stored procedure 2: exec getcolors @colors in('red', 'white', 'blue').........bombs code is create procedure getcolors @colors nvarchar (50) as (select * from hrs_by_activity where color in(@colors) ) need help making this work. thanks in advance O...
How long to you all keep bank & annuity statements?
I realize that this is probably a little off-topic, but as I keep all my msmoney accounts up to date, how long do you all keep statements from banks, stock brokers, annuities, etc? I just opened my filing cabinet and realized that I have records going back MANY years. Do I really need to keep them, or am I being anal? Dick In microsoft.public.money, Dick wrote: >I realize that this is probably a little off-topic, but as I keep all my >msmoney accounts up to date, how long do you all keep statements from banks, >stock brokers, annuities, etc? I would keep the statements fo...
iif and between
hello i have a set of numbers 1-52 in a field. when i query criteria ...between [weekvar] and [weekvar]-13....i get the answer i need with the query: a set of numbers. However, when i put this same condition within an iif statement in the query criteria, it doesn't work and comes up with no results. iif([weekvar]>13, between [weekvar] and [weekvar]-13, 100). The "100" answer will work, but the "between" will not query anything. What i am trying to do is to return a set of numbers while using iif(. I don't understand why it'll work when...
BIG IF statement problem
I can not get this if statement to work any suggestions. =IF(M11>25%,"1%",IF(M11>33%,"4%",IF(M11>40%,"4.5%",IF(M11>45%,"5%",IF(M11>55%,"7%",IF(M11>60%,"7%",IF(M11>65%,"7.5%",IF(M11>70%,"8%",IF(M11>75%,"8.5%",IF(M11>80%,"9%",IF(M11>85%,"10%",IF(M11>90%,"11%",IF(M11>95%,"13%",IF(M11>100%,"15%",IF(M11>110%,"16%",IF(M11>120%,"18%",IF(M11>130%,"19%",IF(M11>140%,"20%"...
IIf
I am creating a report that I want to spell out "has expired on" and the date that I entered in the form if that date is prior to today; or to spell out "will expire on" and the date that I entered in the form if that date is greater than today. I used the following: " Furthermore, the waiver of fees that you have received " & IIf([Forms]![frm_rptDrugFeeLtr].[WvrExpDate]<='=GETDATE()',"has expired on ","will expire on ") & [Forms]![frm_rptDrugFeeLtr].[WvrExpDate] & .... When I put the = in front of GETDATE, i...
IIF Statement
Hello, In my Access 2003 database I have the following IIF statement in one of the modules. DoCmd.OpenQuery IIf(ans = vbYes, "qryGetMWF", "qryGetWF") This works fine but I want to change it so that a 2nd query is run in both conditions. If True I want the following 2 queries run. qryGetMWF and qryTrainingDatesMWF If False. qryGetWF and qryTrainingDatesWF How does the IIF statement need to be changed? Thanks. -- Regards, Chris "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message news:F4AB3547-70C8-46AC-89DB-...
iif question
This should be an easy one for you gurus... I a cell, I have a NOW() function that keeps updating everytime a value changes. =IF(F22 = "","",NOW()) Isn't there a way to capture the date/time and "lock it" so that it doesn't change? I'm keeping track of user input date & time values. tia, -- JMorrell Take a look here: http://www.mcgimpsey.com/excel/timestamp.html In article <5FD7764D-9253-423F-A09B-3E2598D89A61@microsoft.com>, "JMorrell" <JMorrell@discussions.microsoft.com> wrote: > This should be an easy o...