Solved

Combine fields into 1 listbox

Posted on 2013-01-28
7
273 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

806 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