linking to a query with a calculated field

[Bkndbrown]

I have queries with calculated fields. Such as count a field on a table or concatenating a string together. It works just fine and I can sort no problems. When I link to it on a form I cannot sort by that field anymore. I get either syntax error (missing operator) in query expression.

[June7]

What method are you using to sort? You are applying sort criteria to form? In the RecordSource SQL?

I cannot replicate issue.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Bkndbrown]

I have attached a way stripped down version of the database with one record of fictional data

[Bkndbrown]

The error is on the form if you click the down arrow to sort.

[June7]

A little hard to test sorting with only one record.

Not seeing any sort parameters in queries or form design.

What is purpose of this form? Why does its RecordSource include tables and queries? Usually, a form can do data entry/edit for only one table. Data in aggregate queries cannot be edited. Perhaps you should be build a report.

[Bkndbrown]

It will be a split form with a datasheet on top with a bunch of data shown then when you select a record it will select that record on a subform where I will do data entry for one record in one table. However it would be nice if I could select the drop down to perform a sort or search for one name or other parameter without an error.

I realize that it would be easier to do a sort with more data but it sorts A to Z just fine my purpose was to show the error that I receive and the structure.

[June7]

What error? What should I do to trigger error? I tried the ribbon and right click shortcut menu sorts and both work.

[Bkndbrown]

Open the form then click on the drop down arrow next to the "name" column header of the datasheet where you would click if you wanted change the sort option for the table or if you wanted to filter the table down to one record. You can do this with all threemi of the columns and you get the same error. The error is "syntax error (missing operator) in query expression"

[June7]

Okay, got that. The error pops up and I just click OK, the popup closes and the sort options open.

Change the queries so the alias names do not have spaces.

The OutputSheet query is not needed. Sponsor should be unique in SponsorDataT - GROUP BY is not needed.

Change the form RecordSource query to:

SELECT SponsorDataT.PersonalID, AdultDepQ.CountOfPersonalID AS AdultDep, MinorDepQ.CountOfPersonalID AS MinorDep, [SponsorLastName] & ", " & [SponsorFirstName] & " " & [SponsorMiddleInitial] AS FullName FROM (SponsorDataT LEFT JOIN AdultDepQ ON SponsorDataT.PersonalID = AdultDepQ.PersonalID) LEFT JOIN MinorDepQ ON SponsorDataT.PersonalID = MinorDepQ.PersonalID;