Access Reports Help

[jamieharrop]

Morning all,

I'm currently setting up a customer management database.

I've setup a table with all the fields and I've setup a form to populate those fields.

One of the table fields is "Date of Birth"

I'm trying to produce a report that will display all the customers with upcoming birthdays.

Ideally I'd like to be able to input a time frame (1st November to 30th November for example) and see an output of all the birthdays within this time frame. The list should be organised with the earliest birth day (not birth year) first.

If I have to split the "Date of Birth" field in to three separate fields (day, month and year) then that's fine.

It's a long time since I've played with databases (this is the first time since high school. Oh how I wish I listened to my teacher).

Any guidance would be appreciated.

Thanks!

Jamie

 

[jamieharrop]

I have managed to find one way of doing it, but it's quite cumbersome.

I've setup a query for each month of the year. By splitting the date of birth in to three fields, and then using the criteria "=11" (for November) in the November query, I've been able to get the results I want.

However, having 12 queries and 12 reports seems far too complex.

I have to assume there's an easier way (by being able to manually insert the month in a form each time I run the query/report). If this can be done, I'd appreciate some pointers.

Thanks!

 

[PHV]

I strongly suggest to NOT splitting your DOB into 3 fields !
Have a look at the Month function.
You need only one report based on one parametized query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jamieharrop]

Thanks for the response, PHV.

However, I'm still not sure what the solution is.

I'm not seeing the month function anywhere, and I'm not sure what the one parameter needs to be to create the correct query.

Hope that makes sense.

Jamie

 

[dhookom]

The Month() function returns the month number of a date. IE:
Month(#31-Oct-2008#) = 10
You might be able to use this to filter your query
Month([date Of birth]) = ...

If you want a range of dates that might not be tied to a month, you could add a couple text boxes to a form for users to enter start and end dates. Then use

WHERE Format([Date of Birth],"mmdd") Between Format(Forms!frmDates!txtStart,"mmdd") AND Format(Forms!frmDates!txtEnd,"mmdd")

Duane
Hook'D on Access
MS Access MVP