Solved

Combine fields into 1 listbox

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 40
Create Form using Wizard 14 32
Access Excel export not behaving 2 25
Qry for calculation on form, on click of Checkbox 3 5
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

24 Experts available now in Live!

Get 1:1 Help Now