I'm having a formulating dilemma that I hope anyone can help with. I need to do some reporting for a list of people so I can know there anniversary dates for charging them. However the way it works isn't just on fixed number of days. its always the next month on the same day of your intial date.
For Example:
If you became a customer on 1/2/07, your annivesary date would be 2/2/07, which is 31 days.
But if you became a customer on 2/2/07, your anniversary date would be 3/2/07, whichh is 30 days.
Since not every month has the same numbers of days, I'm stumped.
How would I construct the formula so I could pull a list each day of who has hit their annivesary date. I can't seem to get a date diff formula that makes it happen.
Can anyone help with this? It would be much appreciated.
12  2876 
I'm having a formulating dilemma that I hope anyone can help with. I need to do some reporting for a list of people so I can know there anniversary dates for charging them. However the way it works isn't just on fixed number of days. its always the next month on the same day of your intial date.
For Example:
If you became a customer on 1/2/07, your annivesary date would be 2/2/07, which is 31 days.
But if you became a customer on 2/2/07, your anniversary date would be 3/2/07, whichh is 30 days.
Since not every month has the same numbers of days, I'm stumped.
How would I construct the formula so I could pull a list each day of who has hit their annivesary date. I can't seem to get a date diff formula that makes it happen.
Can anyone help with this? It would be much appreciated.
Anniversary date = DateSerial(Year (StartDate)+1,M onth(StartDate) +1, Day(StartDate))
I suspect that is too simple, OK for year one but years 2, 3 ... ??
The DateSerial function has the solution, but I will let you sort that!
DateSerial function is a very powerful/flexible function. I should look at the help file entry
MTB
edit
I have assume buy 'anniversary' you do mean the next year, as opposed to the next month you have indicated!?
I'll try to fiddle with it. Let me know if you have any other revalations. Thank you Mike
The srial formula worked sort of. It will show me the people on the same day next month.
Next question being, will I have to set up 12 separate fields for each month with a criteria that just equals the current days date, or can I get it to calculate it daily throughout the year?
The srial formula worked sort of. It will show me the people on the same day next month.
Next question being, will I have to set up 12 separate fields for each month with a criteria that just equals the current days date, or can I get it to calculate it daily throughout the year?
I am not sure what you requirement is.
Given that to-days date is 19 July 2007(or Date()), then for each person starting on 2 Jan 2007 what actually are you trying to return/filter ?
MTB
We bill Monthly by the date the customer becomes active. So if 5 become active today,their anniversaries dates would be 8/19/07, 9/19/07, 12/19/07, 1/19/08, 2/19/08.........and continues monthly until they are no longer a customer.
What I need is a report that can be pulled each day to show who needs to be charged on that day
Day(Date) returns the day of the of the month, i.e. 19 for 7/19/07. So, something like - If Day(Date) = Day(JoinDate) Then
-
'Payment is due today
-
'Code goes here
-
End If
Good Luck!
Linq ;0)>
I suppose I better get my VB skills up to snuff. Thanks
Day(Date) returns the day of the of the month, i.e. 19 for 7/19/07. So, something like
- If Day(Date) = Day(JoinDate) Then
-
'Payment is due today
-
'Code goes here
-
End If
Good Luck!
Linq ;0)>
Glad we could help!
Linq ;0)>
Hi Presto731
I think your definition of anniversary and mine are a different!
MTB
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: tertius |
last post by:
Hi,
Is there a more pythonic way to write or do a date difference
calculation? I have as input two date fields in the form 'YYYY-MM-DD'
TIA
Terius
|
by: mattmerc |
last post by:
Hi,
I would like to have a date_last_modified field for one of my SQL
tables. Instead of coding my front end to keep up with this field is
there a way to user a formula for this column in Enterprise Manager so
that each time a record is created or updated the current date/time
will be inserted/updated? I tried using getdate() but then the...
|
by: Lapchien |
last post by:
My table has a record called date/time - filled (not surprisingly) with a
date and time. I can format the date in one query field, also the time,
using format in an expression. My user wants me to provide a listing of all
table entries between 22:00 and 07:30 the next day, between a given set of
dates (typically one week apart but in...
|
by: A |
last post by:
Hi Group,
Coming from a VB background I am looking for the quickest path to do a date
diff in C#. I see that C# has no date diff function so this must be more
obvious than it looks.
Thanks
|
by: Raghu Raman |
last post by:
Hi
I want to find the difference between 2 dates in terms of year.It is
confusng in msdn.could u help me on that.
for ex,
int year=datediff("y",date1,date2)//of course it is in VB.
--------------
COULD U PLS TELL ME HOW TO DO IT IN C#.Net
| | |
by: dkrysmann |
last post by:
Hi,
I'm very new to this group and the php, now I've question about date
function.
I need function how to calculate time diffrence between two dates.
First date: $rs['lastBuildDate' is like 'March 6, 2006 10:04 am' next
date have to be today.
The expected output number of days and hours.
Later on I will add some If statements if days >...
|
by: Troy Lee |
last post by:
I have a report in Access. I want to calculate with the DateDiff
function. Two questions.
1. How do I set up the Date Diff to subtract a date in the past from
today's date? I want this to be dynamic, automatically incrementing
the day count based on the current day's date. (I do not input the
current day's date into any field, so it will...
|
by: Pauley |
last post by:
Hello,
Please keep in mind when you read this post that I am a total noob when it comes to coding.
Coding in ASP
On MS Server
SQL Database/Table
I know this topic has been covered many times and many ways. In fact I've tried to use one version or another of 20 examples with no success. All I am trying to do is determine the timespan...
|
by: sajitk |
last post by:
Dear All
I have two dates ile Start Date and End Date.
I want to get the difference of the 2 dates in months.....How do we do it...
For eg.
Start Date = 01/11/2009
End Date: 01/01/2010;
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
