[AccessD] Simulating ROW_NUMBER in access

[Ryan W]

*scratches head*. I could have sworn I tried that first thing... maybe I
was trying to concatenate them.

Thanks!



On Thu, Apr 13, 2023 at 2:48 PM Gustav Brock via AccessD <
accessd at databaseadvisors.com> wrote:

> 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).
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>