Show the date closer to today & bigger & smaller than today

  • Thread starter Thread starter MyVi
  • Start date Start date
M

MyVi

Hi everyone.

There is something that I donno know how to do it.
Two things wondering how to...

I'm working on a form, within a Textbox, where I need to get:

1.- show the bigger date you have in a query's column (in the textbox
within the form) but always smaller than DATE(). For instance: We have
in the query's column these dates: 1-9-05, 15-9-05, 20-9-05, 10-10-05
and I need to see on the textbox simply 10-10-05 'cause is the closest
date we have up today (or DATE()) and is not bigger than today also.

2.- show the smallest date you have in the column but also bigger than
today (DATE()).

I'm also doing other calcs... and I'm doing this within the FORM in the
textbox on Control Source, where you get the resoult directed shown. I
wonder how to do 1 & 2 in the same way.

Thank you so much


Vic
 
To show the latest date that is earlier than today:

SELECT Max(T.DateFieldName)
FROM TableName AS T
WHERE T.DateFieldName < Date();


To show the earliest date that is later than today:

SELECT Min(T.DateFieldName)
FROM TableName AS T
WHERE T.DateFieldName > Date();
 
Ken, thank you for answering.

Do I have to create a query to use what you have wrote?
The date I have is in a Query (due to is already filtered from a TABLE)
and the final resoult I would like to get it shown in a FORM instead
(sure I know I can create a Query and then filter all the stuff and
create the FORM from that new Query, but I wonder.... can I use that
code directly into the FORM?

Thank you

Vic
 
There are a couple of ways you can do this in a form... depending upon what
you want to show.

It appears that you just want the date value itself to display in a textbox?
If yes, we can use the DMax function as the control source of a textbox on
the form.

To show the latest date that is earlier than today, put this expression
(change the generic names to the real names) as the Control Source of a
textbox on the form:

=DMax("DateFieldName", "TableOrQueryName", "[DateFieldName] < Date()")

To show the earliest date that is later than today, put this expression
(change the generic names to the real names) as the Control Source of a
textbox on the form:

=DMin("DateFieldName", "TableOrQueryName", "[DateFieldName] > Date()")
 
Thank you Ken,

I just found that out.

Yes, as you said so. What I have done is

=DMax("[D_LAST]";"Esta_486";"[D_LAST]<DATE()")
and it works 100% as I was looking for.

Thank you so much.

Vic
 
Back
Top