[AccessD] Simulating ROW_NUMBER in access

[Gustav Brock]

Hi Ryan

You should only have to add the fields – and converting back and forth and then to Double does nothing to the basic values as date and time numerically are Double:

 .....  subquery.sample_date + subquery.sample_time
<= outerquery.sample_date + outerquery.sample_time


If you need a speedy method, study my RowNumber function at:

https://github.com/GustavBrock/VBA.RowNumbers

/gustav


Fra: Ryan W<mailto:wrwehler at gmail.com>
Sendt: 13. april 2023 21:39
Til: Access Developers discussion and problem solving<mailto:accessd at databaseadvisors.com>
Emne: Re: [AccessD] Simulating ROW_NUMBER in access

I was able to slim it down a litte:
 .....  CDBL(CDATE(CSTR(subquery.sample_date & " " &
subquery.sample_time))) <= CDBL(CDATE(CSTR(outerquery.sample_date & " " &
outerquery.sample_time)))


It's still ugly as sin.







On Thu, Apr 13, 2023 at 1:20 PM Ryan W <wrwehler at gmail.com> wrote:

> I got it... it's ugly but it works.
>
> WHERE outerquery.ID = subquery.id AND  and
> CDBL(cdate(subquery.sample_date))+cdbl(cdate(cstr( subquery .sample_time)))
> <=
> CDBL(cdate(outerquery.sample_date))+cdbl(cdate(cstr(outerquery.sample_time)))
>
> For whatever reason even though sample_date and sample_time are varchar, I
> have to cast them to string so I can cast them to a date to convert to a
> double.. heh
>
>
> On Thu, Apr 13, 2023 at 12:10 PM Ryan W <wrwehler at gmail.com> wrote:
>
>> Whoops, I sent the wrong dang fiddle URL:
>>
>> https://dbfiddle.uk/68MqShXH
>>
>> The one in the original email is where I made a new date/time field,
>> which worked but I can't include that in the exported data, so I'm trying
>> to composite the sample_date and sample_time fields to get the right
>> ROW_NUMBER (or maybe it's more of a rank/dense rank situation).