Dmax for nearest date

[Dan @BCBS]

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

But I think I'm way off... Any suggestions..

 

[Marshall Barton]

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

But I think I'm way off... Any suggestions..

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

 

[Dan @BCBS]

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...

Suggestions??

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

But I think I'm way off... Any suggestions..

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

 

[Marshall Barton]

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.
--
Marsh
MVP [MS Access]

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...

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

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

 

[Dan @BCBS]

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"

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.
--
Marsh
MVP [MS Access]

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...

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

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

 

[Marshall Barton]

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.
--
Marsh
MVP [MS Access]

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"

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.

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...


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


:
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()")

 

[Dan @BCBS]

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?

Thanks
Dan

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.
--
Marsh
MVP [MS Access]

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"

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.



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...


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


:
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()")

 

[Marshall Barton]

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]

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?

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.

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"


:
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.



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...


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


:
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()")