A2010, split .mdb, local copy of FE
Form bound to inner join query
Datasheet view
I'm *sure* I've done this before....

I'm getting an error when I click on the column auto-filter of one
field - and I've run out of ideas what to look for.

Error text:
Syntax error (missing operator) in query expression 'Mix ID'.

After I dismiss the error dialog, the auto-filter menu appears, without
the data value select list. The remaining filter options all work (at
least the ones I've tried).

I suspect possibly something has happened in the BE ... unfortunately
the oldest available backup on the file server throws the same error. I
tried several older versions of the FE; all threw the same error. The
error is happening on more than one workstation. I'm pretty sure I used
this auto-filter earlier this morning, without error. The column
(control)'s row source is [Mix Designs].[Mix ID].

Asking for trouble-shooting suggestions....

The form's bound query (several columns ...'d out for brevity):

SELECT [Concrete Tests].PlantID, [Concrete Tests].LotID, ...,
[Concrete Tests].MixID, ..., [Concrete Tests].CommentID,
[Mix Designs].[Mix Name], [Mix Designs].[Mix Description],
IIf([PlasticUnitWt]>0,([DesignWt]-[PlasticUnitWt])/[DesignWt]+0.06,Null)
AS AirContentUW, [Mix Designs].[Mix ID], Comments.Comment
FROM [Mix Designs] INNER JOIN (Comments RIGHT JOIN
[Concrete Tests] ON Comments.CommentID = [Concrete Tests].CommentID) ON
[Mix Designs].MixID = [Concrete Tests].MixID
ORDER BY [Concrete Tests].SampleDate;


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On 31/01/2011 23:21:41, "Clif McIrvin" wrote:
There seem to be references to MixID without a space and [Mix ID] with a
space. Is this correct? As a matter of principal, I would strongly advise
neverhaving spaces in field names either in tables or in forms. It saves all
the squre brackets. IMO, MixDesigns and ConctreteTests are perfectly
readable. Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote


Yes, CamelCase is perfectly readable. I have gone back and re-done some
of what was improperly done way back early in the learning curve, but
that task seems hard to find time for, somehow <g>.

Yes, MixID and [Mix ID] (different columns, different data - someday I'd
like to find time to get that renamed) could lead to confusion; but why
would this form work for months and then suddenly stop? That's the part
that has me stumped.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

There is a terrific shareware tool named "FindAndReplace" from Rick Fisher
at Rickworld.com. It makes the process of globally renaming objects a piece
of cake.

There are other tools but I haven't tried them. Others have recommended
them in the newsgroups:

Speed Ferret from Black Moshannon Software
another tool also named Find And Replace but it's a commercial product.

Larry

"Clif McIrvin" <clare.nomail (AT) gmail (DOT) com> wrote