Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combine fields into 1 listbox

Posted on 2013-01-28
7
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 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 48

Accepted Solution

by:
Dale Fye earned 1600 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

660 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