IIf function, & Date(), & AND, & > & <

I'm trying to get the query to check if an anniversary date is within the 
next 30 days, so I tried using this formula, but it did not work.
Your help is greatly appreciated.
Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")


0
Utf
1/7/2010 5:10:26 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
5193 Views

Similar Articles

[PageSpeed] 55

On Thu, 7 Jan 2010 09:10:26 -0800, Hurrikane4 wrote:

> I'm trying to get the query to check if an anniversary date is within the 
> next 30 days, so I tried using this formula, but it did not work.
> Your help is greatly appreciated.
> Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")


Using your Greater Than and Less Than approach:
Expr1:IIf(([Anniv] <=Date()  + 30) And ([Anniv]>= Date()),"Yes","No")

Perhaps this approach might be simpler:
Expr1:IIf([Anniv] Between Date() and Date() + 30,"Yes","No")


-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
1/7/2010 5:42:14 PM
Don't add numbers to date fields.  It can be problematic.  Use DateAdd() 
instead.  It allows you to add days, months, whatever you want.

In your case:
IIf([Anniv]>Date() AND [Anniv]<DateAdd("d",30,date()), "Yes", "No")

OR, IIf([Anniv]>Date() AND [Anniv]<Dateadd("m",1,date()), "Yes", 
"No")which would check for dates within a month, whether 30 days, 31 or 28.

Phil




On 1/7/2010 9:10 AM, Hurrikane4 wrote:
> I'm trying to get the query to check if an anniversary date is within the
> next 30 days, so I tried using this formula, but it did not work.
> Your help is greatly appreciated.
> Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")
>
>

0
Phil
1/7/2010 5:44:48 PM
date() include today's year, while anniv includes real birth date? if so:

Format( anniv, "mmdd" )  BETWEEN  Format( now, "mmdd" )  AND  Format( now + 
30, "mmdd")

or

DateSerial( Year(now), Month(anniv), Day(anniv) )  BETWEEN date( ) AND 
date( )+30



Vanderghast, Access MVP


"Hurrikane4" <Hurrikane4@discussions.microsoft.com> wrote in message 
news:0BFC6A7D-E403-4A4C-BE50-604477A0A7E1@microsoft.com...
> I'm trying to get the query to check if an anniversary date is within the
> next 30 days, so I tried using this formula, but it did not work.
> Your help is greatly appreciated.
> Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")
>
> 

0
vanderghast
1/7/2010 6:13:04 PM
That did work, thank you very much!

"vanderghast" wrote:

> date() include today's year, while anniv includes real birth date? if so:
> 
> Format( anniv, "mmdd" )  BETWEEN  Format( now, "mmdd" )  AND  Format( now + 
> 30, "mmdd")
> 
> or
> 
> DateSerial( Year(now), Month(anniv), Day(anniv) )  BETWEEN date( ) AND 
> date( )+30
> 
> 
> 
> Vanderghast, Access MVP
> 
> 
> "Hurrikane4" <Hurrikane4@discussions.microsoft.com> wrote in message 
> news:0BFC6A7D-E403-4A4C-BE50-604477A0A7E1@microsoft.com...
> > I'm trying to get the query to check if an anniversary date is within the
> > next 30 days, so I tried using this formula, but it did not work.
> > Your help is greatly appreciated.
> > Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")
> >
> > 
> 
0
Utf
1/7/2010 6:41:03 PM
That also worked, thank you for your help.

"fredg" wrote:

> On Thu, 7 Jan 2010 09:10:26 -0800, Hurrikane4 wrote:
> 
> > I'm trying to get the query to check if an anniversary date is within the 
> > next 30 days, so I tried using this formula, but it did not work.
> > Your help is greatly appreciated.
> > Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")
> 
> 
> Using your Greater Than and Less Than approach:
> Expr1:IIf(([Anniv] <=Date()  + 30) And ([Anniv]>= Date()),"Yes","No")
> 
> Perhaps this approach might be simpler:
> Expr1:IIf([Anniv] Between Date() and Date() + 30,"Yes","No")
> 
> 
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
> .
> 
0
Utf
1/7/2010 6:42:01 PM
Thank you for your help.

"Phil Smith" wrote:

> Don't add numbers to date fields.  It can be problematic.  Use DateAdd() 
> instead.  It allows you to add days, months, whatever you want.
> 
> In your case:
> IIf([Anniv]>Date() AND [Anniv]<DateAdd("d",30,date()), "Yes", "No")
> 
> OR, IIf([Anniv]>Date() AND [Anniv]<Dateadd("m",1,date()), "Yes", 
> "No")which would check for dates within a month, whether 30 days, 31 or 28.
> 
> Phil
> 
> 
> 
> 
> On 1/7/2010 9:10 AM, Hurrikane4 wrote:
> > I'm trying to get the query to check if an anniversary date is within the
> > next 30 days, so I tried using this formula, but it did not work.
> > Your help is greatly appreciated.
> > Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")
> >
> >
> 
> .
> 
0
Utf
1/7/2010 6:43:01 PM
Reply:

Similar Artilces:

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...

Count if function
Hi Bhalchandra here, I have Excel 2003. A B C D E F G Act.Desc STATUS Dec-09 Jan-10 Feb-10 Mar-10 Apr-10 Act 1 Plan 0.6 0.9 0.9 3 Act 1 Actual 0.6 0.6 0.6 Act 2 Plan 0.3 0.45 0.45 1.5 Act 2 Actual 0.6 0.6 0.6 Act 3 Plan 0.7 1.05 1.05 3.5 Act 3 Actual Act 4 Plan 0.3 0.45 0.45 1.5 Act 4 Actual Act 5 Plan 0.75 1.125 1.125 3.75 Act 5 Actual Sum Plan 2.65* 3.975 3.975 13.25 Actual 0 1.2 1.2 1.2 Count Plan Actual Average Plan Actual =sumif($b$3:$b$14,"Plan",C$3:C$14) =sumif($b$3:$b$14,"Acual",C$3:C$14) ...

Case sensitivity of functions in Excel
Hi, Which Excel functions are case sensitive and which are not? Can anyone shed some light or point to some resource that explains the case sensitivity of functions in Excel? Thanks in advance. Regards, Raj Most functions are not case sensitive, but you can use the EXACT function if case is important to you. FIND is case-sensitive, but SEARCH is not. Hope this helps. Pete On Jun 2, 1:37=A0am, Raj <rsp...@gmail.com> wrote: > Hi, > > Which Excel functions are case sensitive and which are not? > > Can anyone shed some light or point to so...

Modified Date
I would like to record (just the date) in a cell when some data in another cell is changed by a third party. I know you can do this through track changes but I would like it to be visible to everyone. Don't know exactly what you want but right click sheet tab>view code>copy\paste this. Now, if you input anything into cell a1, cell a2 will show the date Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Range("a2") = Date End Sub -- Don Guillett SalesAid Software donaldb@281.com "John L" <anonymous@discussions.mic...

Multiple Unions and functions
I have a function that has 4 unions. The queries are similar except for the where clauses It looks something like: SELECT... FROM... JOIN... WHERE @Arg1 = 0 and @Arg2 = 0 and e.EID IS NULL UNION SELECT... FROM... JOIN... WHERE @Arg1 = 0 and @Arg2 = 1 UNION SELECT... FROM... JOIN... WHERE @Arg1 = 1 and @Arg2 = 0 and e.EID IS NULL UNION SELECT... FROM... JOIN... WHERE @Arg1 =1 and @Arg2 = 1 @Arg1 and @Arg2 are passed in and are 0 or 1. Is this the best way to handle the possible combinations or would it be better to do an if test and only do the one that matche...

TRIM function #2
Is it possible to trim an entire worksheet? You could use a macro Sub trimall() Application.ScreenUpdating = False Dim myRange As Range Set myRange = Range("A1:D1000") '< Change to suit For Each c In myRange c.Select c.Value = Trim(c.Value) Next c Application.ScreenUpdating = True End Sub Mike "fitou_learn" wrote: > Is it possible to trim an entire worksheet? Many thanks Mike. It worked a treat! "Mike H" wrote: > You could use a macro > > Sub trimall() > Application.ScreenUpdating = Fa...

countif function help
I'm trying to perform a countif function and I was hoping some of th more excel inclined individuals on this forum could help with this. What I'm trying to accomplish below is to reference the dates (lef most column) and then count how many times the same date occurs. Fo example, 6/7/2004 has two records. Then I want to take that number an look at whether it was a confirm or statement (3rd column), then I wan to multiply by the amount, but I will have one new column searching fo just confirms and another new column just for statements. For example on 6/7/2004 there was a total of 2...

Loss of Functionality #2
One of our clients recently upgraded from sp2 to sp3 for Outlook 2002. After doing so, they noticed the following problem... When inserting an attachment in an email, with Rich Text set and without Word as the email editor, the client often needs to rename the attachments in the body of the email by right-clicking on it, selecting properties, and changing the Label name. This would then change the name of the attachment. This worked great with Outlook 2002 sp2 but the attachment name fails to change after upgrading to sp3. The function is still there but when you type in a new name and...

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 ? ...

Index Match Functions #3
I am building a spreadsheet query tool, the data is exported via flat file and I convert it to .xls. There are mulitple files in the work book and I need to be able to query the data from multiple spreadsheets. Would index / match be the best functions? Is there some good examples of how to set them up? I am fine with lookup functions but this is too complex for that and I am not familiar with the index and match functions. Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/...

Count Consecutive Dates
I have a database that allocates people to jobs, which is based ontheResource Scheduling database.I would like to show on a Form the current amount of shifts anemployee has worked, by counting consecutive dates from a given date(ScheduleDatetxt on the form).Relevant tables used in the formEmployeesEmploy_Ref (text)Surname....ScheduleDetails (Where jobs allocation is saved)ScheduleDetailsIDScheduleIDEmploy_Ref (text)Schedule (job date is saved)ScheduleIDScheduleDateIs there anyway I can do this using VB or SQL? Any help would begreatlyappreciated....

base class virtual destructor not resolving inherited virtual functions
I have a heavy fog over my brain, and can't see the obvious answer... I have a base class "CBase" and an inherited class "CChild". I instantiate an object of "CChild", work with it, and then delete it. It has an empty destructor, which does nothing, and then calls the CBase destructor, which calls a virtual function which is overridden in CChild -- BUT, it doesn't call the overriden function (it is simply calling the base class function). What am I not seeing here? Thanks DanB ----- Code snippets ----- class CBase { CBase() { } ...

Convert Date Range to Work Week Hours
I have a report which generates values based on a date range [StartDate] & [EndDate]. I am not sure that access can do this but what I need to be able to do is have Access generate the number of days or hours are available excluding weekends based on the date range. For example: StartDate = 1/1/2008 EndDate = 1/17/2008 Total Days = 17 Business Days = 13 (4 weekend days during this range) Any help on calculating this is greatly appreciated. Thanks Dwayne, Create a loop, starting with your StartDate, and adding 1 day on each iteration. (StartDate +1, +2, etc..) During each ...

Any suggestions about the "countif" function?
The COUNTIF function uses a number for setting the condition, for example =COUNTIF(A1:A100,"<10"). How is it possible to replace the condition (in this case "<10") with a cell address? Thank you very much. Gene If the condition is in cell D1, then try this: =COUNTIF(A1:A100,"<"&D1) Hope this helps. Pete On Sep 21, 4:16 pm, "EpsilonRho" <Epsilon...@nospam.ooo> wrote: > The COUNTIF function uses a number for setting the condition, for example > =COUNTIF(A1:A100,"<10"). How is it possible to replace the conditi...

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...

Code for previous amount for customer in specific date
Hello The code below to display the previous amount for customer in specific date Mybe useful for someone SELECT SUM(ORTRXAMT) AS EXPR1 FROM (SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, DOCDATE, ORTRXAMT = CASE RMDTYPAL WHEN 2 THEN ORTRXAMT * - 1 WHEN 7 THEN ORTRXAMT * - 1 WHEN 8 THEN ORTRXAMT * - 1 WHEN 9 THEN ORTRXAMT * - 1 WHEN 1 THEN ORTRXAMT WHEN 3 THEN ORTRXAMT WHEN 4 THEN ORTRXAMT WHEN 5 THEN ORTRXAMT WHEN 6 THEN ORTRXAMT END, DEX_ROW_I...

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...

Excel
Hello, I'm trying to separate digits from a large number, a date actually, into adjacent cells. For example, in cell A1, I have the eight-digit figure "11252009" (MM/DD/YEAR, or Nov 25, 2009). I would like to separate the 11252009 into separate cells. If cell A1 is 11252009, then I want: B1 to have 11 (month) C1 to have 25 (date) D1 to have 2009 (year) Note: I also have seven-digit dates as well (3152009 = 3/15/2009). Please help. Cheers, Ketsu EggHeadCafe - Software Developer Portal of Choice Separate ASP Code From HTML Content http://www.eggheadcafe.com/tutorials/aspn...

Excel "IF" functions
Hello, I am trying to create an "IF" formula to indicate an "X" in a cell if the following criteria is met: 10% of entity's Total Assets(C11) and .05% total variance (between two periods) and any variances over $500K Thanks! An example would have helped a lot. What do we compare 10% of total assets to? How do you calculate variance? Do you meant .05% variance, or 5% variance? When you say 500K, do you mean 500000? When you say "and" do you mean *all* criteria must be met, or any one of them? Regards, Fred "Katrina" <...

If then functionality in the approval hierarchy of BP RM.
I am currently using Business Portal RM v2.7. I would like the approval hierarchy to include if then functionality for dollar limits. For example, if Tom has a $1,000 limit he can send everything under $1,000 to the Purchasing person, if it's between $1,000 and $5,000 it goes to his direct manager Sue. If it's over $5,000, Sue has to approve it and send it to her boss who will then send it to Purchasing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agr...

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 ...

Problem with Lookup function
I am settign up soem large tables and am using the Lookup function to read to values in the table. I am gettign some incorrect values. For example, when the lookup value is MA10 I am getting the table value listed for MA1. How can I fix this? thank you kindly! Have you checked out what Excel Help says on the Lookup functions? Specifically about what occurs when no exact match is found? Is the list sorted? Have you used the FALSE argument? Is MA10 value in the list? What do MA1 and MA10 represent? Are they text entries? Post your formula. Gord Dibben MS Excel MVP On Tue, 30 ...

"improper procedure call" when average log function results
Get an "improper procedure call" error message when trying to average results of the log function in Access. Need to do this to calculate the Upper confidence Interval for grouped data. Never had this problem in Access 98 or 2003. Hi - You haven't given a whole lot of information, but you will get this error if you try to use a number less than or equal to zero to the log function. I suggest you check your data. If this is not the problem, please give more detailed information (query? VB? ) about what you are trying to do. John rabald68 wrote: >G...

adding date of an Outlook email
How in Excel 2007 can I add "email sent" date and "email received" date cells linking to local Outlook 2007? Beemer ...