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()" ?
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.
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
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"
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
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.
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.
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.