Jet Database Error

Rodney D. Lester · Jul 24, 2004

Error Message:

The Microsoft Jet database engine does not recognize '[Forms]![Test]![Date]'
as a valid field name or expression.

I get this when trying to filter a query by the date in a bound text box on
a form.

What am i doing wrong?

Thanks in advance...

Rodney

Ken Snell · Jul 24, 2004

Post the SQL statement of the query.

Ken Snell · Jul 24, 2004

Also, don't use Date as a field or control name. It's a reserved word in
ACCESS, and ACCESS can become quite confused. See KB article number 286335
for more info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

Rodney D. Lester · Jul 24, 2004

Yes, I forgot about that... I changed the Date field to RCDate and it still gives the error?

Here's the SQL:

TRANSFORM Count(RollCall.MemberID) AS CountOfMemberID
SELECT opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name, Ministry.ID, Count(RollCall.MemberID) AS Totals
FROM Ministry INNER JOIN (SubMinistry INNER JOIN (opGrades INNER JOIN (Roster INNER JOIN RollCall ON Roster.MemberID = RollCall.MemberID) ON opGrades.Grade = Roster.Grade) ON SubMinistry.Grade = RollCall.Grade) ON Ministry.ID = SubMinistry.MinistryID
WHERE (((RollCall.RCDate)=[Forms]![Test]![RCDate]))
GROUP BY opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name, Ministry.ID, RollCall.RCDate
PIVOT RollCall.Service;

Thanks

Ken Snell said:
Also, don't use Date as a field or control name. It's a reserved word in
ACCESS, and ACCESS can become quite confused. See KB article number 286335
for more info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>

Rodney D. Lester said:

Error Message:

The Microsoft Jet database engine does not recognize '[Forms]![Test]![Date]'
as a valid field name or expression.

I get this when trying to filter a query by the date in a bound text box on
a form.

What am i doing wrong?

Thanks in advance...

Rodney

Click to expand...

Ken Snell · Jul 24, 2004

I don't see anything out of line (except you've double included
RollCall.RCDate in the GROUP BY clause). I'm guessing that the query has
already become "confused" by the earlier use of Date.

Try creating an entirely new query using this new SQL statement.

--

Ken Snell
<MS ACCESS MVP>

Yes, I forgot about that... I changed the Date field to RCDate and it still
gives the error?

Here's the SQL:

TRANSFORM Count(RollCall.MemberID) AS CountOfMemberID
SELECT opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, Count(RollCall.MemberID) AS Totals
FROM Ministry INNER JOIN (SubMinistry INNER JOIN (opGrades INNER JOIN
(Roster INNER JOIN RollCall ON Roster.MemberID = RollCall.MemberID) ON
opGrades.Grade = Roster.Grade) ON SubMinistry.Grade = RollCall.Grade) ON
Ministry.ID = SubMinistry.MinistryID
WHERE (((RollCall.RCDate)=[Forms]![Test]![RCDate]))
GROUP BY opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, RollCall.RCDate
PIVOT RollCall.Service;

Thanks

Ken Snell said:
Also, don't use Date as a field or control name. It's a reserved word in
ACCESS, and ACCESS can become quite confused. See KB article number 286335
for more info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>

Rodney D. Lester said:

Error Message:

The Microsoft Jet database engine does not recognize '[Forms]![Test]![Date]'
as a valid field name or expression.

I get this when trying to filter a query by the date in a bound text box on
a form.

What am i doing wrong?

Thanks in advance...

Rodney

Click to expand...

Wayne Morgan · Jul 24, 2004

PMJI,

A couple of simple starting points. 1) The form needs to be open when you run the query. 2) Do the control on the form and the field have the same name? If so, try renaming the control on the form to txtRCDate.

--
Wayne Morgan
MS Access MVP

Yes, I forgot about that... I changed the Date field to RCDate and it still gives the error?

Here's the SQL:

TRANSFORM Count(RollCall.MemberID) AS CountOfMemberID
SELECT opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name, Ministry.ID, Count(RollCall.MemberID) AS Totals
FROM Ministry INNER JOIN (SubMinistry INNER JOIN (opGrades INNER JOIN (Roster INNER JOIN RollCall ON Roster.MemberID = RollCall.MemberID) ON opGrades.Grade = Roster.Grade) ON SubMinistry.Grade = RollCall.Grade) ON Ministry.ID = SubMinistry.MinistryID
WHERE (((RollCall.RCDate)=[Forms]![Test]![RCDate]))
GROUP BY opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name, Ministry.ID, RollCall.RCDate
PIVOT RollCall.Service;

Larry Linson · Jul 24, 2004

Also, is this SQL being built / handled in code, or run directly from the
Query Builder window? If from code, please show the code... because the
syntax may not be correct. If from the Query Builder window, you've gotten
good advice from Ken and Wayne.

Larry Linson
Microsoft Access MVP

Yes, I forgot about that... I changed the Date field to RCDate and it still
gives the error?

Here's the SQL:

TRANSFORM Count(RollCall.MemberID) AS CountOfMemberID
SELECT opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, Count(RollCall.MemberID) AS Totals
FROM Ministry INNER JOIN (SubMinistry INNER JOIN (opGrades INNER JOIN
(Roster INNER JOIN RollCall ON Roster.MemberID = RollCall.MemberID) ON
opGrades.Grade = Roster.Grade) ON SubMinistry.Grade = RollCall.Grade) ON
Ministry.ID = SubMinistry.MinistryID
WHERE (((RollCall.RCDate)=[Forms]![Test]![RCDate]))
GROUP BY opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, RollCall.RCDate
PIVOT RollCall.Service;

Thanks

Ken Snell said:
Also, don't use Date as a field or control name. It's a reserved word in
ACCESS, and ACCESS can become quite confused. See KB article number 286335
for more info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>

Rodney D. Lester said:

Error Message:

The Microsoft Jet database engine does not recognize '[Forms]![Test]![Date]'
as a valid field name or expression.

I get this when trying to filter a query by the date in a bound text box on
a form.

What am i doing wrong?

Thanks in advance...

Rodney

Click to expand...

Ken Snell · Jul 24, 2004

Good points, Wayne.

--

Ken Snell
<MS ACCESS MVP>

PMJI,

A couple of simple starting points. 1) The form needs to be open when you
run the query. 2) Do the control on the form and the field have the same
name? If so, try renaming the control on the form to txtRCDate.

--
Wayne Morgan
MS Access MVP

Yes, I forgot about that... I changed the Date field to RCDate and it
still gives the error?

Here's the SQL:

TRANSFORM Count(RollCall.MemberID) AS CountOfMemberID
SELECT opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, Count(RollCall.MemberID) AS Totals
FROM Ministry INNER JOIN (SubMinistry INNER JOIN (opGrades INNER JOIN
(Roster INNER JOIN RollCall ON Roster.MemberID = RollCall.MemberID) ON
opGrades.Grade = Roster.Grade) ON SubMinistry.Grade = RollCall.Grade) ON
Ministry.ID = SubMinistry.MinistryID
WHERE (((RollCall.RCDate)=[Forms]![Test]![RCDate]))
GROUP BY opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, RollCall.RCDate
PIVOT RollCall.Service;

Tom Wickerath · Jul 25, 2004

I see something out of line....
The SQL statement starts out with the TRANSFORM keyword. That means that this is a crosstab
query. When you include parameters in a crosstab query, you must define the parameter
explicitly. See the following KB article:

Error When Running Crosstab Query with a Parameter
http://support.microsoft.com/?id=209778

For more information on creating crosstab queries, see

How To Create A Crosstab Query
http://www.access.qbuilt.com/html/crosstab_queries.html

Tom
_________________________________

I don't see anything out of line (except you've double included
RollCall.RCDate in the GROUP BY clause). I'm guessing that the query has
already become "confused" by the earlier use of Date.

Try creating an entirely new query using this new SQL statement.

--

Ken Snell
<MS ACCESS MVP>

_________________________________

Yes, I forgot about that... I changed the Date field to RCDate and it still
gives the error?

Here's the SQL:

TRANSFORM Count(RollCall.MemberID) AS CountOfMemberID
SELECT opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, Count(RollCall.MemberID) AS Totals
FROM Ministry INNER JOIN (SubMinistry INNER JOIN (opGrades INNER JOIN
(Roster INNER JOIN RollCall ON Roster.MemberID = RollCall.MemberID) ON
opGrades.Grade = Roster.Grade) ON SubMinistry.Grade = RollCall.Grade) ON
Ministry.ID = SubMinistry.MinistryID
WHERE (((RollCall.RCDate)=[Forms]![Test]![RCDate]))
GROUP BY opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, RollCall.RCDate
PIVOT RollCall.Service;

Thanks
_________________________________

Also, don't use Date as a field or control name. It's a reserved word in ACCESS, and ACCESS can
become quite confused. See KB article number 286335 for more info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>
_________________________________

Error Message:

The Microsoft Jet database engine does not recognize '[Forms]![Test]![Date]' as a valid field
name or expression.

I get this when trying to filter a query by the date in a bound text box on a form.

What am i doing wrong?

Thanks in advance...

Rodney

Ken Snell · Jul 25, 2004

Ah, good catch. I do not work with crosstab queries at this time, and had
overlooked this. Thanks, Tom.

--

Ken Snell
<MS ACCESS MVP>

Tom Wickerath said:
I see something out of line....
The SQL statement starts out with the TRANSFORM keyword. That means that this is a crosstab
query. When you include parameters in a crosstab query, you must define the parameter
explicitly. See the following KB article:

Error When Running Crosstab Query with a Parameter
http://support.microsoft.com/?id=209778

For more information on creating crosstab queries, see

How To Create A Crosstab Query
http://www.access.qbuilt.com/html/crosstab_queries.html

Tom
_________________________________

I don't see anything out of line (except you've double included
RollCall.RCDate in the GROUP BY clause). I'm guessing that the query has
already become "confused" by the earlier use of Date.

Try creating an entirely new query using this new SQL statement.

--

Ken Snell
<MS ACCESS MVP>

_________________________________

Yes, I forgot about that... I changed the Date field to RCDate and it still
gives the error?

Here's the SQL:

TRANSFORM Count(RollCall.MemberID) AS CountOfMemberID
SELECT opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, Count(RollCall.MemberID) AS Totals
FROM Ministry INNER JOIN (SubMinistry INNER JOIN (opGrades INNER JOIN
(Roster INNER JOIN RollCall ON Roster.MemberID = RollCall.MemberID) ON
opGrades.Grade = Roster.Grade) ON SubMinistry.Grade = RollCall.Grade) ON
Ministry.ID = SubMinistry.MinistryID
WHERE (((RollCall.RCDate)=[Forms]![Test]![RCDate]))
GROUP BY opGrades.AutoID, RollCall.RCDate, Roster.Grade, Ministry.Name,
Ministry.ID, RollCall.RCDate
PIVOT RollCall.Service;

Thanks
_________________________________

Also, don't use Date as a field or control name. It's a reserved word in ACCESS, and ACCESS can
become quite confused. See KB article number 286335 for more info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>
_________________________________

Error Message:

The Microsoft Jet database engine does not recognize

'[Forms]![Test]![Date]' as a valid field