Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Built-in Functions _ Dmax Returns A Wrong Value

Posted by: Kamulegeya Aug 21 2019, 05:26 AM

Hello members.

I have data in a query as shown in the screen shot.

The code below returns 9 yet i expected 19.

CODE
?DMax("myNumber", "qry_get_num", "PeriodID=38 and TransTypeID=3")
9


Any idea why?

Ronald .

 

Posted by: Phil_cattivocarattere Aug 21 2019, 05:35 AM

What kind of field is myNumber? is it a calculated field? what is its data type? text or number?

Posted by: jleach Aug 21 2019, 06:21 AM

Yea, it looks like MyNumber is a string, and therefore is being sorted via "natural sort" (alphabetic), where the largest single digit comes first.

Posted by: Kamulegeya Aug 21 2019, 06:27 AM

Here is the SQL (it is a pass through query).

CODE
select t.reference,
t.PeriodID,t.TransTypeID, t.TransID,dense_rank() over(partition by PeriodID,t.TransTypeID order by TransID) as myNumber


from tblAdvancesTransactions as t

Posted by: PhilS Aug 21 2019, 06:42 AM

The return type of dense_rank is a BigInt. Access (except for the versions of Access 2016/2019 released within the last 18 months) cannot handle this data type and converts it to text.
Try to explicitly convert it to Int in your query.

Posted by: Kamulegeya Aug 21 2019, 07:16 AM

Yes!

CODE
select t.reference,
t.PeriodID,t.TransTypeID, t.TransID,cast(dense_rank() over(partition by PeriodID,t.TransTypeID order by TransID) as Int)  as myNumber


from tblAdvancesTransactions as t


I get correct Results

uarulez2.gif