Datasheet View Column AutoFilter Error

[Clif McIrvin]

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;

 

[Phil]

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;

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

 

[Clif McIrvin]

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;

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

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.

 

[Larry Daugherty]

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

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;

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

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.