asked on

Combine fields into 1 listbox

Dear Experts

I have a query which brings up various events that are happening over the course of the next fortnight. This query "Next Fortnight Part 2" has 5 different fields which I'd like to combine into one ignoring blanks & display it list box to display on a form.

The code for the query is below:

SELECT [Next Fortnight Part 1].[UpComing Start Date], [Next Fortnight Part 1].[UpComing Prob Date], [Next Fortnight Part 1].[UpComing End Date], [Next Fortnight Part 1].[UpComing Review Date], [Next Fortnight Part 1].[UpComing Birthday]
FROM [Next Fortnight Part 1];

Open in new window

Can anybody help?

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Crowfoot

ASKER

hi JimHorm

Thanks for that, all works well, except where there is an instance of 2 fields against 1 record - see screenshot below:

In this example Fred Smith has both a start date & a birthday happening in the next fornight

Any ideas how I can get the 2 fields split onto seperate lines?

Dale Fye

Looks like your "Date" fields contains a lot more information than just the date.

You will need to use the UNION method I posted below in order to break those entries into separate lines in your listbox.

Tom Crowfoot

ASKER

Thanks for these - used fyed's solution - works perfectly, have awarded points to JimHorn as well as his solution answered the question as asked as I didnt mention the liklihood of mutliple data in a record. Hope this is all ok

Dale Fye

BTW,

I would strongly recommend that you normalize this table with a [PersonID], [Event], and a [EventDate] field. This will allow you to have as many (or few) fields as you need to describe events for individuals. Otherwise, when you decide you need to track a new "Event", you will add a new field (BAD) and have to modify the query to make sure you have that field added to your list.

You should also have a table of "Events" to allow your users to select from.

Tom Crowfoot

ASKER

Thanks for that - good point will do that