UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlast Irregularity, Access 2016    
 
   
damonpc
post Sep 5 2019, 07:50 PM
Post#1



Posts: 161
Joined: 18-December 04
From: Launceston, Tasmania


Hi I am working on someone elses code that doesn't appear to work. They are using DLast to find the appropriate rate (WoolTaxRate2) in the table LevyRates) based on the date of a transaction. I will show the code below as it is not in the attached. What appears to be happening is that when the transaction date is 06/09/2019 it is picking up the rate of 0.02 and not 0.015 as shown in the table.

I have read that DLast can be tricky but i would have thought that this would be straightforward. I have spent a fair while on this and got nowhere hence the post.


CODE
DLast("WoolTaxRate2", "LevyRates", "StartDate <= #" & CStr(forms!form1!TransDate) & "#")



Any help greatly appreciated.

Damon Cornelius
Go to the top of the page
 
DanielPineault
post Sep 5 2019, 08:09 PM
Post#2


UtterAccess VIP
Posts: 6,845
Joined: 30-June 11



I'm wondering if the issue isn't related to the date itself not being treated properly. Perhaps try something along the lines of:

CODE
DLast("WoolTaxRate2", "LevyRates", "StartDate <= " & Format$(Forms!form1!TransDate, "\#mm\/dd\/yyyy\#"))


What date does the value of 0.02 correspond to?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
damonpc
post Sep 5 2019, 08:27 PM
Post#3



Posts: 161
Joined: 18-December 04
From: Launceston, Tasmania


Thanks Daniel, that seems to work. The value of, in this instance, 0.015 is passed back as a percentage charge in the database from which this is an extract.

As this DB was built some time ago it maybe the upgrade to Office 365 that has caused the issue.

Will test and pass on good news to my colleague.
Go to the top of the page
 
arnelgp
post Sep 6 2019, 02:18 AM
Post#4



Posts: 1,286
Joined: 2-April 09
From: somewhere out there...


perhaps you should consider using DMax() instead of DLast().
if transactions are entered at random date, it might pick
the wrong value.

sample data:

Forms!Form1!TransDate = 02-sep

TransDate
01-sep
30-aug
02-sep
31-aug

using Dlast will get data from 31-aug (<=forms!form1!transadate).
using DMax will get data from 02-sep.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
PhilS
post Sep 6 2019, 09:27 AM
Post#5



Posts: 616
Joined: 26-May 15
From: The middle of Germany


QUOTE
I have read that DLast can be tricky but i would have thought that this would be straightforward.

DLast (and DFirst) are not tricky; they are dead simple. - However, they don't (and cannot) work as their names suggest.
DLast and DFirst return random values!

For some background info on the matter you might want to read DFirst/DLast and the Myth of the Sorted Result Set. - TL;DR: Use DMin/DMax instead.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Sep 6 2019, 09:32 AM
Post#6


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


I did a short YouTube video on the same topic, using Max() and Min() vs First() and Last() in SQL. The same problems appear with DFirst and DLast.

Don't forget that there is no inherent sort order in tables. Therefore, the results you get from the First and Last functions depend entirely on whether a sort order has been applied to the recordset, and if so, what that sort order happens to be.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
DanielPineault
post Sep 6 2019, 09:57 AM
Post#7


UtterAccess VIP
Posts: 6,845
Joined: 30-June 11



Best to create a custom query to guarantee the sorting and filter and retrieve the value as required.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
PhilS
post Sep 6 2019, 11:15 AM
Post#8



Posts: 616
Joined: 26-May 15
From: The middle of Germany


QUOTE
Therefore, the results you get from the First and Last functions depend entirely on whether a sort order has been applied to the recordset, and if so, what that sort order happens to be.

Well, this is correct. However, it creates the impression the result of First and Last could be controlled. In some situations this is not true, unless you take the internal workings of the database engine into account.
So, I would recommend to rather propagate the official wording:
QUOTE
You can use the DFirst function to return a random record from a particular field in a table or query when you need any value from that field.

(Quoted from Application.DFirst method (Access), emphasis added by me)

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 09:59 AM