Days remaining?

[raskew]

Re your Post #18

First-off, that ungainly Iif() statement isn't doing what you may think:

1) It refers to "w", which represents Weekdays (same as "d" for Days) when it's plain it's after Weeks (e.g. >52). For that, you need to use "ww"

2) Doesn't take into account that the DateDiff() function merely subtracts one month from another without consideration if there's truly a month's difference. Works the same on Years and Weeks. For example, today is 25 Mar 09. Datediff("m", date(), #4/1/09#) will return 1, when in fact a full month won't pass until 4/25/09.

I've cleaned-up the code, replacing "w" with "ww". Problem being, it's not always going to return the correct answer (see 2), above). You can feed a variable to it representing a date, rather than always hard-coding the date.

Here it is. Copy the below to the immediate window, then press <enter> after each line.

x = #4/1/55#

? Iif(DateDiff("ww",Date(),DateAdd("yyyy",(DateDiff("yyyy",x,Date())+1),x))>52, DateDiff("ww",Date(),DateAdd("yyyy",(DateDiff("yyyy",x,Date())+1),x))-52, DateDiff("ww",Date(),DateAdd("yyyy",(DateDiff("yyyy",x,Date())+1),x)))

It'll return 1, which is incorrect.

Gotta ask -- What's wrong with the code provided in Post #12?

Seems to me that it's a little easier to type:
? NumMonths(#4/1/55#) <enter>

...than contend with that monster Iif() statement.

Just my opinion. -- Bob

 

[blanchard]

I don't quite understand the Module part, infact there's snippets i do understand and have fathomed out but simple stuff like exporting a file and emailing and run apps, but when it comes to meddling with the code to add other variables then I don't really know where to begin.

 

[raskew]

There's no meddling involved. The code, provided you've got it stored in a module, remains static. You need only to call it, providing a date or date field to work from.

I've previously provided examples of calling the function, both from the Immediate window and from a query. Let me use a stripped-down version of the function to explain what happens on each line of the code. Note that the Operators used (Iif(), DateSerial(), DateDiff(), DateAdd()) are all (with the exception of DateSerial())
used in your monster Iif() statement.

Function NumMonths(DOB As Date, Optional StartDte As Variant) As Integer
[COLOR="RoyalBlue"]'Call the function, providing a event date (e.g.DOB) and optionally
'StartDte (otherwise the current date becomes the default StartDte)[/COLOR]

Dim dteHold As Date

[COLOR="royalblue"]'This line determines if you've provided a StartDte, if not
'StartDte is set to the current date[/COLOR]
   StartDte = IIf(IsMissing(StartDte), Date, StartDte)

[COLOR="royalblue"]'This line populates a datetime variable with the month and day of the
'event and the current year[/COLOR]
   dteHold = DateSerial(Year(Date), Month(DOB), Day(DOB))

[COLOR="royalblue"]'This line determines if the date just created is < the current date,
'has already occured. If so, it adds one year to establish the next
'scheduled occurence.[/COLOR]
   dteHold = DateAdd("yyyy", IIf(dteHold < StartDte, 1, 0), dteHold)
   
[COLOR="royalblue"]'This line determines the number of full months between the StartDte and the
'next event date.  [COLOR="Red"]+ (Day(StartDte) > Day(dteHold)[/COLOR] is a boolean statement
'that equates to -1 if True, 0 if False and this is what determines full months.[/COLOR]
   NumMonths = DateDiff("m", StartDte, dteHold) [COLOR="Red"]+ (Day(StartDte) > Day(dteHold))[/COLOR]

End Function

HTH - Bob