Seperate Groups in a Listbox 2

[ckeener]

Hello,

I have a listbox with entries that include the date. I would like to be able to seperate the all the entries by date (ie. line, different font color, or something so that is is easy to tell when information chnages to another day)

example:
4/25/06 John Brown Maintenance 5 hrs
4/25/06 John Brown Repairs 2 hrs
4/26/06 John Brown Maintenance 3 hrs
4/26/06 John Brown Repairs 4 hrs
4/27/06 John Brown Maintenance 8 hrs
4/27/06 John Brown Repairs 0 hrs

I am at a loss at how to accmoplish this but it would make things easier to read.

Thanks,
ckeener

 

[spizotfl]

to get the dates ordered, in the row source use a query with an order by clause on your date field.
as far as changing colors, maybe do some mod division of the date cast to int and set different colors based on that value. you would have to look up the details on how to make that work, though....

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[ckeener]

Thanks spizofl,

I actually already have the dates ordered the way that I want. My problem is figuring how to change the colors within the listbox based on date. It doesn't even have to be a color thing. Any way to be able to easily tell the dates apart. I really am not sure where to look up the details on making that work.

Any ideas on that?

Thanks,
ckeener

 

[BoxHead]

I don't know of any way to change the formatting within a listbox. The following may get a satisfactory result for you.

Your data:

example:
4/25/06 John Brown Maintenance 5 hrs
4/25/06 John Brown Repairs 2 hrs
4/26/06 John Brown Maintenance 3 hrs
4/26/06 John Brown Repairs 4 hrs
4/27/06 John Brown Maintenance 8 hrs
4/27/06 John Brown Repairs 0 hrs

I assume the SQL for this to be along the lines of (typed not tested)
SELECT [myDate], [employeeName], [jobFunction], [hoursWorked] & " hrs" AS Expr1
FROM myTable
ORDER BY [myDate];

In design view, create anther listbox for testing purposes and try changing it to
SELECT [myDate], [employeeName], [jobFunction], [hoursWorked] & " hrs" AS Expr1
FROM myTable
UNION
SELECT DISTINCT [myDate],"","",""
FROM myTable
ORDER BY [myDate];

This should change the listbox data to:
4/25/06
4/25/06 John Brown Maintenance 5 hrs
4/25/06 John Brown Repairs 2 hrs
4/26/06
4/26/06 John Brown Maintenance 3 hrs
4/26/06 John Brown Repairs 4 hrs
4/27/06
4/27/06 John Brown Maintenance 8 hrs
4/27/06 John Brown Repairs 0 hrs

I don't know the rowsource you're using so there will probably have to be some modification beyond filling in your own field and table names, but the union query should allow you to add a row as a linebreak between the dates.

Be aware that any events you have running off of selections in the listbox will have to be set up to deal with someone selecting one of these filler rows.

HTH


John



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[ckeener]

Good Idea John! Thanks.

I am still having a little trouble with it because is does not want to sort like you example.

Here is the SQL:

SELECT tblTimeSheet.AutoID, tblTimeSheet.Date AS [Date], tblTimeSheet.Employee AS Employee, tblTimeSheet.WorkArea AS [Work Area], tblTimeSheet.StartTime AS [Clock In], tblTimeSheet.EndTime AS [Clock Out], tblTimeSheet.Hours
FROM tblTimeSheet
WHERE (((tblTimeSheet.Employee) Like [cmbEmployee].[value] & "*"))
UNION
SELECT DISTINCT "", tblTimeSheet.Date AS [Date],"","","","",""
FROM  tblTimeSheet
ORDER BY tblTimeSheet.Date DESC;

but I get an error message:

" The ORDER BY expression (tblTimeSheet.Date) includes fields that are not selected by the query. Only those fields requested in the first query can
be included in an ORDER BY expression."

Any ideas on fixing this problem.

If I do not have an ORDER BY after the second SELECT I get data like

AutoID Date Employee Work Area Clock In Clock Out Hours
4/24/2006
4/25/2006
4/26/2006
4/27/2006
4/28/2006
21 4/25/2006 Dale Holler Play Supervision 7:30:00 AM 8:00:00 AM 0.5
22 4/25/2006 Dale Holler Play Construction 8:00:00 AM 9:00:00 AM 1
23 4/25/2006 Dale Holler Student Programs, Chapel 9:00:00 AM 9:45:00 AM 0.75

 

[PHV]

Replace this:
ORDER BY tblTimeSheet.Date DESC
with this:
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ckeener]

Thanks John and PH,

Excellent ideas and help. This is a lot easier to read.

Star each.

Thanks again for your help.