[AccessD] Name Flipping

[Rocky Smolin]

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