[AccessD] Name Flipping

[Paul Hartland]

It sounds to me like one of the names is causing the problem, I would run
the function on its own to view all the results and maybe paste into excel
or use the function to create a table and then sort the results to see the
top few names, could you also post your function as there may be a simple
work around there as well

Paul

On 7 Oct 2017 01:57, "Rocky Smolin" <rockysmolin at bchacc.com> wrote:

> 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
>