[AccessD] Name Flipping

[Dan Waters]

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