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
838 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:
SQL syntax for subforms of subforms that must relate.
Hello. I am working on a meeting dbase and I have got to this point with help from this site but am stuck here. Due to the intricacy of some agendas, I have had to add levels so to speak with more agenda tables. I have 5 levels total so far. Each of the agenda level tables are set up the same as the original agenda table but with identifying level names. tblAgendaItems AgendaID (autonumber, primary key) MtgID (foreign key) AgendaItem AgendaItemName AgendaItemMinutes tblAgendaItemsLevel2 AgendaIDLevel2 (foreign key to AgendaID) MtgID (Number) AgendaItemLevel2 AgendaItemNameLe...
Help with incorrect information from statement download
I installed money 05 and when I selected my bank money goes and contacts my bank and successfully gets my accounts information. The balance is correct, what is wrong is that it list deposits on the same side as my withdrawals. If I try to correct this manually it messes up my balances. I know the information from my bank is correct because on different computer I have been using money 03 please advise. Joshua Wickard wrote: > I installed money 05 and when I selected my bank money > goes and contacts my bank and successfully gets my > accounts information. The balance i...
TransferSpreadsheet range syntax
I would like to use DoCmd.TransferSpreadsheet acImport to access 2 columns of data in a spreadsheet. I’m having trouble with the range. I can import one column/field, but I can’t get the syntax for two fields. One field, "Sheet3!A1:A1260” works great. Anyone know how to do this? Is it possible? Thanks, Mark Have you tried - "Sheet3!A1:B1260" "Mark CHP IMD" <MarkCHPIMD@discussions.microsoft.com> wrote in message news:B7592562-92CB-4490-A597-F797BBEAD037@microsoft.com... >I would like to use DoCmd.TransferSpreadsheet acImport to access 2 columns >of ...
Need help with SELECT statement. 04-19-10
Hi, I need some help with the select statement given the business rules and desired result below. Thank you so much for your help in advance. Please see the business rules below. IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL DROP TABLE #Office CREATE TABLE #Office ( CompanyId INT NULL, OfficeId INT NULL, CorpOffice BIT NULL, DBAName VARCHAR(15) NULL ) go INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1') INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2') INSERT INTO #Office VALUES (1323, 1306, 0, 'Te...
Deleting with an IF Statement
Hello Everyone, I am trying to write an IF statement that will delete certain rows i they match the false criteria. Ex. IF cells A7:A12>=500, if true keep those rows, if false delete thos rows. I am looking to delete the entire row if the statement is false. Ca someone please help? Thanks -- Message posted from http://www.ExcelForum.com How about data>filter>autofilter>custom>go from there. Record a macro if desired. -- Don Guillett SalesAid Software donaldb@281.com "garungaha1 >" <<garungaha1.18rtos@excelforum-nospam.com> wrote in message news:g...
Ruling out combinations without if statements?
Is there a way to rule out combinations with out having to use an if then block. My form has 5 different text boxes. I want the user to enter the information they wish to search by and have which ever text boxes that are not null have that information passed to an SQL statement to search by. The problem is, if I do an if then block for txtdate txtPIN txtSN txtTicketNumber txtStaff there's 125 possible combination of null vs not null within those 5 text boxes. Is there any way to make an SQL statement that does something like SELECT table.* FROM table WHERE field LIKE txtDate IF t...
IF statement that yields a value based on a formula
Help! If cell B12 >= 0, then I need the answer to be calculated based on the formual "B12 * .40". If cell B12 < 0, then the answer is zero. How do I do this? -- barbara h IF(B12>=0,B12*0.4,IF(B12,0,0)) Click yes if helped -- Greatly appreciated Eva "barbara h" wrote: > Help! If cell B12 >= 0, then I need the answer to be calculated based on the > formual "B12 * .40". If cell B12 < 0, then the answer is zero. > > How do I do this? > -- > barbara h Hi, Try this =MAX(0,B12*0.4) -- Mike ...
How to delete phantom statement?
In M2007 I have a downloaded OFX statement for an investment account that won't go away. It says there are 2 transactions, but if I click on them, there are none. If I try to delete the downloaded statement, there is no error, but the statement does not go away. I've tried repairing the file - no errors, but statement is still there. I removed online services for that FI, restarted Money, and set up online services again but its still there. I also had a few new transactions come in, and the new statement downloaded fine, but after it was processed the old statement is still hangi...
Can't print Monthly Statements
Hello When we try to print current or past monthly statements, we are getting the following error message: "The year you've selected for an open-year column is invalid for Statement - Catering" Help?! ...
Cannot download statements
Not sure what is causing this: I download statements from several financial institutions. Until recently this worked with very few problems. Over the last week or so, none of them have successfully downloaded. I know this is not much to go on, but any idea? I am currently using Money Plus Deluxe Trial Version (and no it has not expired yet !) My O/S is Windows XP SP3 Thanks In microsoft.public.money, Brian wrote: >Not sure what is causing this: > >I download statements from several financial institutions. Until recently >this worked with very few problems. Over the la...
Questions on format of Select statement
SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution from tblCalls, in dbCallLogs WHERE tblCalls.colResolution from tblCalls, in dbCallLogs where tblCalls.ColTime >= #12/1/2006# <= #3/2/2007# ORDER BY tblCalls.ColTime DESC; I want to know if this is right? I notice in some queries the table name before the column name is missing? Also when do you have to put in the database name? Is it the same in Access as SQL Server? I want the most recent first. I want those records betweeb 12.1.06 and 3.01.07. thanks, On Wed, 21 Mar 2007 13:30:07 -0700, Janis <Janis...
Sudden Loss of Direct Statements
My citi credit card statement used to synch just pefectly with money 2003 dx on my Win XP machine. Suddenly, it stopped. I've checked with the bank, and the account was locked because of "too many incorrect login" attempts. After unlocking, I changed the password and tried loging in via the web. That works. Using same information in money yields nothing except lockouts. I've verified the login information several times. It just simply doesn't seem to work from money. (I've got 9 other direct synching accounts from this file that still work just fine...) Anyone ev...
Shorcuts or Macro for Creating If Else Statements
I create if else statements often. But I find it tedious to have to type them and redefine them all the time for different workbooks. For example, let's say you want this type of if else condition for calculating price to earnings: a1=price; b1=earnings =if ( iserror( a1/b1),"n/a", if (or( a1/b1<0, a1/b1>100),"nmf", a1/b1)) Is there a way of shortening this? What I mean is, of course I can copy this down the column and calculate a bunch of P/Es for a bunch of companies. I'm trying to see if there is a macro or short cut that will help you create if el...
Statements not downloading, how to get help?
I am using money 2006 and I have not been able to get my banking accounts to download new transactions for several weeks, where can I go for help? Should I call my bank? Is there a money support email or phone number? Does anyone have any suggestions. I should note that other accounts from my brokerage company are downloading correctly, its just my bank accounts that are not updating correctly. Thanks, Sam In microsoft.public.money, Sam wrote: > I am using money 2006 and I have not been able to get my banking >accounts to download new transactions for several weeks, where can I go...
If Statement 01-13-10
Need help with this formula? Need to calculate a formula for CELL A based upon the following IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 tss wrote: > Need help with this formula? > Need to calculate a formula for CELL A based upon the following > IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 =IF(AND(B1="STOCK",C1>=1,C1<25),D1*4,"**No idea what you want here**") IF( AND( logical1, logical2, logical3 ), value_if_true, **value_if_false** ) Put this in A1: =3DIF(AND(B1=3D"Stock",C1>=3D1,C1<...
Account Statement
Do you know of any way to add the journaled receipt to the Account Statement ideally in a smaller version and say four to a page? Is this even possible? ...
(Follow on from previous post) Using IF statements
I have an alternating set of three columns, labelled (in row 5) Beg, Mid and End. They represent the beginning, middle and end of each month. Row 24 contains values for these periods. I would like to calculate the average for all Beg columns, for all Mid columns and for all End columns. In a previous post I got as far as putting following formulae in three different cells: =AVERAGE(IF('Cash flow'!5:5="Beg",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5="Mid",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5=&...
Stop Downloading Statements in Money 2004
I don't want to download statements in Money 2004. I've tried to find somewhere to stop this, but everytime I stop the statement downloads, I stop online bill pay. Is it possible to seperate the two? ...
IIF Function in query
Hello all, I have a table which I have the default value 0, If these a way to use the function "IIF" in query to keep the cell blank when the cell contain a 0 as the default value? Because, if there is no quanity entered, I don't want to see the default value in my report Thanking you for your help! Fred's That sort of begs the question "Why have a default value if you don't want it?" IIf([Field] = 0, Null, [Field]) To remove the unwanted 0s, you can write an Update query: UPDATE MyTable SET MyField = Null WHERE MyField = 0 Of course, what happens if so...
Sumif statement
Is it possible to create a sumif statement that sums based on multiple conditions? ie. using a formula such as SUMIF ($A$2:$A$80,$A119,M$2:M$80) works fine, however, if I now want to add a further condition to this statement that would execute this formula only if say the values in $k$2:$k$80 are equal to "ABC". I assume some conditional and statement would be used or possibly an array formula, however, no success thus far. Ian Ian, SUMPRODUCT does it =SUMPRODUCT(($A$2:$A$80=$A119)*($K$2:$K$80="ABC")*(M$2:M$80)) Not even an array formula. -- HTH Bob Phillip...
IF statements #14
Please tell me why the following is not valid? =IF(B2>2,[B2^-.10],[B2^.10]) The problem is the square brackets: replace them with parentheses or remove them, and you're good to go. ...
Cannot Delete Online Statement
I am using Money 2002. I exported an online statement from my bank. For some unknown reason, a whole year's worth of transactions were dowloaded. (Over 1000 transactions). Only 8 of these are new. I searched for online help and found information on Microsoft that said to right click on "Transactions to Read" and choose delete from the drop-down menu. My right click menu only has "Read Statement". There is no delete option. I can't find a way to uncheck all transactions and then check the eight I need to update. So, if can't delete the statement, I will ha...
If statement with a color solution
Can an IF statment be written such that when a condition is met, its color is changed (from the Black default)? B1 = 21 =If(B1=21,"Red","Blue") where the word Red is displayed/printed in red. B1 = 22 =If(B1=21,"Red","Blue") here, the word Blue would be displayed/printed in blue OS = XP Hm MCE; Office 2007 Thank You! Wayne G. Dengel Look up conditional formatting in help -- Regards, Peo Sjoblom <wgd.roaming@verizon.net> wrote in message news:hb17g45342f6f6tpnhhlb6q3eehbtljbes@4ax.com... > Can an IF statment be written such...
SQL Syntax: Can anyone help?
Hi All Access 2007 Windows XP I have a table called: UsystblApplication Which has 2 fields called: 1. FieldName 2. Number I want to be able to Update the reccord from a form which has a control called: 1. Me.txtLastUsedRecordProject And do it from a Command Button. I am using the code below, and although I get the warning that I won't be able to undo it. It just doesn't not alter. Here is the code I am using Can anyone help? Private Sub CmdLastUsedRecord_Click() Dim strSQL As String strSQL = "UPDATE UsystblApplication SET [Number]= " _ & txtLastUsedReco...
e-mailing customer statements in 10.0
Hello: A GP 10.0 client has one customer that they have recorded with an e-mail address. All of their hundreds of other customers in GP do not have an e-mail address recorded in GP. When they ran statements today, they got an error preventing them from printing the statements. I cannot remember what the error was. But, once we clicked the E-Mail Options button afterward and "told" GP to print (instead of e-mail) statements, the statements printed fine. Is there a special "formatting" in the Customer Maintenance window for entering e-mail addresses? Otherwise, w...
Hello. I am working on a meeting dbase and I have got to this point with help from this site but am stuck here. Due to the intricacy of some agendas, I have had to add levels so to speak with more agenda tables. I have 5 levels total so far. Each of the agenda level tables are set up the same as the original agenda table but with identifying level names. tblAgendaItems AgendaID (autonumber, primary key) MtgID (foreign key) AgendaItem AgendaItemName AgendaItemMinutes tblAgendaItemsLevel2 AgendaIDLevel2 (foreign key to AgendaID) MtgID (Number) AgendaItemLevel2 AgendaItemNameLe...
Help with incorrect information from statement download
I installed money 05 and when I selected my bank money goes and contacts my bank and successfully gets my accounts information. The balance is correct, what is wrong is that it list deposits on the same side as my withdrawals. If I try to correct this manually it messes up my balances. I know the information from my bank is correct because on different computer I have been using money 03 please advise. Joshua Wickard wrote: > I installed money 05 and when I selected my bank money > goes and contacts my bank and successfully gets my > accounts information. The balance i...
TransferSpreadsheet range syntax
I would like to use DoCmd.TransferSpreadsheet acImport to access 2 columns of data in a spreadsheet. I’m having trouble with the range. I can import one column/field, but I can’t get the syntax for two fields. One field, "Sheet3!A1:A1260” works great. Anyone know how to do this? Is it possible? Thanks, Mark Have you tried - "Sheet3!A1:B1260" "Mark CHP IMD" <MarkCHPIMD@discussions.microsoft.com> wrote in message news:B7592562-92CB-4490-A597-F797BBEAD037@microsoft.com... >I would like to use DoCmd.TransferSpreadsheet acImport to access 2 columns >of ...
Need help with SELECT statement. 04-19-10
Hi, I need some help with the select statement given the business rules and desired result below. Thank you so much for your help in advance. Please see the business rules below. IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL DROP TABLE #Office CREATE TABLE #Office ( CompanyId INT NULL, OfficeId INT NULL, CorpOffice BIT NULL, DBAName VARCHAR(15) NULL ) go INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1') INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2') INSERT INTO #Office VALUES (1323, 1306, 0, 'Te...
Deleting with an IF Statement
Hello Everyone, I am trying to write an IF statement that will delete certain rows i they match the false criteria. Ex. IF cells A7:A12>=500, if true keep those rows, if false delete thos rows. I am looking to delete the entire row if the statement is false. Ca someone please help? Thanks -- Message posted from http://www.ExcelForum.com How about data>filter>autofilter>custom>go from there. Record a macro if desired. -- Don Guillett SalesAid Software donaldb@281.com "garungaha1 >" <<garungaha1.18rtos@excelforum-nospam.com> wrote in message news:g...
Ruling out combinations without if statements?
Is there a way to rule out combinations with out having to use an if then block. My form has 5 different text boxes. I want the user to enter the information they wish to search by and have which ever text boxes that are not null have that information passed to an SQL statement to search by. The problem is, if I do an if then block for txtdate txtPIN txtSN txtTicketNumber txtStaff there's 125 possible combination of null vs not null within those 5 text boxes. Is there any way to make an SQL statement that does something like SELECT table.* FROM table WHERE field LIKE txtDate IF t...
IF statement that yields a value based on a formula
Help! If cell B12 >= 0, then I need the answer to be calculated based on the formual "B12 * .40". If cell B12 < 0, then the answer is zero. How do I do this? -- barbara h IF(B12>=0,B12*0.4,IF(B12,0,0)) Click yes if helped -- Greatly appreciated Eva "barbara h" wrote: > Help! If cell B12 >= 0, then I need the answer to be calculated based on the > formual "B12 * .40". If cell B12 < 0, then the answer is zero. > > How do I do this? > -- > barbara h Hi, Try this =MAX(0,B12*0.4) -- Mike ...
How to delete phantom statement?
In M2007 I have a downloaded OFX statement for an investment account that won't go away. It says there are 2 transactions, but if I click on them, there are none. If I try to delete the downloaded statement, there is no error, but the statement does not go away. I've tried repairing the file - no errors, but statement is still there. I removed online services for that FI, restarted Money, and set up online services again but its still there. I also had a few new transactions come in, and the new statement downloaded fine, but after it was processed the old statement is still hangi...
Can't print Monthly Statements
Hello When we try to print current or past monthly statements, we are getting the following error message: "The year you've selected for an open-year column is invalid for Statement - Catering" Help?! ...
Cannot download statements
Not sure what is causing this: I download statements from several financial institutions. Until recently this worked with very few problems. Over the last week or so, none of them have successfully downloaded. I know this is not much to go on, but any idea? I am currently using Money Plus Deluxe Trial Version (and no it has not expired yet !) My O/S is Windows XP SP3 Thanks In microsoft.public.money, Brian wrote: >Not sure what is causing this: > >I download statements from several financial institutions. Until recently >this worked with very few problems. Over the la...
Questions on format of Select statement
SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution from tblCalls, in dbCallLogs WHERE tblCalls.colResolution from tblCalls, in dbCallLogs where tblCalls.ColTime >= #12/1/2006# <= #3/2/2007# ORDER BY tblCalls.ColTime DESC; I want to know if this is right? I notice in some queries the table name before the column name is missing? Also when do you have to put in the database name? Is it the same in Access as SQL Server? I want the most recent first. I want those records betweeb 12.1.06 and 3.01.07. thanks, On Wed, 21 Mar 2007 13:30:07 -0700, Janis <Janis...
Sudden Loss of Direct Statements
My citi credit card statement used to synch just pefectly with money 2003 dx on my Win XP machine. Suddenly, it stopped. I've checked with the bank, and the account was locked because of "too many incorrect login" attempts. After unlocking, I changed the password and tried loging in via the web. That works. Using same information in money yields nothing except lockouts. I've verified the login information several times. It just simply doesn't seem to work from money. (I've got 9 other direct synching accounts from this file that still work just fine...) Anyone ev...
Shorcuts or Macro for Creating If Else Statements
I create if else statements often. But I find it tedious to have to type them and redefine them all the time for different workbooks. For example, let's say you want this type of if else condition for calculating price to earnings: a1=price; b1=earnings =if ( iserror( a1/b1),"n/a", if (or( a1/b1<0, a1/b1>100),"nmf", a1/b1)) Is there a way of shortening this? What I mean is, of course I can copy this down the column and calculate a bunch of P/Es for a bunch of companies. I'm trying to see if there is a macro or short cut that will help you create if el...
Statements not downloading, how to get help?
I am using money 2006 and I have not been able to get my banking accounts to download new transactions for several weeks, where can I go for help? Should I call my bank? Is there a money support email or phone number? Does anyone have any suggestions. I should note that other accounts from my brokerage company are downloading correctly, its just my bank accounts that are not updating correctly. Thanks, Sam In microsoft.public.money, Sam wrote: > I am using money 2006 and I have not been able to get my banking >accounts to download new transactions for several weeks, where can I go...
If Statement 01-13-10
Need help with this formula? Need to calculate a formula for CELL A based upon the following IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 tss wrote: > Need help with this formula? > Need to calculate a formula for CELL A based upon the following > IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 =IF(AND(B1="STOCK",C1>=1,C1<25),D1*4,"**No idea what you want here**") IF( AND( logical1, logical2, logical3 ), value_if_true, **value_if_false** ) Put this in A1: =3DIF(AND(B1=3D"Stock",C1>=3D1,C1<...
Account Statement
Do you know of any way to add the journaled receipt to the Account Statement ideally in a smaller version and say four to a page? Is this even possible? ...
(Follow on from previous post) Using IF statements
I have an alternating set of three columns, labelled (in row 5) Beg, Mid and End. They represent the beginning, middle and end of each month. Row 24 contains values for these periods. I would like to calculate the average for all Beg columns, for all Mid columns and for all End columns. In a previous post I got as far as putting following formulae in three different cells: =AVERAGE(IF('Cash flow'!5:5="Beg",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5="Mid",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5=&...
Stop Downloading Statements in Money 2004
I don't want to download statements in Money 2004. I've tried to find somewhere to stop this, but everytime I stop the statement downloads, I stop online bill pay. Is it possible to seperate the two? ...
IIF Function in query
Hello all, I have a table which I have the default value 0, If these a way to use the function "IIF" in query to keep the cell blank when the cell contain a 0 as the default value? Because, if there is no quanity entered, I don't want to see the default value in my report Thanking you for your help! Fred's That sort of begs the question "Why have a default value if you don't want it?" IIf([Field] = 0, Null, [Field]) To remove the unwanted 0s, you can write an Update query: UPDATE MyTable SET MyField = Null WHERE MyField = 0 Of course, what happens if so...
Sumif statement
Is it possible to create a sumif statement that sums based on multiple conditions? ie. using a formula such as SUMIF ($A$2:$A$80,$A119,M$2:M$80) works fine, however, if I now want to add a further condition to this statement that would execute this formula only if say the values in $k$2:$k$80 are equal to "ABC". I assume some conditional and statement would be used or possibly an array formula, however, no success thus far. Ian Ian, SUMPRODUCT does it =SUMPRODUCT(($A$2:$A$80=$A119)*($K$2:$K$80="ABC")*(M$2:M$80)) Not even an array formula. -- HTH Bob Phillip...
IF statements #14
Please tell me why the following is not valid? =IF(B2>2,[B2^-.10],[B2^.10]) The problem is the square brackets: replace them with parentheses or remove them, and you're good to go. ...
Cannot Delete Online Statement
I am using Money 2002. I exported an online statement from my bank. For some unknown reason, a whole year's worth of transactions were dowloaded. (Over 1000 transactions). Only 8 of these are new. I searched for online help and found information on Microsoft that said to right click on "Transactions to Read" and choose delete from the drop-down menu. My right click menu only has "Read Statement". There is no delete option. I can't find a way to uncheck all transactions and then check the eight I need to update. So, if can't delete the statement, I will ha...
If statement with a color solution
Can an IF statment be written such that when a condition is met, its color is changed (from the Black default)? B1 = 21 =If(B1=21,"Red","Blue") where the word Red is displayed/printed in red. B1 = 22 =If(B1=21,"Red","Blue") here, the word Blue would be displayed/printed in blue OS = XP Hm MCE; Office 2007 Thank You! Wayne G. Dengel Look up conditional formatting in help -- Regards, Peo Sjoblom <wgd.roaming@verizon.net> wrote in message news:hb17g45342f6f6tpnhhlb6q3eehbtljbes@4ax.com... > Can an IF statment be written such...
SQL Syntax: Can anyone help?
Hi All Access 2007 Windows XP I have a table called: UsystblApplication Which has 2 fields called: 1. FieldName 2. Number I want to be able to Update the reccord from a form which has a control called: 1. Me.txtLastUsedRecordProject And do it from a Command Button. I am using the code below, and although I get the warning that I won't be able to undo it. It just doesn't not alter. Here is the code I am using Can anyone help? Private Sub CmdLastUsedRecord_Click() Dim strSQL As String strSQL = "UPDATE UsystblApplication SET [Number]= " _ & txtLastUsedReco...
e-mailing customer statements in 10.0
Hello: A GP 10.0 client has one customer that they have recorded with an e-mail address. All of their hundreds of other customers in GP do not have an e-mail address recorded in GP. When they ran statements today, they got an error preventing them from printing the statements. I cannot remember what the error was. But, once we clicked the E-Mail Options button afterward and "told" GP to print (instead of e-mail) statements, the statements printed fine. Is there a special "formatting" in the Customer Maintenance window for entering e-mail addresses? Otherwise, w...