[AccessD] sorting query on 'named' collumn and use 'named'collumn in where criteria of a query

Just did a test. If you use a function in the WHERE of the second query then
the function will get called for each record, but only for records which
satisfy your other criteria. If the function appears again in the ORDER BY
it gets called again but only for records which have been included by the
WHERE.

You can check this out yourself by putting a Debug.Print in the function
--
Andy Lacey
http://www.minstersystems.co.uk



--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] sorting query on 'named' collumn and use
'named'collumn in where criteria of a query
Date: 18/06/04 13:07

>
> Which query will run first, the source (sub)query with the functions (and
no
> WHERE statement, so no filtering) or the main query with select and
> filtering?
> Because if the query with the calculatins runs first the gain in speed
maybe
> will be little, because then the calculations (functions) will run for all
> records...
>
> Bert-Jan
>
> ----- Original Message -----
> From: "Andy Lacey" <andy at minstersystems.co.uk>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Friday, June 18, 2004 10:46 AM
> Subject: Re: [AccessD] sorting query on 'named' collumn and use
> 'named'collumn in where criteria of a query
>
>
> > Hi Bert-Jan
> > No you can't, but what you can do is write a SELECT query which gets
you
> the
> > fields you want plus the calculated values, then make this the source
of
> > your existing query rather than the original table. Because the
calculated
> > values are already in the source you can then refer to them as many
times
> as
> > you like.
> > --
> > Andy Lacey
> > http://www.minstersystems.co.uk
> >
> >
> >
> > --------- Original Message --------
> > From: Access Developers discussion and problem solving
> > <accessd at databaseadvisors.com>
> > To: accessd at databaseadvisors.com <accessd at databaseadvisors.com>
> > Subject: [AccessD] sorting query on 'named' collumn and use 'named'
> collumn
> > in where criteria of a query
> > Date: 18/06/04 07:56
> >
> > >
> > > Dear list,
> > >
> > > in qeuries i use a lot of functions to format a value, or to get
> > > 'calculated' values, these functions get 'named' collumns (like
.... AS
> > > clientnumber). This works fine.
> > > However when i want to use this named collumn in the WHERE -
statement
> or
> > in
> > > the ORDER BY statement and i use the collumn name this doesn't
work. Ik
> i
> > > use the same function again in the WHERE and ORDER BY statement
this
> works
> > > fine, but it slows down the qeury, because the function has to
be
> executed
> > > more times.
> > > Can i use the named collums in the WHERE and ORDER BY statement
and if
> so
> > > how do i do that?
> > >
> > > I am working with A2k on WINXP and WIN2kProffesional
> > >
> > > Thanks.
> > >
> > > Bert-Jan
> > >
> > > --
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > >
> > >
> > >
> > >
> >
> > ________________________________________________
> > Message sent using UebiMiau 2.7.2
> >
> > --
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>
>

________________________________________________
Message sent using UebiMiau 2.7.2