Combining two date fields in a report

[ivnurse]

I have an existing database with "LastName", "FirstName", "Birthdate",
"SpouseName" and "SpouseBirthdate" fields. I want to create a report that
combines the "Birthdate" and "SpouseBirthdate" fields into one and have them
all print in chronological order.

Help!

 

[boblarson]

Sorry, but that doesn't quite make sense. Can you post a sample of what your
output would be expected to look like?

 

[ivnurse]

Yeah, sorry.

My input form has the following:

LastName
FirstName
Birthdate
SpouseName
SpouseBirthdate

I want the report to output all birthdays in chronological order regardless
of whether the birthdate is contained in the "Birthdate" or "SpouseBirthdate"
field.

BIRTHDATE NAME
01/16 Jones, Bob (this would be from the "Birthdate" field)
01/23 Jones, Carol (this would be from the
"SpouseBirthdate" field)
02/11 Smith, Barbara (again from the "SpouseBirthdate" field)
02/12 Williams, Ann ("SpouseBirthdate")
03/16 Smith, John ("Birthdate")
03/16 Williams, Ron ("Birthdate")

 

[boblarson]

You can use a Union Query to get this:

SELECT Birthdate, LastName & ", " & FirstName As PName
FROM YourTableNameHere
UNION
SELECT SpouseBirthdate, SpouseName
FROM YourTableNameHere
ORDER BY BirthDate, PName


But if your SpouseName isn't in Last, First order then you would need to
modify the code further.

 

[Jeff Boyce]

To build on what Bob's already provided, it would help to know what data you
are storing in the field you named [SpouseName]. It's possible to call a
field anything but store something unusual/unexpected/unrelated, so having
an example of what's stored there would help.

You are concatenating [LastName] and [FirstName] to handle some data ... but
do you need to concatenate [LastName] and [SpouseName] also? (and what do
you do if the Spouse uses his/her OWN LastName?)

More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/