dlast function (urgent support)

[okotan]

Hello all

i want to get numerical value from table by dlast function to query

But the number is 870,04 in the table however the number is 870,00 in the query

i didnt understand, why couldnt get ,04 value to query

The Function as below;

Val(IIf([Period]=0 Or [Period]<0;0;DLast("[BIRIKMIS_AMORTISMAN]";"HAREKET";"DEMIRBAS_NO=" & [DEMIRBAS_NO])))

thanks

 

[pbaldy]

That function often won't return what you expect:

http://support.microsoft.com/kb/208190

Does DMax() pull what you want?

 

[The_Doc_Man]

The problem is that DLast gives you the last record of your subset of records, but tables don't store their records in an obvious way. More specifically, in the absence of a query with an ORDER BY component, there is no telling WHAT is in the last record in a table. Particularly if the field in question isn't the Prime Key, the order of appearance of any given record will ALMOST seem random to a casual observer.

Further, I notice that you are using the VAL function. That implies that you want a number from a text field. Text fields and numeric fields don't sort in the same exact way so we need to know if there are ever any extraneous alphabetic characters in the field you are referencing. That would ALSO affect the sort order even if you DID have an ORDER BY clause somewhere in the mix.

 

[Galaxiom]

The problem is that DLast gives you the last record of your subset of records, but tables don't store their records in an obvious way. More specifically, in the absence of a query with an ORDER BY component, there is no telling WHAT is in the last record in a table.

In my experience, even if the source records are sorted in a query, the Last() function (and presumably DLast() too) does not return the "last" record. I found that Last() applied to a DESC order returned a different record from First() applied to ASC order.

First(), Last(), DFirst() and DLast() should only be used if you don't care which record is returned. This might sound pointless but there are situations where it is useful.

For example, the Lookup field when the query is already Grouped on the value represented by the Lookup. Every record in the group would return the same value for the lookup. Grouping on that lookup would tell the engine to waste time comparing the lookup values. So use First() to get the first one that it finds.

 

[sneuberg]

In case you didn't look this site https://support.microsoft.com/en-us...-and-last-functions-return-unexpected-records that pbaldy provided has a few methods of resolving this problem.

 

[Galaxiom]

In case you didn't look this site https://support.microsoft.com/en-us...-and-last-functions-return-unexpected-records that pbaldy provided has a few methods of resolving this problem.

Hadn't come across that one and this is what they say there:

The First(), Last(), DFirst(), and DLast() functions ignore sort orders, indexes, and primary keys. These functions return the first or last undeleted record based on the order in which the records were entered into the table, not the first or last record in a specified sort order.

The First or Last record entered? I doubt it be relied on after a Compact.

Last time I looked (several years ago) I found this page from Microsoft.

You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field.

I thought it unlikely that it would get a truly random record. Moreover what would be the difference between First() and Last()?

Seems Microsoft isn't even sure what these functions do.

 

[The_Doc_Man]

Galaxiom, as you looked up a couple of sites, I looked up other things as well. Your point about DFirst, First, DLast, and Last is well taken. The order of record appearance in a table is not truly random - but it is not predictable and therefore is EFFECTIVELY random for a large enough table. After a Compact&Repair, record order in a table that has had insertions and deletions is not likely to be the same as it was before the C&R but the actual order will be hard to predict.

However, DLast is a domain aggregate, not an SQL aggregate. If the domain is a query with an ORDER BY clause, how would it ignore the order of presentation? To my limited memory, the recordset is created for a query BEFORE you present it to its requester. And the plot gets thicker when the query involves a JOIN.

okotan, the solution to your problem is to not use DFirst or DLast, but to instead use DMin or DMax functions as appropriate. You can still filter them with a criterion clause to assure that you are focusing on a particular DEMIRBAS_NU value. Unless, of course, you are looking at something based on time and were assuming that DLast would always give you the last record. Unfortunately, in a table with creations and deletions, that is not always a good assumption.

 

[okotan]

problem was solved when i remove val() function.

thank you very much