Solved

Combine fields into 1 listbox

Posted on 2013-01-28
7
276 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 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 48

Accepted Solution

by:
Dale Fye 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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