[AccessD] Name Flipping

[Rocky Smolin]

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