Solved Time Last Modified using Dmax (1 Viewer)

[ChrisMore]

Hi all,

I have created a union query (I have used a union query because my order form has multiple subforms) and I am then trying to use DMax in the order form to show the last modified date and time of the order. As shown in the attached image, I am trying to display the time highlighted in red as it's the most recent time according to the date modified.

I have used the below but all it is doing is displaying the maximum time and doesn't consider the date. So currently the result I am getting is 16:33:08.

=DMax("Time_Order_Last_Modified","UnionTablesTime","[Order_Number] = '" & [Order_Number] & "' AND DMax(""[Date_Order_Last_Modified]"",""UnionTablesTime"",""Order_Number='" & [Order_Number] & "'"")")

How can I get it to work so it shows the maximum time for the maximum date?

Thanks for your help,
Chris

 

[Minty]

It's a shame your fields are split as time and date, this would be a breeze if it was stored as a single field.
Is that by design or just inherited?
Create a calculated field OrderTime + OrderDate and Dmax that.

Note the use of the "+ " to add the values together.

 

[theDBguy]

Hi. Just a guess, but maybe you need to delimit your date criteria.

Sent from phone...

 

[ChrisMore]

It's a shame your fields are split as time and date, this would be a breeze if it was stored as a single field.
Is that by design or just inherited?
Create a calculated field OrderTime + OrderDate and Dmax that.

Note the use of the "+ " to add the values together.

Hi Minty,

Thanks for your suggestion. I have created a new query called UnionTablesTime_Query and added the fields together to form a field called 'Time & Date'.

This is the syntax I have used but I am now getting an error.

=DMax("Time & Date","UnionTablesTime_Query","[Order_Number] = '" & [Order_Number] & "'")

Have I done something wrong?

 

[ChrisMore]

Hi. Just a guess, but maybe you need to delimit your date criteria.

Sent from phone...

I thought that too but I couldn't figure out where to put the # as I am using the second DMax as the criteria for the first so the # will then be in the expression of the second DMax syntax.

 

[Minty]

Get rid of the "&" - just call the field OrderDT without the spaces.

Spaces and special characters cause all sorts of problems, you will probably need to use square brackets if you stick with it. Just avoid at all costs.

=DMax("[Time & Date]","UnionTablesTime_Query","[Order_Number] = '" & [Order_Number] & "'")

 

[ChrisMore]

Get rid of the "&" - just call the field OrderDT without the spaces.

Spaces and special characters cause all sorts of problems, you will probably need to use square brackets if you stick with it. Just avoid at all costs.

=DMax("[Time & Date]","UnionTablesTime_Query","[Order_Number] = '" & [Order_Number] & "'")

Thanks Minty, that was a breeze! All working great now.

Rookie error from me there adding a special character! I just removed it and renamed the field something sensible.

Cheers!

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.