Dmax, pulling a latest record based on Date field

[adi2011]

Hello,

I have query in my database (db) which contains fild "LatestPrice".


This field must always pull latest price based on field "DateOfEntry".
"DateOfEntry" is Date/Time field which has default value "Now()" in table "TRAFFIC".

This is how formula looks like in my query

LatestPriceLookUp("[Price]","TRAFFIC","[DateOfEntry]=#" & DMax("[DateOfEntry]","TRAFFIC","[ID_Article] like " & "'" & [ID_Article] & "'") & "#")

It works great, except that it CAN NOT pull any data in case there is more then one record in table "TRAFFIC" with SAME date. So in table "TRAFFIC" I have
these two records in field "DateOfEntry" for one Article

1. 11/10/2015 12:08:27 PM
2. 11/10/2015 4:25:39 PM

and result of price is Null. I need to change criteria for DMax in these cases, so I could pull the newer record. In this case, it is a second one (11/10/2015 *4:25:39* PM).

Is that even possible?

Any help is appreciated and many thanks in advance.
Adi

[CJ_London]

think you have reached the limitations of using domain functions and start to learn use subqueries etc.

try

LatestPrice: SELECT TOP 1 Price FROM traffic as T where T.id_article=mainquery.id_article order by t.dateofentry desc

change the bit in red for the name of your table this refers to per your domain function

[RuralGuy]

I would think it would work. What value is being returned with your DMAX() function?

[June7]

The date may be same but because of time component the values are not the same. The DMax should return the 4 PM date/time.

Using LIKE operator without * wildcard might as well just use = sign.

Is ID_Article a text or number type? If number, remove the apostrophe delimiters.

Yes, domain aggregates should work but subquery might be faster.

[RuralGuy]

DMax *may* just think that 11/10/2015 12:08:27 PM is later than 11/10/2015 4:25:39 PM even though it shouldn't. maybe we chould try CDbl([DateOfEntry]) or something like it. I would really be curious what the DMax is currently returning.

[June7]

No, 12:08:27 PM is just past noon. 4:25:39 PM is later. Access knows this, as long as the values are actually date/time type.

[adi2011]

Hi,

thank you all very much for helpful replies and ideas.

After long testing my DB I found that DMax example (expression) I use:

LatestPriceLookUp("[Price]","TRAFFIC","[DateOfEntry]=#" & DMax("[DateOfEntry]","TRAFFIC","[ID_Article] like " & "'" & [ID_Article] & "'") & "#")

works perfect ONLY if I set "special" short date format in Regional settings of Win OS. Please see screen bellow

http://www.madijelovi.com/2016-01-10_10-36-50.jpg

Can anyone explain me this? It is awkward that I will have to do this in order for my expression to work. However, I would like to keep my expression since I use to it for long time.

Thanks in advance for help.
Adi

[CJ_London]

dates are actually decimal numbers - the format you see 1/1/2016, 1st January 2016, 13:00:00, 1PM etc are all just text views of that number.

the value before the decimal point represents the date and the value after the dp the time.

so for today, the number is 42379 and tomorrow 42380 etc

and now is 12:34 which is represented as a fraction of the number of seconds in a day - (in a day, 60 secs x 60 mins x 24 hrs) = 86400 and now (60 secs x 60 mins x 12 hrs)+(34 mins x 60 secs)=45240 sec so 45240/86400=0.523888

the # used with text dates converts them in sql back to a number. So my guess is you have a date 'number' which you are converting to text and then converting back to a number and something has been lost in transalation

[June7]

Maybe this will help http://allenbrowne.com/ser-36.html

The DMax works for me but I my date values are in U.S./Access standard and my system is using U.S./Access standard. Access expects 1/1/2016 to be Jan 1, 2016 not 1 Jan 2016.