More than one record on a row?

[kbrooks]

I have a database with only 2 fields...Name and Birthdate.

I have this query set up to generate a list of people that have birthdays today:

SELECT Birthdays.Birthdate, Birthdays.Name, DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate])) AS CurrBD
FROM Birthdays
WHERE (((DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate])))=Date()))
ORDER BY DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate]));

This works fine and does give me the correct list, but it's putting each record on it's own row. What I'd like to do is have them all on one row, separated by commas. (John Doe, Bob Smith, Jane Johnson)

Is this possible?

 

[KeithG]

What about a cross tab query?

 

[kbrooks]

How would that work? I've been playing with it but I've never used them before and I'm not coming up with anything useable. I have it where it will put each name in a separate column but also in a separate row.

10/21/2008  George Jones
10/21/2008                       Steve Smith
10/21/2008                                            Tammy White

 

[Rabbie]

If you use your original query to generate a dataset containing all the birthdays for a given date then you could write some VBA to output these to a text box which would give you what you wanted.

 

[kbrooks]

Thanks for your reply.... but that's way beyond my level of expertise. Any chance of telling me exactly how to do that? Would be much appreciated...but of course I'd understand if you don't want to do my work for me.

 

[Rabbie]

Hi
Here is attached DB.
Open Form 1 to see what is created.

 

[kbrooks]

Wow thank you, I very much appreciate the help!

I set up a module identical to yours but when I run it, it stops on that first 'Dim' line and highlights "rs As DAO.Recordset". The error is "Compile error: User-defined type not defined".

I went ahead and set up the form anyway, because I know nothing about modules and thought maybe it didn't matter, but I get #Error in the field.

Which parts of that module do I need to change with my own field/table/query names? I changed query1 to the name of my query but it didn't look like anything else needed changed?

 

[kbrooks]

Never mind, I googled the error message and got it fixed. Thank you SO much for your help!!!!

 

[kbrooks]

Ok another question....and I should've mentioned this off the bat.

Ultimately I want this list of names to go on an intranet site, through database results. Apparantly database results can only pull from a query or a table, not a form. Is there any way to get this list of names into a query, or populate some kind of temp table?

 

[Rabbie]

In the DB I posted there is a Public function which gives you the string you originally requested. I just put the result on a form as it was the easiest way to show the result.

I have now added query2 which shows how you can get the birthdays in a query.

 SELECT Showbd() as Birthday;