Getting last value that is not null

[sheusz]

Hello brains trust



Seeking a little help here.

I use the code below to get the last value from a table (xServices) to display data (HourMtr) in a text box on a form. It works great except in the case where the last record may have no value entered in the HourMtr field. In these instances it shows nothing, as you would expect.

What I would like is for the last value in the table that is not empty (null) to be displayed, which might be some records back from the last record in the table.

=DLookUp("[HourMtr]","xServices","[ServiceNo] =" & Nz(DMax("[ServiceNo]","xServices","[MachineNo]='" & [MachineNo] & "'"),0))

How do I go about getting the result I am after?

Thank you in advance.

[Welshgasman]

So add the criteria of HourMtr not being Null or a ZLS in your DMax.
That however is getting the max service no, so unless that is incremented each time, that would not be the case.

[orange]

I agree with Paul's suggestion.
Please tell us what LAST means in your request.
Last ----latest date/time
Last ----physical position in an ordered list

It might also help readers if you supplied
-some sample data from xServices
-the design of xServices showing datatype and Required or Not

[xps35]

Create a query to select all not Null records and apply the DLookup to the query.

[Micron]

Agree with Orange. I think you will need a subquery, however I'm not certain if you can use that directly as the source for an unbound textbox. I did that as an experiment and while the sql was accepted the result is ?Name error. I imagine you could return a record in code though, and pass it to the value property of the textbox.

http://allenbrowne.com/subquery-01.html

[CJ_London]

You can use a rowsource in a listbox or combo to display the data

[sheusz]

Hi all

Thank you for your replies. Sorry that I didn't make myself clear. Below is a screenshot showing what I mean. This is not the actual table, but demonstrates what I'm after. I want to return the value of HourMtr in the highlighted field.

[xps35]

So it is not about being not null, but not being not zero. Makes no different. Make a query to select the non zero records and apply that to your DLookup.

[Micron]

I have to ask, since some past values are 0 and service date is not in chronological order then what happens if someone puts in a lower value in a field at a later date? Like in record ID 4 they put 50 in HourMtr field. Do you really want last (id 6 = 50) or do you want the max (id 5 = 63)?

[sheusz]

Hi Micron

The table is in chronological order (Australian format DD/MM/YY) and the HourMtr reading can never be less than a previous reading (as in a vehicle odometer). The data is imported in to the table from a spreadsheet that comes from the device. In some cases there will be a 0 value in the HourMtr and Odometer fields depending upon the way the data was exported from the device.

I'll try using the approach suggested by Peter and Micron and use a subquery.

[Micron]

OK, I think the dd/mm/yyyy makes all the difference. I interpreted those dates as mm/dd/yyyy so I saw the possibility of records with larger values being before records with lesser ones.

[sheusz]

Thank you all for your insights.

As suggested I've created a query that will give me the result I'm after, but I don't know how to get the result in to the text box on the form.


This is the code for the query. (Yes, I'm aware of the spaces in names convention - this is a legacy project)

SELECT Customers.CustomerID, Customers.CompanyName, [Customer Machine Components].MachineNo, xServices.[Service Date], xServices.[Service Report No], xServices.Odometer, xServices.HourMtr
FROM (Customers INNER JOIN [Customer Machine Components] ON Customers.CustomerID = [Customer Machine Components].CustomerID) INNER JOIN xServices ON Customers.CustomerID = xServices.CustomerID
WHERE (((xServices.Odometer)>0))
ORDER BY xServices.[Service Date] DESC;

[ArviLaanemets]

What about using

DMax("[ServiceNo]","xServices","[MachineNo]='" & [MachineNo] & "'  AND HourMtr >0")
or
DMax("[ServiceNo]","xServices","[MachineNo]='" & [MachineNo] & "'  AND Nz(HourMtr,0) >0")

[CJ_London]

The table is in chronological order

Appreciate you are not using first/last but be aware you simply cannot rely on that for first/last in a query unless you apply the order in a query - the order in the table is just a view applied when you open the table to view. Data is stored randomly - what you see is not necessarily what you get. It may be OK initially but as records are added/deleted/updated it will become apparent. For domain functions, you do not have the luxury of being able to specify an order.

[sheusz]

Appreciate you are not using first/last but be aware you simply cannot rely on that for first/last in a query unless you apply the order in a query - the order in the table is just a view applied when you open the table to view. Data is stored randomly - what you see is not necessarily what you get. It may be OK initially but as records are added/deleted/updated it will become apparent. For domain functions, you do not have the luxury of being able to specify an order.

Hi CJ, Yes, I am aware of this.