Solved

Combine fields into 1 listbox

Posted on 2013-01-28
7
269 Views
Last Modified: 2013-01-28
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?
0
Comment
Question by:correlate
  • 3
  • 3
7 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 38826747
My usual style is to create a query specific for a combo box/list box.  Save the below as qlst_your_listbox_name, then refer to it in the Row Source property of your list box:

SELECT
      [UpComing Start Date] &
      [UpComing Prob Date] &
      [UpComing End Date] &
      [UpComing Review Date] &
      [UpComing Birthday]
FROM [Next Fortnight Part 1];
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 38826768
I usually use a union query, but my IT guys have blocked my text, see my image for an example.sample queryThis will create a list with two columns, the first being a description of the event, the second being the date.

You will also need to add a criteria to the WHERE clauses to limit the results to a single record, or add an additional field that identifies the PK of the record.
0
 

Author Comment

by:correlate
ID: 38826784
hi JimHorm

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

Query ScreenShot
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?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38826805
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.
0
 

Author Closing Comment

by:correlate
ID: 38826877
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38826913
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.
0
 

Author Comment

by:correlate
ID: 38826949
Thanks for that - good point will do that
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now