show record that are Close to now date (1 Viewer)

eshai

Registered User.
Local time
Today, 15:03
Joined
Jul 14, 2015
Messages
193
is there a way to show only the record that are close to now date
for now i want to get around the "max function"
i have a query that build from 4 queries
one field called "caredate" i already used "max" in one query but im getting All the results (its ok)
now i need to show only the record that close to "date()"
some function in the cratitia "show only close to date()" ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:03
Joined
Sep 21, 2011
Messages
14,052
What do you call close?
3 days, 5 days 7 days???
Code:
WHERE Caredate Between  (Date() - <YourNumberOfDays>) AND Date()
 

eshai

Registered User.
Local time
Today, 15:03
Joined
Jul 14, 2015
Messages
193
What do you call close?
3 days, 5 days 7 days???
Code:
WHERE Caredate Between  (Date() - <YourNumberOfDays>) AND Date()
The closest date Without reference "y" "m" "d"
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:03
Joined
Sep 21, 2011
Messages
14,052
How do you define what is close to a date if you you do not use year, month or day? :confused:
 

eshai

Registered User.
Local time
Today, 15:03
Joined
Jul 14, 2015
Messages
193
How do you define what is close to a date if you you do not use year, month or day? :confused:
that was my Q'
ok let's say i want to search between 2 dates #1.1.2000# to now date what is the closest date to now date
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:03
Joined
Feb 28, 2001
Messages
27,001
OK, here is a way to do that, but you have to decide how you mean "close."

If you have a date field (NOT a date-formatted text field, but a REAL datatype of date), that is stored as what is called a "typecast" - meaning that it is an alternate interpretation of the real format, which in this case is "DOUBLE." A date is the number of days and fractions thereof since the Access reference date of 1-Jan-1900 as day 1. If you take the difference between two dates that are in this format, the units of that difference are in days and fractions thereof. Let us say that you have a target date TGTDATE as a string and a table with a date field [DATEFLD].

Code:
SQLSTRING = "SELECT (DATEFLD - #" & TGTDATE & "#) AS DELTADAYS FROM MYTABLE ORDER BY DELTADAYS;"

The first record of this query would give you the dates in order. Now if you wanted only days BEFORE the target date, do this instead:

Code:
SQLSTRING = "SELECT (DATEFLD - #" & TGTDATE & "#) AS DELTADAYS FROM MYTABLE
WHERE DATEFLD < #" & TGTDATE & "# ORDER BY DELTADAYS;"

Obviously, if you wanted "AFTER" you would have used ">" instead of "<."

Finally, if you wanted the closest date in either direction, do this:

Code:
SQLSTRING = "SELECT ABS(DATEFLD - #" & TGTDATE & "#) AS DELTADAYS FROM MYTABLE ORDER BY DELTADAYS;"

NOTE: If you have dates and times, you might get things within less than 24 hours, so you have to watch out how you format the results. If you only have dates, the differences should always be integers IN EFFECT (but they would still be computed as DOUBLE internally).

Obviously, there is more to it than that. Once you have the string with the SQL, you have to put it somewhere that is useful. Also, if you want ONLY the closest record then (as long as you have an ORDER BY clause) you can use SELECT FIRST 1 to limit what is returned. And you would include more than just the expression. For instance, you would include the FullName, DateFld, transaction number, or other relevant things as part of the field list that follows your SELECT clause.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 13:03
Joined
Sep 21, 2011
Messages
14,052
Max(caredate) < = Date()

Along the lines of
Code:
SELECT Transactions.Description, Max(Transactions.Date) AS MaxOfDate
FROM Transactions
GROUP BY Transactions.Description
HAVING (((Max(Transactions.Date))>#6/1/2016# And (Max(Transactions.Date))<=Date()));

That gets me a description and date for the latest Date in the table which is <= today, but only for those > 1st June 2016

HTH
 

eshai

Registered User.
Local time
Today, 15:03
Joined
Jul 14, 2015
Messages
193
OK, here is a way to do that, but you have to decide how you mean "close."

sorry
The_Doc_Man
but Not really understand you
TGTDATE as a string what value to put "date()" ?
DATEFLD is my date field ? = "caredate"
MYTABLE ? the "caredate" field is inside table called "monynotes"
 

eshai

Registered User.
Local time
Today, 15:03
Joined
Jul 14, 2015
Messages
193
Max(caredate) < = Date()

Along the lines of
Code:
SELECT Transactions.Description, Max(Transactions.Date) AS MaxOfDate
FROM Transactions
GROUP BY Transactions.Description
HAVING (((Max(Transactions.Date))>#6/1/2016# And (Max(Transactions.Date))<=Date()));

That gets me a description and date for the latest Date in the table which is <= today, but only for those > 1st June 2016

HTH

tnx
Gasman

Max(caredate) < = Date() that work
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:03
Joined
Feb 28, 2001
Messages
27,001
Gasman is taking you one way. I'm going a different route. Neither of us is wrong because with Access there are many paths to a correct answer.

Code:
SELECT FIRST 1 CAREDATE, other fields, ( DATE() - CAREDATE) AS DELTADAYS FROM MONYNOTES WHERE CAREDATE <= DATE() ORDER BY DELTADAYS;

You have to fill in what else you wanted to see regarding other fields from that record. Sorry for the confusion about "as a string" because I thought you were selecting based on a date in a text box. But on re-reading I see you meant "from the current date." My error in missing that key point.

NOTE: IF there is more than one record with the same "closest" value as the first record returned, you won't see it. Then the direction that GASMAN is going might be better.

EDIT: I have corrected this thanks to Gasman's observant eyes which are better than my lazy fingers.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 13:03
Joined
Sep 21, 2011
Messages
14,052
Gasman is taking you one way. I'm going a different route. Neither of us is wrong because with Access there are many paths to a correct answer.

Code:
SELECT FIRST 1 CAREDATE, other fields, ( DATE() - CAREDATE) AS DELTADAYS FROM MONYNOTES WHERE CAREDATE <= DATE() ORDER BY MONYNOTES ;

You have to fill in what else you wanted to see regarding other fields from that record. Sorry for the confusion about "as a string" because I thought you were selecting based on a date in a text box. But on re-reading I see you meant "from the current date." My error in missing that key point.

NOTE: IF there is more than one record with the same "closest" value as the first record returned, you won't see it. Then the direction that GASMAN is going might be better.
Doc,

Shouldn't that be ?
Code:
ORDER BY DELTADAYS
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:03
Joined
Feb 28, 2001
Messages
27,001
Whoopsie - that's what I get for shooting from the hip. Absolutely right, Gasman. Sometimes my fingers are in motion but my brain isn't in gear.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:03
Joined
Sep 21, 2011
Messages
14,052
I only mentioned it as a lot of times the o/p just takes what is posted and just pastes it into their code. :(
Just happened to me on another forum where I used generic Category and the field name was General Category. :(
 
Last edited:

Users who are viewing this thread

Top Bottom