Getting last value that is not null

[sheusz]

Hi Arvil



I've tried this and it works OK, except it does not show the matching result for the MachineNo on the current form, it is actually returning the highest value of HourMtr in the xService table. I need it to show the highest value for the HourMtr based on the MachineNo displayed on the current form (Me.MachineNo)?

[ArviLaanemets]

Hi Arvil

I've tried this and it works OK, except it does not show the matching result for the MachineNo on the current form, it is actually returning the highest value of HourMtr in the xService table. I need it to show the highest value for the HourMtr based on the MachineNo displayed on the current form (Me.MachineNo)?

I used the part of formula in your post#1 as starting point.

Maybe

DMax("[HourMtr]","xServices","[MachineNo]='" & Me.MachineNo & "'  AND HourMtr >0")

[sheusz]

Hi Arvil
Thanks for that. I'm getting the #Name? error in the text box. The on the form the MachineNo is displayed in a text box that is bound to the field MachineNo in table. Could this be the problem?

[ArviLaanemets]

DMax("[HourMtr]", "xServices", "[MachineNo] = '" & Forms!YourFormName!YourControlName & "'"  AND [HourMtr] >0")

Btw. I advice to rename all controls in your forms, as Access (and you too) can really get confused otherwise. E.g.:
table field: FieldName;
linked text box: txtFieldName;
linked combo box: cbbFieldName;
label for linked control: lblFieldName;
etc.

Also, name all forms like fFormName
When you add another form fFormName2 to form fFormname1 as subform, Access by default names the subform control which contains the form fFormname2 with same name (i.e. fFormname2). I always immediately rename the subform control as sfFormname2, to avoid further confusion. NB! The subform control and the form displayed in subform are different objects, with entirely different set of properties. When you click on unselected subform once, the subform control will be activated. When you click on it again, the form contained in subform will be activated

[sheusz]

Hi ArviL

Thank you again for your explanations and recommendations. There is a lot of work in implementing your ideas, but I will get to it.

I have noticed that the MachineNo field in the xServices table actually has a space in it. Hence the space in the code below. (yet something else to clean up)

I've adapted the code you suggested, but get an error when trying to save it. The expression you entered contains an invalid string

=DMax("[HourMtr]", "xServices", "[Machine No] = '" & Forms!frmMachineComponents!txtMachineNo & "'"  AND [HourMtr] >0")

I really do appreciate your help.

[ArviLaanemets]

[QUOTE=sheusz;515192]The expression you entered contains an invalid string[/code]
Instead
'" & Forms!frmMachineComponents!txtMachineNo & "'" AND [HourMtr] >0")
must be
'" & Forms!frmMachineComponents!txtMachineNo & "' AND [HourMtr] >0")
or
'" & Forms!frmMachineComponents!txtMachineNo & "'" & " AND [HourMtr] >0")

Without DB I can't test it, so it all is on fly!

[Welshgasman]

=DMax("[HourMtr]", "xServices", "[Machine No] = '" & Forms!frmMachineComponents!txtMachineNo & "'  AND [HourMtr] > 0")

?

[sheusz]

Hello AvriL & Welshgasman

Thank you for your replies. They both have given me the result I was after

As always I've spotted a couple of issues, not related to this problem, but things that need to be addressed "on the ground". Turns out that the HourMtr can be reset to a lower value than one preceding the last value recorded. (It's a firmware bug on the machines)

e.g (in descending order of reading dd/mm/yy)

01/08/23 500
20/07/23 0
18/07/23 900
15/05/23 1200

The code you have provided gives the highest value for HourMtr, which I think is the best way to go for now. But down the track I think I'll have to code for the highest value, then total all of the values after the highest reading was taken, and add them to the highest reading, to give true data. I think I can handle that.

[ArviLaanemets]

To get a latest non-zero HourMtr value for MachineNo, you can use DMAX() to get max date for this MachineNo with HourMTR > 0, and then use this found date to get HourMtr for this MachineNo at max date using DLOOKUP(). This will work so long as you never have several different HourMtr values for same MachineNo at same date.

[sheusz]

Thanks AvriL That is a great help.