How to select the next upcoming date from a date field

[Neil Chinneck]

Hello everyone, this is my first post.

I have a continuous form with several fields displayed, one of which is called 'PackingDate'. What I want to do his highlight the records with the next upcoming packing date.

So far, I've got to the stage where I can highlight those records with the earliest date by adapting this bit of code (http://www.tek-tips.com/faqs.cfm?fid=18 04) and using the Min([PackingDate]) as the reference, but this is not quite right. What I need to do is select the next upcoming PackingDate from this field and store this in the text box, rather than just the minimum date. I'm assuming this will require some sort of loop to select the earliest date until it finds one which is later than (or the same as) the current date.

Could someone help me please?

Thanks

Neil

[jimatqsi]

Here's how I might do it. Place an unbound textbox called txtPackDate somewhere on the form. Heading, detail, footer, doesn't matter where. You might want to make it not visible, but that's your choice.

In the OnOpen event of the form, set that text box value to be this lowest available packing date. Then, in the conditional formatting of the text boxes you want, set the back color based on a comparison of that line's packing date compared to the value of this txtPackDate textbox.

That should do it. Let us know if you need help on an easy way to set the value in txtPackDate.

Jim

[Neil Chinneck]

Jim,

Thanks for the suggestion, but that's pretty much what I'm doing at the moment, except that I'm using transparency, and a text box behind the row fields to do the highlighting, rather than conditional formatting. My issue is the actual date that would go in the txtPackDate (in your example) box. This needs to be not the lowest available date (as this might be a date in the past) but the lowest date after today's date i.e. the next upcoming date.

So its really suggestion on the code needed to find this date that I'm after.

Thanks for the input though

Neil

[Delerna]

Do it in the query that the continuous form is bound to.
You don't give any details so here is a stab in the dark.


[code=sql]
SELECT
..unknown field list..,
PackingDate,NPD
FROM (select 1 as J,* FROM theTable) a
join ( SELECT 1 as j,min(packingDa te) as NPD
FROM theTable
WHERE packingDate>=no w()
) b on a.j=b.j
[/code]

NPD should now have the next packing date that you mentioned in it and you can use that for your comparisons

I added the 1 as j fields to the two sub queries so that the 1 record in the "b" subquery will join to every record in the "a" subquery.


you could do this
[code=sql]
SELECT
..unknown field list..,
PackingDate,
( SELECT min(packingDate ) as NPD
FROM theTable
WHERE packingDate>=no w()
) as NPD
FROM theTable
[/code]

but I don't recommend it because
this

     ( SELECT min(packingDate) as NPD 
       FROM theTable 
       WHERE packingDate>=now()
     )

will get run once for every record in theTable.
So if there were 1,000,000 records in the table
then the query as a field would be run 1,000,000 times.

In my first query it will only be executed once for the entire query......much more efficient

[NeoPa]

Just a quick comment. When using dates, be careful of using the Now() function instead of the Date() function. Except at midnight, Now() will always be greater than Date(). This can have undesired side-effects when working with dates (as opposed to date/times).

[Delerna]

yea, good point Neopa