[AccessD] Name Flipping

[The Smiley Coder]

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
>