Re: Dmax for nearest date

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

No one is suggesting that you change the table, but I do
need to understand the datatype of the fields that we're
using.

Based on the lack of detailed information in #Error, we're
just shooting in the dark about what problem needs to be
solved. You might be able to get more information if you
run the DMax/Dmin from the Debug window instead of in a text
box expression.
--
Marsh
MVP [MS Access]


Dan @BCBS <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
The only way I know to change the data type is in the Table.
I have abour 8 years of data in this table (SQL Table).

Is there another way to change the data type???
Or do I need to change the table?


"Marshall Barton" wrote:
The format is irrelevant, it's the data type that's
important.

DMin or DMax, you will still have exactly the same syntax
and data type issues.


Dan @BCBS <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
The format for Due Date in the query is "Short Date"

I've reposted my question because I tried Dmin and I think it's closier to
my goal.

Subject "Dmin for nearest date"


"Marshall Barton" wrote:
Since the due data field is calculated in the query, you do
need to use that instead of a table.

=DMax("DueDate","q_Inventory","DueDate < = Date()")

is legal as long the name of the calculated field is
DueDate. Also make sure it's a Date type field. If it's a
Text field that looks like a date, then change the query to
convert it by using the CDate function.


Dan @BCBS <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

That returns #Error in the text field..

I'm trying to identify the nearest date (DueDate) to today.
The query that the report is created from does a date calculation.
Then on the report I need to show the nearest date.

Per your suggestion: I plugged this into a text box:
=DMax("DueDate","tblTrackingData","DueDate < = Date()")

I also tried replacing the table name with the Query names since that is
where the due date is calculated:
=DMax("DueDate","q_Inventory","DueDate < = Date()")

Both ways return #Error in the text box...


Dan @BCBS <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Trying to use DMAX to get the nearest date.

I have a long code that returns the date when something is due.
Example: Joe case 1 = due date 6/1/07, Joe case 2 = due date 6/5/07
Returns 6/1/07...

I need to show the nearest due date for each person.
So in a text box I coded "=DMax([DueDate] < = Date())"


"Marshall Barton" wrote:
Not at all sure I understand what you are doing, or even
what you are trying to accomplish, but a legal way to use
DMax would be:

=DMax("DueDate", "thetable", "DueDate < = Date()")
.

Relevant Pages

  • Select row by ID to nearest date, future (or past if no future dat
    ... I've had no problem building a task report showing all tasks, ... task closest to today's date from which the end user can prioritize their ... What I'm trying to do is build a query that will show ONLY one task per open ... Once I get the query to select only one row for each case by the nearest ...
    (microsoft.public.access.queries)
  • Int rounding down (calculated) whole numbers
    ... I have a function during which a line passes a calculation to an integer, ... A function is required to round times to the nearest x mins (this element ... ' * mins past is used to round to nearest mins past the hour ... dteHighTime = dteHighTime - dblStdMin ...
    (microsoft.public.access.modulesdaovba)
  • Re: Damn you, FEDEX! or Nikon D40 lost in Springfield, MO blackhole.
    ... the 2 mp Mavica he had been using with a Nikon D40. ... After shopping around, he got me to order one for him. ... The shipper had it insured, but from what I have read it could take weeks to sort this crap out. ... You may get your insurance from FedEx and a couple weeks later they find it and deliver it. ...
    (alt.photography)
  • Re: nearestNeighbor
    ... If I look at the 3 index values returned from DTby pointLocation ... > nearestNeighbor returns the index of the nearest point in DT.X for each> query point location in QX. ... So I assume you're using DelaunayTri? ...
    (comp.soft-sys.matlab)
  • Re: Re: Select a canvas line "point"
    ... After some testing I implemented a simple "nearest" calculation using ... the hypoth() function point by point, ... platforms and it is not ensured, that this binary extension is always ...
    (comp.lang.tcl)