[AccessD] Name Flipping

[Bill Benson]

Super great explanation there Anders! Keep smilin!

On Mon, Oct 9, 2017 at 3:38 PM, The Smiley Coder <thesmileycoder at gmail.com>
wrote:

> Access being a very smart tool will only calculate expressions when
> required. So e.g. if your form shows 10 out of 1000 records, access will
> only calculate the expression (flipname in this case) for the first 10
> (visible) records. Scrolling in the form, will cause access to calculate
> the expression for the following now visible records.
>
> Now the exception to this, is when we force access to calculate the
> expression for all records. You can do this by adding a where clause on the
> expression, or an order by.
>
> So in your first test, the expression was not calculated for all records,
> but only those visible. In your second test, adding the order by, caused
> access to calculated the expression for ALL records (how else would it know
> the sort order).
> Then you added something to the where clause, >="A". This essentialy
> filtered out the records where name was Null, and thus those records where
> never passed to your function, thus the function did not error.
>
> I hope that helps to explain what happened.
>
> In cases where I might potentially pass a null value into a function, I
> will usually set the input data type to variant, and then check it for null
> in the function itself, and decide accordingly. Or alternatively, filter
> out the null values, since they probably shouldn't be selectable in the
> dropdown anyway.
>
> Best regards
> Anders Ebro // TheSmileyCoder <http://www.thesmileycoder.com>
> Access MVP 2014-2018
> <http://mvp.microsoft.com/en-us/mvp/Anders%20Ebro-5000469>
>
> On Sat, Oct 7, 2017 at 5:22 AM, Rocky Smolin <rockysmolin at bchacc.com>
> wrote:
>
> > Dan and Paul:
> >
> > I didn't solve the problem but I worked around it in a way that defied
> (to
> > me) explanation.
> >
> > I added the OrderedBy field, the one with the First Name/Last Name, to
> the
> > query. (in the combo box, it's a zero width field).  Then I added a
> > criterion >="A", so that this criterion would retrieve all the records.
> It
> > worked.
> >
> > Then I set the Unique Values property of the query to yes.  It still
> > worked.
> >
> > Finally, I set the Sort on the Flipped Name field to Ascending
> > and...inexplicably...it worked.
> >
> > Now Paul's solution will probably work a treat.  I may try it tomorrow
> out
> > of curiosity.  But like raising children, when I get the behavior I'm
> > looking for I generally stop working the problem.
> >
> > Thank you both for your inputs.
> >
> >
> > Rocky Smolin
> > Beach Access Software
> > 760-683-5777
> > www.bchacc.com
> > www.e-z-mrp.com
> > Skype: rocky.smolin
> >
> >
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Dan
> > Waters
> > Sent: Friday, October 06, 2017 6:28 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Name Flipping
> >
> > Hi Rocky,
> >
> > I would try to resolve this first by removing any sorting from the query
> > you
> > wrote.  Then write a second query which simply selects the output of the
> > first query and let this second query handle the sorting.
> >
> > Use the second query as the data source for the combobox.
> >
> > HTH!
> > Dan
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Rocky Smolin
> > Sent: October 6, 2017 19:57
> > To: 'Access Developers discussion and problem solving'; 'Off Topic'
> > Subject: [AccessD] Name Flipping
> >
> > Dear List:
> >
> >
> >
> > I have a requirement from a client to show a list in a combo box of
> contact
> > names which appear in Sales Order records sorted by last name.
> >
> >
> >
> > Unfortunately the names are entered First Name (space) Last Name.
> >
> >
> >
> > Fortunately this is very consistent.
> >
> >
> >
> > So I wrote a Public function to flip the names and called it in the query
> > which is the Row Source of the combo box like this:
> >
> >
> >
> > Flipped Name: FlipName([fldRAHOrderedBy])
> >
> >
> >
> > And it works perfectly.  Except. When I add the 'Ascending' to the Sort
> > field,  then I get a message "Data Type Mismatch in criteria expression".
> >
> >
> >
> > When I add the 'Ascending' sort to the Flipped Name field the end of the
> > SQL
> > adds the ORDER BY clause:
> >
> >
> >
> > SELECT tblRentalAgreementHeader.fldRAHID, FlipName([fldRAHOrderedBy]) AS
> > FlippedName, tblRentalAgreementHeader.fldRAHOrderNumber AS [Order #],
> > tblCustomer.fldCustomerName AS Customer,
> > tblRentalAgreementHeader.fldRAHDateOut AS [Date Out],
> > Format([DiscountTotal]+Nz(tblRentalAgreementHeader.
> > fldRAHStrikeCharge)+Nz(tb
> > lRentalAgreementHeader.fldRAHDeliveryCharge)+Nz(
> > tblRentalAgreementHeader.fld
> > RAHPickUpCharge)+Nz(tblRentalAgreementHeader.
> > fldRAHSetUpCharge),"Currency")
> > AS [Order $], tblCustomer.fldCustomerInactive AS Inactive,
> > tblRentalAgreementHeader.fldRAHDiscount
> >
> > FROM (tblRentalAgreementHeader LEFT JOIN tblCustomer ON
> > tblRentalAgreementHeader.fldRAHCustomerID = tblCustomer.fldCustomerID)
> > LEFT
> > JOIN qryDailyBookings ON tblRentalAgreementHeader.fldRAHID =
> > qryDailyBookings.fldRAHID
> >
> > ORDER BY FlipName([fldRAHOrderedBy]);
> >
> >
> >
> > Which apparently is causing it to barf because the query runs fine
> without
> > the ORDER BY.
> >
> >
> >
> > Does anyone know how to work around this?
> >
> >
> >
> > MTIA,
> >
> >
> >
> >
> >
> > Rocky Smolin
> >
> > Beach Access Software
> >
> > 760-683-5777
> >
> >  <http://www.bchacc.com> www.bchacc.com
> >
> >  <http://www.e-z-mrp.com> www.e-z-mrp.com
> >
> > Skype: rocky.smolin
> >
> >
> >
> >
> >
> > --
> > 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
> >
> > --
> > 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
>