Access 2003-Criteria Expression

[Guest]

I have a data base that was created in Access 97. I am currently using the
database in Access 2003 and getting an error on a query expression that
worked in 97. The parameter is: [Type in License Year (2003/2004):] . This
is set as a text box. When the user enters information 2003/2004, an error
message appears with "The expression is typed incorrectly, or it is too
complex to be evaluated." If I enter the data directly in the query in
Design View and Run the message is "Data type mismatch in criteria
expression." All this works in Access 97. What has changed in Access 2003?
What do I change to get this to work in Access 2003?

Thanks for your help.
CN

 

[Allen Browne]

JET 4 (the query engine in Access 2000 and later) is less capable of
determining the data type of parameters and calculated expressions than the
older version of JET was.

To avoid the problem, explicitly declare the parameter so Access understands
the data type. For example, if this parameter is in the Criteria row under a
field of type Number (size Long Integer), choose Parameters on the Query
menu, and enter this row into the dialog:
[Type in License Year (2003/2004):] Long

If the query is for criteria of a calculated fields, typecast the
calculation. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Guest]

I have the Parameter declared as Text under the Query - Parameters. That was
done in the original database. I tried removing the Query-Parameter setting
and still the erros.

CN

JET 4 (the query engine in Access 2000 and later) is less capable of
determining the data type of parameters and calculated expressions than the
older version of JET was.

To avoid the problem, explicitly declare the parameter so Access understands
the data type. For example, if this parameter is in the Criteria row under a
field of type Number (size Long Integer), choose Parameters on the Query
menu, and enter this row into the dialog:
[Type in License Year (2003/2004):] Long

If the query is for criteria of a calculated fields, typecast the
calculation. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a data base that was created in Access 97. I am currently using the
database in Access 2003 and getting an error on a query expression that
worked in 97. The parameter is: [Type in License Year (2003/2004):] .
This
is set as a text box. When the user enters information 2003/2004, an
error
message appears with "The expression is typed incorrectly, or it is too
complex to be evaluated." If I enter the data directly in the query in
Design View and Run the message is "Data type mismatch in criteria
expression." All this works in Access 97. What has changed in Access
2003?
What do I change to get this to work in Access 2003?

Thanks for your help.
CN

 

[Allen Browne]

Firstly, temporarily remove the parameter from the query, and see if it
still errors. You need to know whether this is the cause or if it is
something else.

If that clears the problem, tell us about the field you are placing this
parameter under. It is a calculated field, or a table field? If it is a
table field, open the table in design view and see what Data type it is. If
it is a calculated field, typecast as in the previous link.

If the problem remains, switch the query to SQL View (View menu), and paste
the query statement here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the Parameter declared as Text under the Query - Parameters. That
was
done in the original database. I tried removing the Query-Parameter
setting
and still the erros.

CN

JET 4 (the query engine in Access 2000 and later) is less capable of
determining the data type of parameters and calculated expressions than
the
older version of JET was.

To avoid the problem, explicitly declare the parameter so Access
understands
the data type. For example, if this parameter is in the Criteria row
under a
field of type Number (size Long Integer), choose Parameters on the Query
menu, and enter this row into the dialog:
[Type in License Year (2003/2004):] Long

If the query is for criteria of a calculated fields, typecast the
calculation. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

I have a data base that was created in Access 97. I am currently using
the
database in Access 2003 and getting an error on a query expression that
worked in 97. The parameter is: [Type in License Year (2003/2004):] .
This
is set as a text box. When the user enters information 2003/2004, an
error
message appears with "The expression is typed incorrectly, or it is too
complex to be evaluated." If I enter the data directly in the query in
Design View and Run the message is "Data type mismatch in criteria
expression." All this works in Access 97. What has changed in Access
2003?
What do I change to get this to work in Access 2003?

 

[Guest]

Thank you for the help. Cleared parameter and expression. Saved the query
and re-opened and replaced parameter and expression. It now works.
CN

Firstly, temporarily remove the parameter from the query, and see if it
still errors. You need to know whether this is the cause or if it is
something else.

If that clears the problem, tell us about the field you are placing this
parameter under. It is a calculated field, or a table field? If it is a
table field, open the table in design view and see what Data type it is. If
it is a calculated field, typecast as in the previous link.

If the problem remains, switch the query to SQL View (View menu), and paste
the query statement here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the Parameter declared as Text under the Query - Parameters. That
was
done in the original database. I tried removing the Query-Parameter
setting
and still the erros.

CN

JET 4 (the query engine in Access 2000 and later) is less capable of
determining the data type of parameters and calculated expressions than
the
older version of JET was.

To avoid the problem, explicitly declare the parameter so Access
understands
the data type. For example, if this parameter is in the Criteria row
under a
field of type Number (size Long Integer), choose Parameters on the Query
menu, and enter this row into the dialog:
[Type in License Year (2003/2004):] Long

If the query is for criteria of a calculated fields, typecast the
calculation. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

I have a data base that was created in Access 97. I am currently using
the
database in Access 2003 and getting an error on a query expression that
worked in 97. The parameter is: [Type in License Year (2003/2004):] .
This
is set as a text box. When the user enters information 2003/2004, an
error
message appears with "The expression is typed incorrectly, or it is too
complex to be evaluated." If I enter the data directly in the query in
Design View and Run the message is "Data type mismatch in criteria
expression." All this works in Access 97. What has changed in Access
2003?
What do I change to get this to work in Access 2003?

 

[Guest]

Thank you for the help. Cleared parameter and expression. Saved the query
and re-opened and replaced parameter and expression. It now works.
CN

Firstly, temporarily remove the parameter from the query, and see if it
still errors. You need to know whether this is the cause or if it is
something else.

If that clears the problem, tell us about the field you are placing this
parameter under. It is a calculated field, or a table field? If it is a
table field, open the table in design view and see what Data type it is. If
it is a calculated field, typecast as in the previous link.

If the problem remains, switch the query to SQL View (View menu), and paste
the query statement here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the Parameter declared as Text under the Query - Parameters. That
was
done in the original database. I tried removing the Query-Parameter
setting
and still the erros.

CN

JET 4 (the query engine in Access 2000 and later) is less capable of
determining the data type of parameters and calculated expressions than
the
older version of JET was.

To avoid the problem, explicitly declare the parameter so Access
understands
the data type. For example, if this parameter is in the Criteria row
under a
field of type Number (size Long Integer), choose Parameters on the Query
menu, and enter this row into the dialog:
[Type in License Year (2003/2004):] Long

If the query is for criteria of a calculated fields, typecast the
calculation. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

I have a data base that was created in Access 97. I am currently using
the
database in Access 2003 and getting an error on a query expression that
worked in 97. The parameter is: [Type in License Year (2003/2004):] .
This
is set as a text box. When the user enters information 2003/2004, an
error
message appears with "The expression is typed incorrectly, or it is too
complex to be evaluated." If I enter the data directly in the query in
Design View and Run the message is "Data type mismatch in criteria
expression." All this works in Access 97. What has changed in Access
2003?
What do I change to get this to work in Access 2003?