want to remove year of birth

[cathalfarrell]

I'm building a customer database and I want to directly market customers based on their DOB, i.e its your birthday next week, why don't you book your party with us. Year of birth is irrelevant so I want remove completly.

How can I remove the year to enable me to sort by day and month without the year?

your help is greatly appreciated.

Thanks

 

[John Big Booty]

Have a look at the DatePart() function you should be able to use it to extract the information you require.

 

[cathalfarrell]

Thanks Bob, I'm quite new to access and wasn't familiar with the date part function so your link really help me understand some of the options available to me when working with dates.

I have sorted all birthdays by week number.

Next step that I have difficulty with is that I would like to design a query that when I select a date it will present me with all records where the date of birth is the following week number?

Everyones help is always much appreciated,

 

[namliam]

You do NOT want to cut up dates, when you do they lose all type of meaning .... Atleast that is if you are storing it in your database, extracting information out of it is another matter offcourse.

Now... Format is a usefull tool to manipulate dates:
Format(date(), "YYYYMMDD")
Will give you iso date, you can manipulate that a little to give you only month and day.

Weekday is another usefull function
weekday(date(), 1)
Will give you a 1 for today....
Date() - weekday(date(), 1) + 7 will give you next sunday.

Then using the format you can do > and <= to find all for next week

 

[cathalfarrell]

Want to sort by week of birthday date

Thanks for your help Namliam,

I have kept the original Year of Birth but wanted to order by week of birth, so have formatted the DOB field to display dd/mm.

The problem I am having is that when I format into week number using DatePart it still takes into account the year of birth instead of just the year and month.

For example, the 02/06/10 was in the 23rd week, the 02/06/89 was in the 22nd week. I want the week of the year for day and month of customers birthday but for the current year.

Many thanks
C.

 

[namliam]

Yes, but that is because your doing week of the date.... which holds the year.

If you want to bring the date to this year something like:
Dateserial(Year(date()), Month(yourDate), Day(yourDate) )
or
CDate(year(date()) & Format(Yourdate, "-MM-DD"))
or something simular will bring it to this year

 

[cathalfarrell]

Hi Namliam

Sorry I'm only getting back to your response now, the project had fallen off the radar due to other commitments.

Thanks for the reply, it worked a treat and I now have everyone birth date separated in this years week number.

Much appreciated, thanks

C.