Jet Database Error

R

Rodney D. Lester

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
 
R

Rodney D. Lester

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...
Click to expand...
 
K

Ken Snell

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...
Click to expand...
 
W

Wayne Morgan

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;
 
L

Larry Linson

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...
Click to expand...
 
K

Ken Snell

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;
 
T

Tom Wickerath

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
 
K

Ken Snell

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
Click to expand...
'[Forms]![Test]![Date]' as a valid field
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Chart subform on report 12
query error message - Jet database Engine 1
Date is not a valid expression 9
Problem with jet database 3
Jet error 6
Date Range Criteria for Crosstab Query 6
JET ENGINE ? 2
Subqueries 2

Top