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
> Dmax Returns A Wrong Value, Any Version    
 
   
Kamulegeya
post Aug 21 2019, 05:26 AM
Post#1



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


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 .
Attached File(s)
Attached File  dmax.png ( 107.22K )Number of downloads: 15
 
Go to the top of the page
 
Phil_cattivocara...
post Aug 21 2019, 05:35 AM
Post#2



Posts: 340
Joined: 2-April 18



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

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
jleach
post Aug 21 2019, 06:21 AM
Post#3


UtterAccess Editor
Posts: 10,085
Joined: 7-December 09
From: St Augustine, FL


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

--------------------
Go to the top of the page
 
Kamulegeya
post Aug 21 2019, 06:27 AM
Post#4



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


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
Go to the top of the page
 
PhilS
post Aug 21 2019, 06:42 AM
Post#5



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


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.

--------------------
Go to the top of the page
 
Kamulegeya
post Aug 21 2019, 07:16 AM
Post#6



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


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
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 01:58 AM