Birthday Query (1 Viewer)

marc515151 · Oct 11, 2009

Hey all,

I am very new to access 2007, i can do the basics of creating tables, queries and reports. nothing too fancy.

I have a list of records of customers with DOB as one of the fields set as date/time.

I need to have a query that will look at the dates and show records whos birthdays are coming up within the next three weeks. Ive tried a few but cant seem to work out a way around the year of the DOB.

Any help would be great! i would need detailed help for anything past novice work.

Thanks,
Marc

Brianwarnock · Oct 11, 2009

You can use Dateserial to convert the DOB to this year's birthdate
DateSerial(year(date()),month(dob),day(dob)) and then use Datediff to see if this is within the timescale, something like
Datediff("ww",Dateserial etc,Date()) >=0 and Datediff("ww",Dateserial etc,Date()) <=3

If doing it in a query then Datediff("ww",Dateserial etc,Date()) becomes a fresh column and the criteria between 0 and 3 probably works.

Brian

Brianwarnock · Oct 11, 2009

Later realised that in the next 3 weeks probably meant next 21 days so just

expr: DateSerial(year(date()),month(dob),day(dob))-date()

and in criteria
Between 0 And 21

will select the records that meet this.

marc515151 · Oct 17, 2009

im sorry for sounding stupid but where do i put the:

DateSerial(year(date()),month(dob),day(dob))-date()

Brianwarnock · Oct 17, 2009

It goes in the field row of a new column in the query.

Brian

marc515151 · Oct 17, 2009

Right here is what i have. Now in the table i have my date of birth entered as 18/10/1986. Everytime i run the query i dont get any results.

--LINK REMOVED --http://img127.imageshack.us/i/queryfield.jpg/

Brianwarnock · Oct 17, 2009

When clicking on this link my PC became infected with trojans and viruses.
I'm outa here to do a cleanup.

Brian

marc515151 · Oct 17, 2009

Thats strange, i only uploaded it to imageshack. I apoligize for that if it had anything to do with me, ive uploaded the image to my own webserver where i no it is safe.

Brianwarnock · Oct 17, 2009

It may have been a coincidence, I think it was a spam to tempt me to run a program which I did attempt but my security software intercepted, anyway a full scan revealed no viruses etc, but Im going to run a second checker in a moment.

your problem

Its unlikely that your DOB will be this year !

Change this
expr: DateSerial(year(date()),month(dob),day(dob))-date()
to
expr: DateSerial(year(date()),month(dob),day(dob))
This will give the Birthday date and then place the criteria in this column.

Brian

raskew · Oct 17, 2009

Hi -

Try this, replacing table name and highlighted field names to concur with your table. Note, allowing spaces in your table names, e.g. [Full Contact Details], adds an unnecessary level of complexity to your application.

Code:

SELECT [COLOR="Red"]LastName[/COLOR], [COLOR="Red"]FirstName[/COLOR], DOB, DateDiff("d",DateSerial(Year(Date()),Month([DOB]),Day([DOB])),Date()) AS Upcoming
FROM [COLOR="Red"]tblClients1[/COLOR]
WHERE (((DateDiff("d",DateSerial(Year(Date()),Month([DOB]),Day([DOB])),Date())) Between 1 And 21))
ORDER BY DateDiff("d",DateSerial(Year(Date()),Month([DOB]),Day([DOB])),Date());

HTH - Bob

marc515151 · Oct 18, 2009

Brian i would like to thank you so very much!! i have got it to work with your help and it is greatly appriciated!

Very greatful,
Marc

marc515151 · Oct 21, 2009

sorry brian i have found a problem when testing this before putting it into use.

If a birthday is on say 31/10/1986

and i do a search between 20/10/2009 and 31/10/2009 = the birthday shows up.

If i do a seach between 20/10/2009 and 01/11/2009 = i get no result

at this moment i only have one record in the table which im using as a test to make sure all is working.

Is there any way around this issue of searching between two dates in different months.

Thanks,
Marc

raskew · Oct 21, 2009

If your intent is still

...birthdays are coming up within the next three weeks...

suggest you give post #10 a try.

Bob

marc515151 · Oct 21, 2009

ok ill try post 10. ill rename the fields, just one question, do i add that into the field part of my query, like i did with the other one?

raskew · Oct 22, 2009

Hi -

Post your query SQL as it now exists and I'll attempt to modify it to accomodate my solution.

Note: I've tested this solution against a table with LastName, FirstName and DOB and it has
worked without a problem.

Bob

marc515151 · Oct 22, 2009

Below is what my query is looking like.

in that first field i have the following entered into it.

Code:

SELECT LastName, FirstName, DOB, DateDiff("d",DateSerial(Year(Date()),Month([DOB]),Day([DOB])),Date()) AS Upcoming FROM tblClients1 WHERE (((DateDiff("d",DateSerial(Year(Date()),Month([DOB]),Day([DOB])),Date())) Between 1 And 21)) ORDER BY DateDiff("d",DateSerial(Year(Date()),Month([DOB]),Day([DOB])),Date());

But i get a syntax error when trying to run it.

raskew · Oct 22, 2009

Eeh?

Something wrong here! It'd be a remarkable coincidence if your table was named tblClients1, as shown in my example. Try this:

1) Create a query using your table.

2) Add a calculated field (copy this directly)

Upcoming: DateDiff("d",DateSerial(Year(Date()),Month([DOB]),Day([DOB])),Date())

3) In the criteria cell of field Upcoming, place this:

Between 1 And 21

Bob

marc515151 · Oct 22, 2009

i renamed the table when i looked at what you had wrote.

I give up! in way over my head lol. But this might be the easiest option. Here is the file itself if you dont mind looking at it.

http://www.centrepointbowl.com/access/Database.accdb

raskew · Oct 22, 2009

I don't have A2007. Please convert to an earlier version (A97 - A2003) and repost.

Bob

marc515151 · Oct 22, 2009

http://www.centrepointbowl.com/access/Database02-03.mdb

Many thanks!

Birthday Query (1 Viewer)

Registered User.

Retired

Retired

Registered User.

Retired

Registered User.

Retired

Registered User.

Retired

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread