microsoft access DLOOKUP with MAX function

Question

i have following table in access and i want that when the user click on the next record it should show the max value of digital_num based on the type for example:

when the form show the column type = "Petrol" another textbox that i added in the name of "text14" which has dlookup with max function it should show the value 200 in there not the 100 because i added max function.

ID   Type    Digital_Num
1    Petrol   100  
2    Gas      50
3    Supper   150
4    Petrol   200
5    Gas      50
6    Supper   200

i added a textbox in the form in the name of "text14" besides the textboxes which automatically created for ID, Type, Digital_Num.

and i added the below Dlookup code to its control source

=DLookUp(Max("[Digital_Num]"),"table","[type] =" & '[Form].[type]')

when i switch my form to layout view it keeps showing 200 number in the text14 textbox which is the maximum value in the table it does not change even i press the next record

however it should show the max value of that type based when i click on the next record.

but @HarassedDad, when i change the type textbox to a combo box and changed and added your code "=DMax("[Digital_Num]","table","[Type]=" & [Forms]![f_form]![Combo28])", it through an error, actually what i want is if the end user wants to enter a new record in system and select a type from the list there should be a textbox that should search the table for the maximum digit entered in system yesterday based on the selected type. pls check the snapshot in this link drive.google.com/file/d/1MTRq1BenI76JWK7R8lOdc3oXt1gifkpo/… — Mellad Qarizada
Sorry can't see pictures - but which field is bound to combo28, is it actually returning the [type] or another column? If type is text then you'd need to wrap it in single quotes ,"[Type]= '" & form!type & "'") – — Harassed Dad

Cahaba Data Cahaba Data · Accepted Answer · 2018-12-01T19:06:06

it may be more suitable to create an Aggregate Query to find the Max. Then do the LookUp of that query.

this is easier to test/debug then nesting the lookup and the max functionality in the same task

microsoft access DLOOKUP with MAX function

1 Answers