DMAX Problem

[Shamli]

Hello,


I am trying to query data from the table with max numeric value of a field.

Dmax is working fine for a set of records and its filtering the one with the highest numeric value.
But there are cases where there are only one record in the table.
Its not showing that one record.
Example:
TableName: Order

RevisedNumber	Name	Weekending
1	Adam	2/1/2019
2	Pat	2/1/2019
3	Fiet	2/1/2019

In this case:
Dmax("[RevisedNumber]", "Order")
Works
Displays the record
3 Fiet 2/1/2019


But in this case

RevisedNumber	Name	Weekending
1	Julio	2/8/2019

DMAX functions doesn't work
It shows no record.

I am not sure because there is only one record with that criteria.
Can anyone help me to solve this.

[ranman256]

Because MAX with 3 field gives you the max w 3 variables....not like you think.

you will need 2 queries:
Q1 (only 2 fields): max(Date) , Rev#
select Max(OrderDate), Rev# from table


then Q2 : join Q1 to the data table (on ID) to pull ALL the data you want,
select tbl.Name, Q1.MaxDate, Q1.Rev# from Q1, tbl where Q1.Rev#=tbl.Rev#

[Shamli]

Sorry for not making it clear.
This is my scenario:

ID	RevisedNumber	Name	Weekending	Order
1201	1	Adam	2/1/2019	20
1202	2	Adam	2/1/2019	25
1203	3	Adam	2/1/2019	30

when I do Dmax("[RevisedNumber]","Order")
Answer:

1203

3

Adam

2/1/2019

30

But when this is the scenario:

ID	RevisedNumber	Name	Weekending	Order
1201	1	Adam	2/15/2019	40

where there is only one record for that weekending.
I am getting null.
And sorry for the confusion.

[ranman256]

I don't see how,
DMAX() for REV is 1 for ADAM including the Date

DMAX() for REV is 3 for ADAM (excluding date)

hence the 2 query system. What do you want MAX of? Rev or Date?

[Micron]

What do you want MAX of? Rev or Date?

Looks like RevisedNumber as per the DMax expression sample: Dmax("[RevisedNumber]", "Order")
In testing, it works on a single table record for me. Are you sure the expression is exactly the same in all cases? Have you left out the criteria part of the expression in your post but you are using it in your db?