if, and statementI 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 StatementHere'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) statementHi
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 questionIs 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 clauseCan 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 questionHello. 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" statementGreetings,
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 statementI 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 statementI 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 statementI 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 conditionA 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 workingI 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 ProperlyTake 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...
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 betweenhello
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%"...
IIfI 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 StatementHello,
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 questionThis 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...
SQL Statement Insert IntoI need to append data from one table1 to table2 using the field in table3 as
the criteria. This field in question is 'account number'. How can I write a
sql statement to accomplish this?
Troy,
What binds the tables? In other words, how are they related to each other?
Unless the account number is the same for every entity in Table2?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
"Troy" <Troy@discussions.mic...