Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Formula to Select Dates 1

Status
Not open for further replies.

byrne1

Programmer
Aug 7, 2001
415
US
I need a formula that will allow me to select customer who have birthdays coming in the next XX days. This would normally be easy to do but I have two issues:

1) The date field in my DB is stored in a numeric field as MMDDYY (where January 24, 1983 would be stored as 12483);

2) How do I account for year end (ie, if I run the report in December for birthdays in the next 60 days, how will it know to pull January and February birthdays?)

I have been struggling with this for some time now and I really hope someone can offer a solution. Thank you in advance for your help!
 
You could try something like this:

if len(totext({table.field},0,"")) = 5 then
date(1900+val(right(totext({table.field},0,""),2)),val(left(totext({table.field},0,""),1)), val(mid(totext({table.field},0,""),2,2)))else
if len(totext({table.field},0,"")) = 6 then
date(1900+val(right(totext({table.field},0,""),2)),val(left(totext({table.field},0,""),2)), val(mid(totext({table.field},0,""),3,2)))

This will work for those born in the 1900's. This converts the field to a date field which you can then use in your calculations.

-LB
 
OK, the formula works great in giving me a date from the field value. Now how would I retrieve records that have birthdays within the next 30 days?

 
For birthdays in the next 60 days, try the following. If you want the next 30 days, change the 60s to 30:

date(year(currentdate),month({@date}),day({@date})) in date(year(currentdate), month(currentdate), day(currentdate)) to dateserial(year(currentdate), month(currentdate), day(currentdate)+60) or
date(year(currentdate)+1,month({@date}),day({@date}))in date(year(currentdate), month(currentdate), day(currentdate)) to dateserial(year(currentdate), month(currentdate), day(currentdate)+60)

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top