Days remaining?

[blanchard]

Hello all,
I have creadted a .mdb using Access 2003 on a PC Windows XP.

When I look at my Query that I've set up using the Wizard, I've selected it to show the following:

Name
Email Address
Date of Birth

Now what I want todo is to calculate how long until the next birthday.

I did have a go at setting up the following:
Expr1: DateDiff("m",#02/10/1974#,Now())

but that returned the total of months from the year 1974 to present year.

I want to calculate how many months until the next birthday.

Any help would be appriciated, thanks.
Regards Blanchard

 

[Guus2005]

Expr1: DateDiff("m",#02/10/1974#,Date())-DateDiff("y",#02/10/1974#,Date())*12

This should do the trick. The number of years multiplied by 12 subtracted from the number of months.

Now() gives you date and time, you only need date()

Enjoy!

 

[gemma-the-husky]

right

this formula will do it, where mybday is the birthday

DateAdd("yyyy", DateDiff("yyyy", mybday, Date), mybday)) - Date

it works out elapsed years since your birthday (ie your age) adds this to your birthday to give your next birthday, then subtracts the date ot determine how many more days,

 

[DevastatioN]

Expr1: DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#1974-02-10#,Date())+1),#02/10/1974#))

The middle part finds the next birthday by taking Todays Date minus Birthdate in years... then adds that many years + 1 to the year because this will be the next birthday. Then it compares the next birthday to todays Date to determine the amount of months.

 

[blanchard]

So far the nearest results to of those which i want to achieve are from DevestatioN,
Expr1: DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#1974-02-10#,Date())+1),#02/10/1974#))

but this returns an Expr1 month value of 19

 

[DevastatioN]

When I run that exact line I get 11 Months.

I believe the problem is in symantecs, as you see I accidentally inputted the date two different ways... on my machine this works, however because of your regional settings this may be causing a confusion. I believe it's reading the first date as February, and the second date as October. (which is 8 months difference... and the difference between your 19 and my 11 is 8.)

Try and make both date formats identical, and I believe it'll work.

 

[blanchard]

I have changed the following date to the same as the latter and it still returns a month count of 19

Expr1: DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#02-10-1974#,Date())+1),#02/10/1974#))

Would it be better for me to upload the .mdb file or take snapshots?

 

[DevastatioN]

Ok, I found out it was an issue between our regional settings, when I changed 02-10 to 10-02, I also get 19 months.

It happens because of my code apparently doesn't properly check if the birthday has passed this year or not.

I guess my temperary fix would be to use an IIF statement to see if it's above 12, then subtract 12 from it, else leave it as it is.

IIF((DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#02-10-1974#,Date())+1),#02/10/1974#))) > 12, (DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#02-10-1974#,Date())+1),#02/10/1974#))) - 12, DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#02-10-1974#,Date())+1),#02/10/1974#)))

But that is just getting long and annoying, there must be a simpler way. In Excel you can use the EDate function like this to determine the next birthday for sure, and then do it correct:

=EDATE(A2,12*(DATEDIF(A2,B2,"y")+1))

But Access doesn't have the EDate function. Sorry I can't be of any more use than this, if I can think of a simpler logic way to make it work without the IIF then I will let you know.

 

[DevastatioN]

Actually, instead of that jumble just use two fields inside your query.

The first field has that Exp1 that returns the 19,

Then field 2 is:

Exp2: IIF([Exp1] > 12, [Exp1] - 12, [Exp1])

This should be much shorter.

 

[blanchard]

you have been a great help DevastatioN, thanks very much for you time and the same goes foor the previous posters.

Thanks

 

[blanchard]

ok this works well and is correct:

IIF((DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#02-10-1974#,Date())+1),#02/10/1974#))) > 12, (DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#02-10-1974#,Date())+1),#02/10/1974#))) - 12, DateDiff("m",Date(),DateAdd("yyyy",( DateDiff("yyyy",#02-10-1974#,Date())+1),#02/10/1974#)))

but i find it returns the same results for both Dates of Birth's in my list even though on Date of Bith is shown to have a different month of birth.

See attached

 

[raskew]

Hi -

There are several issues to consider. Personally think
this would be much easier resolved using a function.

1) Creating the next anniversary date, considering that this year's
anniversary may have already passed.

2) Counting the months between today and the next anniversary,
taking into account that the DateDiff() function merely subtracts
one month from another without consideration if there's truly
a month's difference. For example, today is 24 Mar 09.
datediff("m", date(), #4/1/09#) will return 1, when in fact a full
month won't pass until 4/24/09.

Function NumMonths(DOB As Date, Optional StartDte As Variant) As Integer
'Purpose:   Return the number of full months
'           between a designated start date (default = Date()) and an
'           upcoming anniversary of an input date
'Coded by:  raskew
'Inputs:    From debug (immediate) window:
'           Note: Today's date = 24-Mar-2009
'           (1) ? NumMonths(#2/26/55#)
'           (2) ? NumMonths(#2/23/55#)
'           (3) ? nummonths(#4/27/55#)
'           (4) ? nummonths(#4/27/55#, "3/30/09")
'
'Output:    (1) 11
'           (2) 10
'           (3) 1
'           (4) 0

Dim dteHold As Date

StartDte = IIf(IsMissing(StartDte), Date, StartDte)

   dteHold = DateSerial(Year(Date), Month(DOB), Day(DOB))
   dteHold = DateAdd("yyyy", IIf(dteHold < StartDte, 1, 0), dteHold)
   NumMonths = DateDiff("m", StartDte, dteHold) + (Day(StartDte) > Day(dteHold))

End Function

HTH - Bob

 

[blanchard]

hello raskew,
thanks for the code but where do I paste it and will i need to edit any of it?

Sorry for asking all these questions but I'm a newbie on Access.

 

[raskew]

Hi -

Just copy/paste it to a standard module. There should be no editting necessary.

You can call it as shown in the example inputs or use it in a query. Here's a sample query against Northwind's Employees table:

SELECT
    Employees.LastName
  , Employees.FirstName
  , Employees.BirthDate AS DOB
  , [COLOR="red"]NumMonths([BirthDate]) AS MonthsTill[/COLOR]
FROM
   Employees;

HTH - Bob

 

[blanchard]

are there any easy to do tutorials on this with pictures...lol...as i'm really struggling

 

[raskew]

Hi -

Not sure where you're having problems but here's kind of a step-by-step:

1) From the database window, click on Modules.

2) Click on New to create a new standard module.

3) Highlight and copy (Ctrl-C) the function I provided.

4) Return to the new standard module and paste (Ctrl-V) the copied function.

5) Save and close the new module. You'll be offered a default name, e.g. Module1 -- go ahead and accept that.

6) To test, go to the Immediate window (Ctrl-G) and type:

? NumMonths(#6/30/55#) <enter>

It should return 3 -- the number of full months between today (25-Mar-2009) and 30-Jun-2009 (the upcoming anniversary date).

Hope that'll get you started.

Bob

 

[blanchard]

ok thanks i'll give it a go

 

[blanchard]

Hello there,
the following code works on the one date ie. 02/10/1974

Expr1: IIf((DateDiff("w",Date(),DateAdd("yyyy",(DateDiff("yyyy",#02/10/1974#,Date())+1),#02/10/1974#)))>52,(DateDiff("w",Date(),DateAdd("yyyy",(DateDiff("yyyy",#02/10/1974#,Date())+1),#02/10/1974#)))-52,DateDiff("w",Date(),DateAdd("yyyy",(DateDiff("yyyy",#02/10/1974#,Date())+1),#02/10/1974#)))

i need it to work on a variable date rather than just a set date, how do i change the date to some sort of variable within this code please?

Regards blanchard

 

[DevastatioN]

Change #02/10/1974# to [Enter a Date] and it will prompt the user to enter a date for the query.

For example:

Expr1: IIf((DateDiff("w",Date(),DateAdd("yyyy",(DateDiff( "yyyy",[Enter a Date],Date())+1),[Enter a Date])))>52,(DateDiff("w",Date(),DateAdd("yyyy",(D ateDiff("yyyy",[Enter a Date],Date())+1),[Enter a Date])))-52,DateDiff("w",Date(),DateAdd("yyyy",(DateDiff("y yyy",[Enter a Date],Date())+1),[Enter a Date])))

If the date you wish to run the function on exists already in your query, use [FIELDNAME] instead of [Enter a Date].

 

[blanchard]

excellent thanks