Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access 2003-Criteria Expression

Discussion in 'Information Technology' started by cn, Jul 28, 2006.

  1. cn

    cn
    Expand Collapse
    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
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <cn@discussions.microsoft.com> wrote in message
    news:76750841-F8FC-488C-9CEC-B29E596286F5@microsoft.com...
    >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
     
  4. cn

    cn
    Expand Collapse
    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

    "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" <cn@discussions.microsoft.com> wrote in message
    > news:76750841-F8FC-488C-9CEC-B29E596286F5@microsoft.com...
    > >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

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <cn@discussions.microsoft.com> wrote in message
    news:484C6DEA-9CF0-4E04-9F59-809A2D0988EB@microsoft.com...
    >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" <cn@discussions.microsoft.com> wrote in message
    >> news:76750841-F8FC-488C-9CEC-B29E596286F5@microsoft.com...
    >> >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?
     
  6. cn

    cn
    Expand Collapse
    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

    "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" <cn@discussions.microsoft.com> wrote in message
    > news:484C6DEA-9CF0-4E04-9F59-809A2D0988EB@microsoft.com...
    > >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" <cn@discussions.microsoft.com> wrote in message
    > >> news:76750841-F8FC-488C-9CEC-B29E596286F5@microsoft.com...
    > >> >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?

    >
    >
    >
     

Share This Page