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
6277 Views

Similar Articles

[PageSpeed] 47

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:

XslTransform.Transform fails when xsl contains node-set() Function
My transform in cludes a variable that holds a node set from the node-set() extension function. It the transformation works fine when using MSXML2 or msxsl.exe but not in VB.Net. My code looks something like this: Dim XP as New XPathDocument(XMLFileName) Dim XSLT as New XslTransform XSLT.Load(XSLTFileName) Dim Out as New MemoryStream XSLT.Transform(XP.CreateNavigaotr, Nothing, Out, Nothing) <-- Fails on this line Out.Position = 0 Dim XML as New XmlDocument XML.LoadXml(New StreamReader(Out).ReadToEnd) The exception says: System.Xml.Xsl.XsltException: Function 'msxsl:node-set()' ...

change the year in a list of dates ex 1/3/2003 to 1/3/2004
I have a list of dates, all in the year 2003 1/3/2003, and 5/17/2003, I want to change just the year to 1/3/2004 and 5/15/2004 in the whole list. How do I do that? say the dates you have now are in col A in a new column, type =a1+365 and copy down then do a /copy/special/values on the new column so it becomes static and no longer refers to col A... then delete col A and move teh new column to Col a work on a copy of your file to make sure I have it right... "Elaine" <Elaine@discussions.microsoft.com> wrote in message news:57252856-7A95-4C46-8B96-10E4D127EAB9@microsof...

Auto increment date in header
Hello, I want to create a document and in header to put the date like: Thursday, 31.12.2009 and on second page, in header to appear Friday, 01.01.2010 and on third page Saturday, 02.01.2010 and so on Have anybody an idea how to do it? Thank you! Hi Lucrix, To see how to do this and just about everything else you might want to do with dates in Word, check out my Word Date Calculation Tutorial, at: http://lounge.windowssecrets.com/index.php?showtopic=249902 or http://www.gmayor.com/downloads.htm#Third_party In particular, look at the item titled 'Calculate a Date Se...

SendKeys function
Hi all, I am trying to "SENDKEYS" to another appliction I am using. Basically I take Cell A6 and copy the contents into another app. I the want to select another Cell D6 and take the contents of that and cop that. Easy I hear you say......When I run it with just a6 it works fine. Whe I run it with D6 it works fine but when I put them both together th first cell is missed out and both columns in the other app have th data from D6 in. E.g. A6 = 50 E.g. D6 = 100 Copy one into other App = OK Copy both into other App = only inserts 100 and misses out 50. Sorry, that is the best e...

find function start with oldest message
whenever I run a find on my inbox (outlook 2003) it starts with the oldest message in the box and I find myself waiting for 10 minutes until it has worked through to more recent times. Is there any way to configure/change this behavior? Yep, use the Lookout addin for searches instead. It's a free Outlook add-in from Microsoft http://www.microsoft.com/downloads/details.aspx?FamilyID=09b835ee-16e5-4961-91b8-2200ba31ea37&DisplayLang=en -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed...

Help with IIF ans IsNull!
Basically i have a drop down that i want controlling the Criteria for each field in my query to have an advanced search. If i leave a field blank the query comes up with nothing so i tried this: Code: IIf(IsNull([Forms]![Inventory Report Search]![Model]),Is Null,[Forms]![Inventory Report Search]![Model]) this is not working. how do i make is so if a field is blank it will return it as null or not even there. Note: also tried this; Code: Forms]![Inventory Report Search]![Model] Or Forms]![Inventory Report Search]![Model] Is Null works, But comes back as too complex after a few sea...

"function" vs. "worksheet function"
I've bashed around with Excel before, but now I'm having to dig into the Help (on the web). The terms "function" and "worksheet function" are pervasive. Are they basically synonyms? Thanks. "Function" is more general -- it can refer to a worksheet function, or a VBA-based user defined function (UDF). HTH, JP On Mar 20, 4:30=A0pm, Paul <Paul.Domas...@gmail.com> wrote: > I've bashed around with Excel before, but now I'm having to dig into > the Help (on the web). =A0The terms "function" and "worksheet function" ...

Logical function
Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm trying to do a logical function and I can't figure out where to put what....I want to say if the word &quot;fabric&quot; appears in column a, then column b should be 100% or if accessories appears in column a then column be should be 30%....and so on. I have looked at the IF/Then/Value Help and I can't figure out what to do since I'm using text instead of values. Please help! Mary In article <59bb1527.-1@webcrossing.JaKIaxP2ac0>, bchgnn@officeformac.com wrote: > Operating System: Mac O...

Date problem #7
i am trying to do a matrix for some training we have to keep records o and I got all my columns and names , but I want to use a second shee to log what date this happens. I would like to use OK on sht 1 an enter date on sht 2 Does any one have any Ideas to make this work office 2000 -- Message posted from http://www.ExcelForum.com ...

Pivot Table - Grouped Dates
Hi All, I have a grouped pivot by date. Works fine except for the format of the date doesn't allow sorting in the way that I need. The data has the dates in the format: 4/17/2006 14:21 After grouping in the pivot on that field by day (to remove the time portion) I get: 10-Apr Sorting the pivot gives: 10-Apr 10-Mar 11-Apr 12-Apr 13-Apr 13-Mar 14-Apr 14-Mar 15-Apr 15-Mar 16-Mar 17-Apr Rather than by year/month/day. I tried changing the field format in the pivot and it doesn't do anything at all to how the format in the pivot looks? I need the pivot data to sort as actual day...

Enter date into Formula
I want to have a date without using date function in another function, i.e., MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? -- Thanks, Don On Sat, 16 Jan 2010 19:40:01 -0800, DRA <DRA@discussions.microsoft.com> wrote: >I want to have a date without using date function in another function, i.e., >MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 > >I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? The DATE function ...

Entering a date that then gets changed to next weekday Thursday's date, say?
I don't want to make the current sheet I'm working on too complicated, so prefer not to go the macro route. Was hoping a formula would take care of this (?). If I enter a date via "^;", it would be so nice if the sheet knew to change the date to the following Thursday's date. i.e., when I type ^; into the currently empty date cell, it puts today's date of "2006.12.27.Wed" which I then stop to fix to nearest Thursday. Instead, it would be very helpful if a formula or something non-macro did that for me and changed it to the nearest Thursday's date, in...

Don't we have some sort of "dump" function?
I have a few simple character strings that won't "Trim", leaving me to believe that there are some kind of un-printable trailing characters of some sort. I need some sort of dump function like: HexDump("Bill") would yield the string: C2899393 Any ideas? Thanks, Bill Bill wrote: >I have a few simple character strings that >won't "Trim", leaving me to believe that >there are some kind of un-printable trailing >characters of some sort. I need some sort >of dump function like: > >HexDump("Bill") would yield the string: ...

Filling in a cell based on another cells date criteria
I want to populate a cell with the value of another cell..based on the following date criteria - My date cell is stored in (F5) and the cell I want to populate (D20) has a value stored in (C20) If the first of the month is a Mon-FRI then the cell (D20) gets populated on the date in the week which the first of the month falls on...example: if 8/1/08 were a wed the field (D20) would get populated with the value in cell (C20) UNLESS If the first of the month were a Sat..like 11/1/08... if the first of the month falls on a saturday on a sat...then the field (D20) would get populated with (C20&#...

Workbook Protectionand stiil use Comment function
I have a shared workbook that requires some fields to be used for data entry and some fields for comments ( using the Xl comment function), however, when I protect the workbook the overall comment function is also looked (still allows viewing a previous comment). Is it possible to overcome this scenarion so that one doesn't have to Unprotect the book or sheet everytime a comment needs to be inserted or edited. Many thanks Olympiad In Excel 2003 you can edit comments in protected sheet, if you check the option for Edit Objects in the Protect Sheet dialog. -- tj "olympiad&qu...

IIF IsNull or IsNotNull
I have a report text box with the following source: =[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] & " " & [FacilityName] & " / " & [Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs] & " " & [LineDesc] How can I revise this so that the text box will display ONLY IF [cbProfilesAssocsFacIDs] and/or [cbProfilesAssocsFacIDsLineIDs] has a value? The text box is set to shrink - I just can't get the right code. Thanks for your help!!! -- www.Marzetti.com Thanks, Karl! > Which? ...

Expiry date of the current version ?
I have been using Microsoft money for a since 2000. I am on version 2004/2005. I want to buy the latest version. I am not sure whether to buy now or wait for the next version ? if i buy the current version will i be able to download the transaction automatically ? I am surprised that a reputed company like Microsoft has no mention about this on their website. I would appreciate if you guys advice me on this. The newest version is Money Plus Deluxe and you can go to www.microsoft.com/money and you will find it there. "srn" wrote: > I have been using Microsoft money fo...

Help with time function
I NEED HELP... if cell a1 lets say is 7:00 and I would like to add 15 minutes to this time in cell a2 what would be the formula. Everything I seem to try fails. thanks David Poe djoseph11@tampabay.rr.com One way: A2: =A1 + TIME(0,15,0) another: A2: =A1 + 15/(24*60) or equivalently A2: = A1 + 15/1440 or, equivalently A2: =A1 + 1/96 In article <Ihsbc.397852$jH.5735715@twister.tampabay.rr.com>, "David Joseph" <DJOSEPH11@tampabay.rr.com> wrote: > I NEED HELP... > > if cell a1 lets say is 7:00 and I would like to > add 15 ...

Weibull function in Excel
I have a survival dataset (see below). I want to fit to Weibull function S(t)=EXP(-alpha*Time^gamma). If I use Excel Solver, alpha=0.00367 and gamma=2.32. If I use a method tranforming probability to Ln(Ln(1/S(t)) which will be a linear function of Ln (t). But I got a different alpha and gamma. When I compared two alphas adn gammas, the Solver results had a better goodness of fit. What is the problem? THanks Month Probability of survival 0 1 1 1 2 0.92 3 0.90 4 0.88 5 0.83 6 0.76 7 0.74 8 0.69 9 0.57 10 0.48 11 0.39 12 0.27 13 0.27 14 0.13 Barbo - >...

Add st and th to dates
Hi All, Is there a way in excel to format a date so that it automatically includes the "th" and "st". I.e. so 1/8/05 --> 1st August 2005? Ta Andi Hello- Excel provides no such date formatting, and trying to create a custom format like that would be impossible due to the variable 'st', 'rd', 'th', etc. & when each should be used. Perhaps it can be accomplished with VBA, but unless someone has it available & is willing to share, it would probably be more trouble to write than its worth... Unless you need to use the dates for cal...

Default User Defined Functions
I have some user defined functions that I use quite often. I want to have all my user defined functions accessable by any workbook that I start at any time. I do not want to have to open a module that contains the functions and copy and paste then inside the new workbook. How con I make all my user defined functions show up in the user defined function section by default? Thanks, Eric Save them as *.xla (save as>add-in), then check them under tools>add-ins Regards, Peo sjoblom "flycast" wrote: > I have some user defined functions that I use quite often. I want to ...

Word 2007 Is Non-Functional
HELP! Yesterday, Word 2007 worked fine. Today, after some Microsoft auto updates were applied (one was a security update to Office (KB978380)) it fails every time. If I start Word and try to save or open a file - crash ("Microsoft Word has stopped responding"). My system has a QX-9650 Quad-Core with 8GB of memory and dual video cards in a quad-SLI configuration (2GB VRAM). I have one application open (Word) so system resources are not an issue. I'm running Vista x64. I went to the Technet site and found an article on Word continually crashing but neither ...

Function to find closest to 0 (including neg #'s) in cloumn
I am trying to find a function that will sort out numbers in a column (pos & neg) and tell me wich one is closest to zero. =MIN(IF(F1:F20<>0,ABS(F1:F20)))*SIGN(IF(F1:F20<>0,F1:F20)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Dante" <Dante@discussions.microsoft.com> wrote in message news:5299C14D-39F7-4E0A-BBD3-9A2624425C93@microsoft.com... > I am trying to find a function that will sort out numbers in a column (pos & > neg) and tell me wich one is closest to zero. Bob: How about if zero is in the column?...

HQ Reports should allow multiply coloms by store or date
Reports should allow for a data sheet view. I would like to see a report with all items down the left side of my report. Colom headings for total item instock and a quantity in stock at each location. Would be nice to see this for all kinds of reports, Sales by dollar amount, Sales by item, ect. Basicly a spread sheet report that the detail can be seen side beside. ---------------- 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 Agree" button in the message pane. If you do not...

Flagged info
I use the completed box in the flag along with the description when I'm closing an issue that has been tracked throughout via email. In the past, in other places I've worked, the date and time will also be printed, not just the flag and flag status. I noticed in one other contract that the defaults somehow were set so that date/time didn't appear and in this current contract, same thing. This current job, though, is all about tracking BFs, though, and so I cannot afford not to have the date/time the flag info was completed printed out. Does anyone know how to change it so that ...