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

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cn" <(E-Mail Removed)> wrote in message
news:76750841-F8FC-488C-9CEC-(E-Mail Removed)...
>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

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

"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "cn" <(E-Mail Removed)> wrote in message
> news:76750841-F8FC-488C-9CEC-(E-Mail Removed)...
> >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
>
>
>

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cn" <(E-Mail Removed)> wrote in message
news:484C6DEA-9CF0-4E04-9F59-(E-Mail Removed)...
>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
>
> "Allen Browne" wrote:
>
>> 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
>>
>> "cn" <(E-Mail Removed)> wrote in message
>> news:76750841-F8FC-488C-9CEC-(E-Mail Removed)...
>> >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?

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

"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "cn" <(E-Mail Removed)> wrote in message
> news:484C6DEA-9CF0-4E04-9F59-(E-Mail Removed)...
> >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
> >
> > "Allen Browne" wrote:
> >
> >> 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
> >>
> >> "cn" <(E-Mail Removed)> wrote in message
> >> news:76750841-F8FC-488C-9CEC-(E-Mail Removed)...
> >> >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?
>
>
>

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

"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "cn" <(E-Mail Removed)> wrote in message
> news:484C6DEA-9CF0-4E04-9F59-(E-Mail Removed)...
> >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
> >
> > "Allen Browne" wrote:
> >
> >> 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
> >>
> >> "cn" <(E-Mail Removed)> wrote in message
> >> news:76750841-F8FC-488C-9CEC-(E-Mail Removed)...
> >> >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?
>
>
>