Reminder dates in listbox

[tuxalot]

Hi all,

I have a select query like this:

SELECT   qryTrackingDate.[Employee ID],
         qryTrackingDate.[Trace Date],
         qryTrackingDate.ELastName,
         qryTrackingDate.EFirstName
FROM     qryTrackingDate
WHERE    (((qryTrackingDate.[Trace Date])>Date()))
ORDER BY qryTrackingDate.[Trace Date];

This presents in a list box of items that require follow-up - the user selects the tracking date. I would like the dates that are within 5 days of expiring to show in red text. A second feature might be to add an on open event that can email a user that a date is about to expire, but that might be something for a different day.

As always, any help is much appreciated!

Tux

[ADezii]

@tuxalot

I would like the dates that are within 5 days of expiring to show in red text.

Don't think that this would be feasible. Why not simply have a 2nd List Box for this Function, or prompt the User for a specific amount of Days within an Expiration Date prior to populating the List Box?

[ChipR]

I'm not sure that this is possible within a list box, but you could certainly open a continuous form (or a subform on the form) with this as the data set, and use conditional formatting on the expiration date control. A button on the form could be used to send email notices for multiple records, or a button on each record for individual ones. Lots of options.

[tuxalot]

A second listbox is an option - but prompting the user for a specified date might not. Some items may be put on the "tickler" for 30 days, others maybe only a few days. Depends on the issue at hand.

Not sure how to implement a second listbox. Perhaps you can point me in the right direction or provide examples?

I've attached a png for reference.

Thanks,

Tux

[ADezii]

@tuxalot

the user selects the tracking date.

Do you mean 'Trace Date' as derived from Column 2 in the List Box? If not, then how is the User selecting the Tracking Date?

[NeoPa]

Tux,

It helps if you only refer to items that you have explained. It makes it so much easier to communicate clearly. Otherwise we spend loads of time wondering what you mean by Tracking Date and reformatting your SQL to see if anything there is relevant (one assumes not if it's just dumped in as a single line - mostly invisible to the reader without painful scrolling).

In answer to your question, you would need to change your WHERE clause to filter out all but the records you need to see highlighted. I'm unable to clarify that as yet, as your specification makes little sense at the moment.

[tuxalot]

To clarify:

NeoPa - I figured placing the sql between code tags was the preferred method to keep it clean - now I know otherwise.

I do not want to filter out entries using the where clause. I want them to appear in the listbox as they are. Only the entries approaching expiration should show in red text while the others lower in the list (with the list in ascending order, as it is now) will show as standard text. I hope this makes sense.

ADezii - The user is selecting the tracking date using the date picker in the comments form to the left.

[NeoPa]

@tuxalot
I hope it's clear that using the CODE tags is not optional.

The CODE tags won't make it readable all on their own though. If you want it to be readable then you need to use a bit of common sense and format it yourself, just as you should in your code anyway. If I found code like that that I was expected to work on professionally, my first step would be to shoot the originator to ensure no-one ever had to suffer their code in future.

I'm not likely to come across your code professionally of course, and your style there is your own business. Here though, you are expected to make it readable if you want us to read it for you.
@tuxalot
It makes sense, but only in as much as this was clear earlier and you were told twice (it seems to me) that this is not possible.

An alternative, that you expressed interest in, was to create a separate ListBox where only the items you are interested in are included. Now you seem to be saying this is not what you're interested in. Fair enough, but it would be easier if you were clear about exactly what you did want.

[ADezii]

With incomplete information, and using a Table directly instead of an Intermediate Query, the SQL Statement for populating the 2nd List Box would be something similar to:

SELECT tblTest.EmployeeID, 
       tblTest.[Trace Date], 
       tblTest.ELastName, 
       tblTest.EFirstName
FROM tblTest
WHERE (DateDiff("d",Date(),[Trace Date])<=5 And DateDiff("d",Date(),[Trace Date])>0)
ORDER BY tblTest.[Trace Date];

P.S. - Just read your Post #7, simply substitute the Date extracted from the Date Picker for [Trace Date] in the WHERE Clause of the SQL Statement. Again, to the best of my knowledge, this cannot be done (Multiple Text Formats) within the context of a single List Box.

[tuxalot]

NeoPa - You sure make it difficult to enter into Access programming using this forum. Is this forum not for people like me to bring possibly poorly written code, or to approach knowledgeable people like yourself with a need to see if a desired function is feasible in their database? I think the below sql is cleaner and easier on the eyes.

SELECT qryTrackingDate.[Employee ID], qryTrackingDate.[Trace Date], qryTrackingDate.ELastName, qryTrackingDate.EFirstName
FROM qryTrackingDate
WHERE (((qryTrackingDate.[Trace Date])>Date()))
ORDER BY qryTrackingDate.[Trace Date];

BTW, the code above is written by Access - perhaps you should take up the issue of "style" with Bill.

The second listbox is still an option - where you got that I was not interested in that? Maybe it was when I said the following:

Not sure how to implement a second listbox. Perhaps you can point me in the right direction or provide examples?

Thanks Adezii for being so helpful. And *** Edit Removed as flaming ***.

[NeoPa]

@tuxalot
No. It was here.

I'm sure you appreciate that your attitude is not acceptable Tux. Part of my job is to protect our members from lazy people. They do not need people, asking for help, to show the attitude that taking any time to make the post readable is too much for them (as you seem to want to do).

Bill Gates bears no responsibility for your actions. If Bill were here asking for help then he, too, would be subject to our rules. As it is - You are, on both accounts.

Please check your PMs.

[tuxalot]

As I said, and I'll say again, I thought the text was readable. If it was not I stand corrected. I do review my posts, I try to include all relevant information to get the help I need. Lazy? Hardly. New? Yes. A quick look at my post count will show that I am new here and extending a little courtesy and understanding to us newbies would be appreciated.

I understand your time as well as the other experts that frequent this forum is valuable and it is not my intent to cause undue hardship. Educate. Don't bash. Your attitude Neo does not help anyone.

[NeoPa]

83 posts New? Please. How much slack do you expect to be cut?

I say it as I see it Tux, and frankly your attitude is a long way from impressive. What you claim simply doesn't hold water. To be honest, words are just words. Your post before it was fixed was not easy to work with. Fact. A (very) little consideration would have fixed that. You can make all the claims you like, but deeds always speak louder than words.

PS. An apology, while denying any responsibility, is not worth the words it took to make it.

[tuxalot]

NeoPa - Let's face it - you bash many who are just trying to get some helpful advice here. Should not your attitude be exemplary? Just look through many of the recent posts here and in so doing it is likely that somewhere in the thread there is NeoPa, interjecting some snide remark. I spent time on the site today and there are many examples that confirm this. Why not just ease up? Are your derogatory remarks actually helping?

No, we are not perfect, we need to be coached and with educators and experts like we find here, we LEARN. Perhaps this site is best suited for experts asking questions of experts. Too bad, there are many like me who could use a helping hand.

I think enough has been said here. I'll steer clear of your posts and stick to those here who actually want to help and there are many.

[NeoPa]

Fine words Tux, but it (quite typically) misses the point.

My responsibility is to protect our experts, as much as possible, from lazy and bad-mannered posters. You may, in your limited experience, assume that without policing, everybody will simply behave well and make the effort to post in such a way as to make sure our experts don't have to run around putting extra effort in simply to discover what the question is supposed to be. In this you would be quite misguided. If you really have had a look around as you say, you would see this without having to think too deeply.

I will always listen to advice, but your ranting gives no evidence of even appreciating the issues, so you will excuse me if I won't allow myself to be directed by someone who seems clearly intent only on trying to create a smoke-screen so that no-one will look too deeply at their behaviour, and shows no signs of having any wisdom to offer. Perhaps when I see some of your contributions helping people (or even simple attempts to help), then I may review my opinion.

If I put 5 posters off using the site, to save any one of our experts having to deal with the sort of lazy nonsense we get here quite frequently, then I consider that a good job well done. To be perfectly frank, I would be happy to see the back of any and all posters who seem to have that attitude that our experts were put on Earth for the specific purpose of helping them out, without regard to manners or even bothering to express their question clearly enough to be understood without jumping through hoops.

Clearly though, if you feel that you have a valid point and that I am simply too self-involved to see it, then you are welcome to bring this up with other administrator(s) of the site. I'm not above the rules of the site. I can't imagine anyone taking 'this' too seriously though. Can you?

[Delerna]

As stated, it is not possible to produce the desired effect in a list box.
By desired I mean

I would like the dates that are within 5 days of expiring to show in red text

But there is always more than 1 way to skin a cat.

How about something like this...........just as an idea.

SELECT   qryTrackingDate.[Employee ID], 
         qryTrackingDate.[Trace Date], 
         DateDiff("d",Date(),[Trace Date]) as DaysRemaining, 
         qryTrackingDate.ELastName, 
         qryTrackingDate.EFirstName 
FROM     qryTrackingDate 
WHERE    (((qryTrackingDate.[Trace Date])>Date())) 
ORDER BY qryTrackingDate.[Trace Date]; 
 

Now the user can see how many days are remaining.
Not quite the same as highlighted red, but still effective


PS
I for one appreciate NeoPa's efforts and certainly do not see that he is bashing you. It really is difficult to get motivated to help someone when they don't even format posted code in a way that makes it readable.
Just take his Good advice onboard for future posts
Thanks Neopa

[tuxalot]

Thanks Delerna for your post. Your solution achieves the desired effect.

Tux

[ChipR]

Just curious, but is there any reason to use a listbox over a subform?

[tuxalot]

No reason Chip, and from your prior post and what I am learning a sub-form may be a more appropriate way to do this. This db was created by another and I'm modifying it and learning Access along the way. I'm reading more about how to replace the listbox with a subform and apply conditional formatting as you mentioned previously.

Thanks for the reply,

Tux

[NeoPa]

@Delerna
Thanks Delerna. I appreciate the support :)

Tux,

Although we've all been trying to respond directly to your original question, I think that Chip's ideas are probably more appropriate as a solution for you in the longer term. Well worth your while getting some experience in that area too I would say. You'll probably enjoy the extra scope.

[tuxalot]

Thanks NeoPa. I'll educate myself a bit here and call in when I get stuck.

Tux

[Delerna]

I also think the subform is probably the superior option. My post was more about thinking "outside the square"

[tuxalot]

It's very nice to have options.