[AccessD] Name Flipping

[Paul Hartland]

Forgot to mention the (possible) easy work around as well, could of tried

ORDER BY CStr(FlipName([fldRAHOrderedBy]));



On 7 Oct 2017 04:23, "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
>