if then else statement

On my report I have a field that I need help with.  I have written an =If 
statement to try and capture all of the variables, but it is too long, so now 
I need to write the same expression in code.  Here is what I have in my =If 
statement (which resides in the Detail area of the Report):

=IIf([costcode]="013210" And 
[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" 
And 
[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" 
And 
[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301" 
And 
[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101" 
And 
[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101" 
And 
[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211" 
And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311" 
And 
[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))

So, what it says is check this cost code and cost type and then perform the 
calculation of txtActualCost divided by the txt box I've named that 
corresponds to the hours for this cost code/cost type that resides in the 
Report Header area of the report.  

I have tried:

If cost code = 013210 and
   costtype = 05320
Then txtActualCost/txtCraftLabHrs
Else 
End If

I hope someone can help me with this.  The txtActualCost is set as a 
currency field, the txtCraftLabHrs is set as standard.
Thanks.
0
Utf
3/16/2010 2:20:10 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

6 Replies
898 Views

Similar Articles

[PageSpeed] 48

Hi Alberta Rose,
you can use the if then else in this way

If costcode = "013210" and  costtype = "05320" Then
    yourfield=txtActualCost/txtCraftLabHrs
Elseif costcode="020110" and  costtype = "05320" Then
    yourfield=txtActualCost/txtFirewatchLabAct
elseif etc.etc.

End If

or you can use a select statement

tmp_var= costcode & costtype
select case tmp_var
    case "01321005320"
        yourfield=txtActualCost/txtCraftLabHrs
    case "02011005320"
        yourfield=txtActualCost/txtFirewatchLabAct
    case etc. etc.

    case else
        do something if none of the above. You can also avoid the case else 
if you are sure that a condions is always met
end select

HTH Paolo

"Alberta Rose" wrote:

> On my report I have a field that I need help with.  I have written an =If 
> statement to try and capture all of the variables, but it is too long, so now 
> I need to write the same expression in code.  Here is what I have in my =If 
> statement (which resides in the Detail area of the Report):
> 
> =IIf([costcode]="013210" And 
> [costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" 
> And 
> [costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" 
> And 
> [costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301" 
> And 
> [costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101" 
> And 
> [costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101" 
> And 
> [costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211" 
> And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311" 
> And 
> [costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))
> 
> So, what it says is check this cost code and cost type and then perform the 
> calculation of txtActualCost divided by the txt box I've named that 
> corresponds to the hours for this cost code/cost type that resides in the 
> Report Header area of the report.  
> 
> I have tried:
> 
> If cost code = 013210 and
>    costtype = 05320
> Then txtActualCost/txtCraftLabHrs
> Else 
> End If
> 
> I hope someone can help me with this.  The txtActualCost is set as a 
> currency field, the txtCraftLabHrs is set as standard.
> Thanks.
0
Utf
3/16/2010 3:34:01 PM
On Tue, 16 Mar 2010 07:20:10 -0700, Alberta Rose
<AlbertaRose@discussions.microsoft.com> wrote:

>On my report I have a field that I need help with.  I have written an =If 
>statement to try and capture all of the variables, but it is too long, so now 
>I need to write the same expression in code.  Here is what I have in my =If 
>statement (which resides in the Detail area of the Report):
>
>=IIf([costcode]="013210" And 
>[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" 
>And 
>[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" 
>And 
>[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301" 
>And 
>[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101" 
>And 
>[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101" 
>And 
>[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211" 
>And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311" 
>And 
>[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))
>
>So, what it says is check this cost code and cost type and then perform the 
>calculation of txtActualCost divided by the txt box I've named that 
>corresponds to the hours for this cost code/cost type that resides in the 
>Report Header area of the report.  

I've got some real concerns about your table structure!!! If you have multiple
textboxes for the various LabAct fields, I'm guessing that you have multiple
table fields as well: essentially embedding a one to many relationship in a
single record. What if you decide you need another type of LabAct? Change the
structure of your table, all your queries, all your forms, redo all your
calculations??? Ouch!

That said, try using the Switch() function instead. It's often useful in place
of multilevel IIF. It takes arguments in pairs, and evaluates them left to
right; when it first encounters a pair for which the first of the pair is TRUE
it returns the second of that pair and quits. So

=Switch([costcode]="013210" And [costtype]="05320",
[txtActualCost]/[txtCraftLabAct],
[costcode]="020110" And [costtype]="05320",
[txtActualCost]/[txtFirewatchLabAct],
[costcode]="064201" And [costtype]="05320",
[txtActualCost]/[txtCraftSuprLabAct],
[costcode]="061301" And [costtype]="05320",
[txtActualCost]/[txtQAQCSuprvLabAct],
[costcode]="061101" And [costtype]="05310",
[txtActualCost]/[txtSiteTeamLabAct],
[costcode]="031101" And [costtype]="05110",
[txtActualCost]/[txtOfficeTeamLabAct],
[costcode]="042211" And [costtype]="05110", 
[txtActualCost]/[txtEngLabAct],
[costcode]="045311" And [costtype]="05130",
[txtActualCost]/[txtThirdPartyLabAct],
[costcode]="032101" And [costtype]="05110",
[txtActualCost]/[txtProcurementLabAct],
True, Null)

This would be much simpler if you had a table relating Costtype to LabActs -
you could simply join it or use DLookUp.

-- 

             John W. Vinson [MVP]
0
John
3/16/2010 5:20:00 PM
Thanks Paolo.  I tried the first example, and no luck, no error messages, but 
nothing populated in the field.  It has to be the way this report was set up 
and I'm at a loss of what to do next.  Could it be because the LabHrs are 
actually in the Header area of the report and the calculation is in the 
Detail area?

Tks..Laurie


"Paolo" wrote:

> Hi Alberta Rose,
> you can use the if then else in this way
> 
> If costcode = "013210" and  costtype = "05320" Then
>     yourfield=txtActualCost/txtCraftLabHrs
> Elseif costcode="020110" and  costtype = "05320" Then
>     yourfield=txtActualCost/txtFirewatchLabAct
> elseif etc.etc.
> 
> End If
> 
> or you can use a select statement
> 
> tmp_var= costcode & costtype
> select case tmp_var
>     case "01321005320"
>         yourfield=txtActualCost/txtCraftLabHrs
>     case "02011005320"
>         yourfield=txtActualCost/txtFirewatchLabAct
>     case etc. etc.
> 
>     case else
>         do something if none of the above. You can also avoid the case else 
> if you are sure that a condions is always met
> end select
> 
> HTH Paolo
> 
> "Alberta Rose" wrote:
> 
> > On my report I have a field that I need help with.  I have written an =If 
> > statement to try and capture all of the variables, but it is too long, so now 
> > I need to write the same expression in code.  Here is what I have in my =If 
> > statement (which resides in the Detail area of the Report):
> > 
> > =IIf([costcode]="013210" And 
> > [costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" 
> > And 
> > [costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" 
> > And 
> > [costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301" 
> > And 
> > [costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101" 
> > And 
> > [costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101" 
> > And 
> > [costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211" 
> > And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311" 
> > And 
> > [costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))
> > 
> > So, what it says is check this cost code and cost type and then perform the 
> > calculation of txtActualCost divided by the txt box I've named that 
> > corresponds to the hours for this cost code/cost type that resides in the 
> > Report Header area of the report.  
> > 
> > I have tried:
> > 
> > If cost code = 013210 and
> >    costtype = 05320
> > Then txtActualCost/txtCraftLabHrs
> > Else 
> > End If
> > 
> > I hope someone can help me with this.  The txtActualCost is set as a 
> > currency field, the txtCraftLabHrs is set as standard.
> > Thanks.
0
Utf
3/17/2010 5:00:03 PM
Hi John.  Yes I agree, this database setup is not optimal for sure.  It's one 
that I've inherited and have to make work.  The multiple text boxes are only 
on this report.  The table actually is pretty clean as far as setup goes.  I 
have a table with the cost codes/cost types in it.  The LabAct refers to the 
actual labour costs which are in each record of the main table under the 
field of ActualCost.  If I use the =Switch expression, won't I run into the 
same issue of not having enough space in the txt box to insert all the code I 
need (it was about 1/2 of it that I put here as an example)?  I have tried 
the If Then Else coding, and nothing...

Any suggestions?  Thanks  Laurie...

"John W. Vinson" wrote:

> On Tue, 16 Mar 2010 07:20:10 -0700, Alberta Rose
> <AlbertaRose@discussions.microsoft.com> wrote:
> 
> >On my report I have a field that I need help with.  I have written an =If 
> >statement to try and capture all of the variables, but it is too long, so now 
> >I need to write the same expression in code.  Here is what I have in my =If 
> >statement (which resides in the Detail area of the Report):
> >
> >=IIf([costcode]="013210" And 
> >[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" 
> >And 
> >[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" 
> >And 
> >[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301" 
> >And 
> >[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101" 
> >And 
> >[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101" 
> >And 
> >[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211" 
> >And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311" 
> >And 
> >[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))
> >
> >So, what it says is check this cost code and cost type and then perform the 
> >calculation of txtActualCost divided by the txt box I've named that 
> >corresponds to the hours for this cost code/cost type that resides in the 
> >Report Header area of the report.  
> 
> I've got some real concerns about your table structure!!! If you have multiple
> textboxes for the various LabAct fields, I'm guessing that you have multiple
> table fields as well: essentially embedding a one to many relationship in a
> single record. What if you decide you need another type of LabAct? Change the
> structure of your table, all your queries, all your forms, redo all your
> calculations??? Ouch!
> 
> That said, try using the Switch() function instead. It's often useful in place
> of multilevel IIF. It takes arguments in pairs, and evaluates them left to
> right; when it first encounters a pair for which the first of the pair is TRUE
> it returns the second of that pair and quits. So
> 
> =Switch([costcode]="013210" And [costtype]="05320",
> [txtActualCost]/[txtCraftLabAct],
> [costcode]="020110" And [costtype]="05320",
> [txtActualCost]/[txtFirewatchLabAct],
> [costcode]="064201" And [costtype]="05320",
> [txtActualCost]/[txtCraftSuprLabAct],
> [costcode]="061301" And [costtype]="05320",
> [txtActualCost]/[txtQAQCSuprvLabAct],
> [costcode]="061101" And [costtype]="05310",
> [txtActualCost]/[txtSiteTeamLabAct],
> [costcode]="031101" And [costtype]="05110",
> [txtActualCost]/[txtOfficeTeamLabAct],
> [costcode]="042211" And [costtype]="05110", 
> [txtActualCost]/[txtEngLabAct],
> [costcode]="045311" And [costtype]="05130",
> [txtActualCost]/[txtThirdPartyLabAct],
> [costcode]="032101" And [costtype]="05110",
> [txtActualCost]/[txtProcurementLabAct],
> True, Null)
> 
> This would be much simpler if you had a table relating Costtype to LabActs -
> you could simply join it or use DLookUp.
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/17/2010 5:06:04 PM
On Wed, 17 Mar 2010 10:06:04 -0700, Alberta Rose
<AlbertaRose@discussions.microsoft.com> wrote:

>Hi John.  Yes I agree, this database setup is not optimal for sure.  It's one 
>that I've inherited and have to make work.  The multiple text boxes are only 
>on this report.  The table actually is pretty clean as far as setup goes.  I 
>have a table with the cost codes/cost types in it.  The LabAct refers to the 
>actual labour costs which are in each record of the main table under the 
>field of ActualCost.  If I use the =Switch expression, won't I run into the 
>same issue of not having enough space in the txt box to insert all the code I 
>need (it was about 1/2 of it that I put here as an example)?  I have tried 
>the If Then Else coding, and nothing...

The limit is pretty high; I've used some Switch functions much larger than
your posted example. There's a limit of 65536 characters in a textbox
(expressions can't be that big and I can't quickly find the limit but it's
well over 1000 characters). However, if your underlying data is more
normalized than your example suggests, I would hope that you could have a
table-driven solution, looking up the labor cost directly (say by using
DLookUp), using the costcode and costtype as criteria. 

The alternative would be to work through Paolo's VBA code, which can certainly
be made to work.
-- 

             John W. Vinson [MVP]
0
John
3/17/2010 8:10:23 PM
Hi Laurie,
the code I posted must be pasted in the on format event of your report, in 
the section where the data changes. So if you have a single report for your 
costcode etc. put it in the on format of the header section. If in the 
details you have a list of different costcode with different calculations you 
have to add it to the on format of the details.

Cheers Paolo

"Alberta Rose" wrote:

> Thanks Paolo.  I tried the first example, and no luck, no error messages, but 
> nothing populated in the field.  It has to be the way this report was set up 
> and I'm at a loss of what to do next.  Could it be because the LabHrs are 
> actually in the Header area of the report and the calculation is in the 
> Detail area?
> 
> Tks..Laurie
> 
> 
> "Paolo" wrote:
> 
> > Hi Alberta Rose,
> > you can use the if then else in this way
> > 
> > If costcode = "013210" and  costtype = "05320" Then
> >     yourfield=txtActualCost/txtCraftLabHrs
> > Elseif costcode="020110" and  costtype = "05320" Then
> >     yourfield=txtActualCost/txtFirewatchLabAct
> > elseif etc.etc.
> > 
> > End If
> > 
> > or you can use a select statement
> > 
> > tmp_var= costcode & costtype
> > select case tmp_var
> >     case "01321005320"
> >         yourfield=txtActualCost/txtCraftLabHrs
> >     case "02011005320"
> >         yourfield=txtActualCost/txtFirewatchLabAct
> >     case etc. etc.
> > 
> >     case else
> >         do something if none of the above. You can also avoid the case else 
> > if you are sure that a condions is always met
> > end select
> > 
> > HTH Paolo
> > 
> > "Alberta Rose" wrote:
> > 
> > > On my report I have a field that I need help with.  I have written an =If 
> > > statement to try and capture all of the variables, but it is too long, so now 
> > > I need to write the same expression in code.  Here is what I have in my =If 
> > > statement (which resides in the Detail area of the Report):
> > > 
> > > =IIf([costcode]="013210" And 
> > > [costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" 
> > > And 
> > > [costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" 
> > > And 
> > > [costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301" 
> > > And 
> > > [costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101" 
> > > And 
> > > [costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101" 
> > > And 
> > > [costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211" 
> > > And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311" 
> > > And 
> > > [costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))
> > > 
> > > So, what it says is check this cost code and cost type and then perform the 
> > > calculation of txtActualCost divided by the txt box I've named that 
> > > corresponds to the hours for this cost code/cost type that resides in the 
> > > Report Header area of the report.  
> > > 
> > > I have tried:
> > > 
> > > If cost code = 013210 and
> > >    costtype = 05320
> > > Then txtActualCost/txtCraftLabHrs
> > > Else 
> > > End If
> > > 
> > > I hope someone can help me with this.  The txtActualCost is set as a 
> > > currency field, the txtCraftLabHrs is set as standard.
> > > Thanks.
0
Utf
3/18/2010 7:15:01 AM
Reply:

Similar Artilces:

IF statement
In excel If 2 columns have an equal value I want a 3rd column to display the value from a 4th column, what is the syntax for this? One way: C1: =IF(A1=B1,D1,"") In article <25057EF7-E877-4F7D-929E-CF1D09F081E3@microsoft.com>, Ragnar <Ragnar@discussions.microsoft.com> wrote: > In excel If 2 columns have an equal value I want a 3rd column to display the > value from a 4th column, what is the syntax for this? Ragnar, You don't say what you want if the values are not equal but the general syntax for an IF statement is: =IF(test, Do if test true, Do if ...

If Then Else??
Cat Indic Apple Banana Coconut Durian Ripe (R) 10 30 50 70 Unripe (U) 60 40 20 10 Seeded (SD) 70 0 0 80 Seedless(SL) 0 70 70 0 If I have a 1 column for either Fruits (Apple, Banana, Coconut or Durian)and another on Indicators (R, U, SD, SL), at the row that I have maybe listing Apple & Unripe, I want to extract the quantity (60), how do I go about with the If Then Else function? Hi, Am Mon, 19 Mar 2012 07:11:36 GMT schrieb khoo nora: > Cat Indic Apple Banana Coconut Durian > Ripe (R) 10 30 50 70 > Unripe (U) 60 40 20 10 > Seeded (SD) 70 0 0 80 > Seedless(SL) 0 70 70 0 ...

Select statement
Hi Guys, This sql statement is not working, I think is because sales rep id is text field, I really appreciate if someone can help me Thanks Set rsSR = db.OpenRecordset( _ "select SaleRepName, SaleRepID " & _ "from tblMSaleRep " & _ "where SaleRepID = " & Me.TextSalesRepID) > I think is because sales rep id is text field Correct. Enclose the text with double quotes (two sets of quotes: "") "where SaleRepID = """ & Me.TextSalesRepID & """...

if else if...
hello all!! any reason why this should not work correctly ? ;))) tx !!! If aWS.Range("M52").Value = True Then aWS.Range("M80").Value = 5 Else If aWS.Range("M51").Value = True Then aWS.Range("M80").Value = 4 Else If aWS.Range("M50").Value = True Then aWS.Range("M80").Value = 3 Else If aWS.Range("M49").Value = True Then aWS.Range("M80").Value = 2 Else If aWS.Range("M48").Value = True Then aWS.Range("M80").Value = 1 End If End If End If End If End If Hi, Well it&...

IF than statement in a range of numbers? please help?
I am trying to creat an IF statement that will be true if the number falls within a range of numbers. I want it to say IF A2 is less than 200 but greater than 100 then this cell will display 6". I only know how to write an IF statement for a less than or equal to statment but I dont know how to make it fall in a specific range of numbers. please help thanks. What you need is: =IF(AND(A2>100,A2<200),6,"out of range") You don't specify what you want if the condition is not met, but this produces the text "out of range" - modify as appropriate. Pete =...

Someone else's job, someone else's template
Complete novice at Publisher, must use previous employee's template & text for new job. Text in Word document. Am I merging, cutting/pasting to text box, importing, or something else? Please help; desperate. Create a text box, right-click, click change text, click text file, browse to your Word document. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Ali" <Ali@discussions.microsoft.com> wrote in message news:F00ADD8A-0715-43C8-8975-007D40E34D72@microsoft.com... > Complete novice at Publisher, must u...

AR statements
Why do 'ALL' transactions print on all customers' statements month after month even though most of them have been paid off months ago. HOw can I get it to only show current period trans. like any credit card statement? Somewhere there is a box that you check to say to print only statements with activity for that month but for the life of me I can't find that box right now. -- Elizabeth M. "NcS" <NcS@discussions.microsoft.com> wrote in message news:456F5D65-E06D-44F6-9E38-FADA69FF307B@microsoft.com... > Why do 'ALL' transactions print on all cu...

Conditional statement with mail merge
Hi, I'd like to do a mail merge (in Pub 02) where I test the contents of the merged field and based on the results print either the contents of the database or some other preset text. I know this is possible in Word, is it possible in Pub 02? Thanx for your help. Gary Noooo, most definitely not available in Publisher. -- "If you don't know where you are going, any road will take you there!" Nuts! But thanx for the answer, now I can stop looking for it in the help files >-----Original Message----- >Noooo, most definitely not available in Publisher. > &...

Need loop structure to get round limited IF statements
I'm completely stuck. I have a spreadsheet that has a worksheet fo each month of the year plus an extra one called TOTALS. In each month there is one row per employee (there are about 20-3 employees) and the columns represent the days of the month. Its spreadsheet to calculate holiday totals. The idea is: the holdays get entered with a '1' and in the TOTALS lis there's a drop down of the names of the employees. The select thei name and the total appear for each month. I've tried nested IF's bu I'M limited to 7. I don'T have much programming knowlege and I c...

if then statements?
I am trying to set up a worksheet such that: Excel combines data from cell 'A' and cell 'B'. If the sum is positive then put result in cell 'C'. If sum is negative then put result in cell 'D'. Thanks if you can help. Scott Hi Scott in C1 put the following formula =IF((A1+B1)>0,A1+B1,"") in D1: =IF((A1+B1)<0,A1+B1,"") copy these formulars for all required rows. Some comments - If the sum is '0' nothing will be inserted in C or B Frank Scott Grippo wrote: > I am trying to set up a worksheet such that: > > Excel c...

if then else statement
On my report I have a field that I need help with. I have written an =If statement to try and capture all of the variables, but it is too long, so now I need to write the same expression in code. Here is what I have in my =If statement (which resides in the Detail area of the Report): =IIf([costcode]="013210" And [costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" And [costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" And [costtype]="05320",[txtActualCost]/[txtC...

Changing Exchange HELO Statement
HELO all :-) Is there any way to change the hostname an exchange 2000 server quotes in it's HELO string when sending mail? The server in question is connected to the net with a static IP, but the connections hostname doesn't match what is returned by reverse DNS, and some sites deny connections on that basis. Any help appreciated, Carl. ...

Downloading bank statements and date format error???
Just installed Money2005 and when I try to download my financial statement it gives me an error that the date format of the downloaded file is incorrect. It worked perfectly in M2004 and never gave me this error? I changed the date format through the control panel and downloaded using other format on website instead of money format and it works but is a hassle. Also I am Canadian and had to change to US English settings on my computer to get the correct date format which is annoying. Anyone have any ideas of a better fix???? Thanks In microsoft.public.money, Wags wrote: >Just instal...

Else If
Is there a function which will check in a specified cell for a number (should be from 1-10) and return a specific value based what number it found in that cell? Ex. IF(A1=1,"text1") IF(A1=2,"text2") IF(A1=3,"text3") IF(A1=4,"text4") IF(A1=5,"text5") IF(A1=6,"text6") IF(A1=7,"text7") IF(A1=8,"text8") etc... -- mallets123 ------------------------------------------------------------------------ mallets123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25090 View this thread: http://...

IIF statement question
How do I write an IIF statement if I want to exclude rows where there are dates. There are 3 date fields. If one of the date fields is Not Blank then do not print report. If all fields are Null then print report. Would a simple "IsNull" in each field in my query suffice? Seems to me it would take less time to test "Would a simple "IsNull" in each field in my query suffice?" than to post a question. If you can't test this on your own, reply back. -- Duane Hookom Microsoft Access MVP "TotallyConfused" wrote: > How do I write an IIF st...

Statements need option to exclude E-mail Customer from Printing
There needs to be a new option in Tools | Routines | Sales | Statements > Select "E-mail Options" that allows us to exclude E-mail Customers from Printing while still Sending them via E-mail and printing all other Statements. We are using Dynamics GP 9.0. One of our goals in sending e-mail statements is to NOT spend effort and postage to print, fold, stuff, and mail the statements that can be sent from E-mail. The current Statements "E-mail Options" do not allow us to exclude printing E-mail Customer Statements while still printing Non-E-mail Customer Statement...

If Statement Help
I'm trying to get this to work, and I'm wondering if an If statement is the most efficent way. Anyway. In one row, I have various numbers corresponding to weeks, week #1, week #2, week #3, etc. In a row right below that, I have a number corresponding to shipment quantity. City City 2 City 3 More Cities... Arrival Week 1 3 1 Shipment Quantity 500 400 300 I'd like to see how many total arrivals I'll have in a certain week. Week 1 should return 800 (500+300). Week 2 should return 400. Any h...

Adding "If Statement" with DSum
I am currently working with a database that needs a small adjustment to the following code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") This code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL ...

something else
hi can we please talk about some news headlines here please thanks Hi, Sabian. > can we please talk about some news headlines here please Of course not. This newsgroup is dedicated to Microsoft Access database discussions and always has been. If you don't want to discuss this topic, you are welcome to subscribe to different newsgroups. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/e...

If statement linking data from one sheet to another
I have a spreadsheet with many tabs. I am trying to develop a summery sheet that will ‘auto fill’ certain rows of data and still remain dynamic. What I am doing: in Column I, I have a number; I want this to be the row number for finding the data in the other worksheet. I have If Statements in Columns A thru E for filling the data if Column I has a number in it. If Column I is empty then nothing appears. Formula is: =IF(I53 <> "","=+Agency Reallocation data!D&I53","") But, when I use this formula I get =+Agency Reallocation data...

IF statements #15
What would I do if I wanted to do a statement that said the following: If Column A>0 and B>0 then =1 else = 0 Thanks in advance -- cb3291u ------------------------------------------------------------------------ cb3291u's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7822 View this thread: http://www.excelforum.com/showthread.php?threadid=393303 Try this =if(and(a1>0,b1>0),1,0) Colin -- Colin Vicary ------------------------------------------------------------------------ Colin Vicary's Profile: http://www.excelforum.com/member.php?act...

IF Then Else
Hi, Can someone point out to me what I have wrong with this IF statement. No matter what I change or where I place commas I still get a Syntax Error. I have a Form with one text box for an input number and a command button to run my query. I want to put in this IF statement in the query. It is suppose to do this. When a number is put into the text box the query checks for match in the dwFirstNumber field. If there is not any then to look in the dwSecondNumber field and then the dwThirdNumber for matches. This is what I entered on the criteria line of my query: If(([Forms]![tbl...

Customer Statement
How may I customize the customer statement? Good luck. Statements in MSM:SB are seriously broken At the core, Invoices are seriously flawed.. I'm not sure what MS thinks an invoice is and what information related to one is used for, but IT isn't what I NEED from a SMALL BUSINESS package.. I'm realizing that MSM:SB is a joke and statements in particular are a cruel joke. "xsintrick" wrote: > How may I customize the customer statement? > > ...

If,Then, Else
Could someone please help me with the correct syntax for the "IF, Then: Else: " command -- Alan S "Alansa59" <Alansa59@discussions.microsoft.com> wrote in message news:979ED94B-6E2C-4D89-9417-07C02D84CCE8@microsoft.com... > Could someone please help me with the correct syntax for the "IF, Then: > Else: > " command > -- > Alan S Sure. Let's say you want to do something if a file doesn't exist. It could be coded like this If Dir(MyFileName) = "" Then 'This part executes when the above condition ...

Iif statement that sees the formmatted date value on query table
I have a query date long date field and an expression the formats the long date to weekdays. I can't seem to create a report Iif statement that can see the weekdays, no problem writing a statement that finds the long date. -- kuslusr1 firefly40 wrote: >I have a query date long date field and an expression the formats the long >date to weekdays. I can't seem to create a report Iif statement that can see >the weekdays, no problem writing a statement that finds the long date. You have some kind of misperception. The format of a value is irrelevant when using the value...