4 Replies Latest reply on Aug 3, 2006 8:15 AM by Newsgroup_User

    need help with a recordset query

    Level 7
      I'm building a search page in ASP which will look at a specific table in
      an Access database. The users can search with different criteria.

      The table has a date field, which will contain people's date of birth.

      In the search box I'd like the user to be able to search for anyone that
      was born in 1930 for example. They'll just type a year into the form field.

      However on my results page I'm getting the error message "Application
      uses a value of the wrong type for the current operation." The line it
      refers to is

      rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param4",
      135, 1, -1, rsResults__ParamYear) ' adDBTimeStamp

      the sql for the query is:

      SELECT *
      FROM tblCerts
      WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
      LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE ParamYear)

      I can put in a default year and click the test button in the recordset
      dialog box and the correct records are returned. But on the page itself
      I get the error message.

      Please could someone tell me where I've gone wrong.
      Thanks

      Vix
        • 1. Re: need help with a recordset query
          Level 7
          Try this in your sql statement,

          SELECT *
          FROM tblCerts
          WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
          LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE #ParamYear#)

          Dave


          "Vix" <user@example.net> wrote in message
          news:eapsjo$e7c$1@forums.macromedia.com...
          > I'm building a search page in ASP which will look at a specific table in
          > an Access database. The users can search with different criteria.
          >
          > The table has a date field, which will contain people's date of birth.
          >
          > In the search box I'd like the user to be able to search for anyone that
          > was born in 1930 for example. They'll just type a year into the form
          field.
          >
          > However on my results page I'm getting the error message "Application
          > uses a value of the wrong type for the current operation." The line it
          > refers to is
          >
          > rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param4",
          > 135, 1, -1, rsResults__ParamYear) ' adDBTimeStamp
          >
          > the sql for the query is:
          >
          > SELECT *
          > FROM tblCerts
          > WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
          > LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE ParamYear)
          >
          > I can put in a default year and click the test button in the recordset
          > dialog box and the correct records are returned. But on the page itself
          > I get the error message.
          >
          > Please could someone tell me where I've gone wrong.
          > Thanks
          >
          > Vix


          • 2. Re: need help with a recordset query
            Level 7
            It doesn't like that. Tells me I have a syntax error.

            Baxter wrote:
            > Try this in your sql statement,
            >
            > SELECT *
            > FROM tblCerts
            > WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
            > LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE #ParamYear#)
            >
            > Dave
            >
            >
            > "Vix" <user@example.net> wrote in message
            > news:eapsjo$e7c$1@forums.macromedia.com...
            >
            >>I'm building a search page in ASP which will look at a specific table in
            >>an Access database. The users can search with different criteria.
            >>
            >>The table has a date field, which will contain people's date of birth.
            >>
            >>In the search box I'd like the user to be able to search for anyone that
            >>was born in 1930 for example. They'll just type a year into the form
            >
            > field.
            >
            >>However on my results page I'm getting the error message "Application
            >>uses a value of the wrong type for the current operation." The line it
            >>refers to is
            >>
            >>rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param4",
            >>135, 1, -1, rsResults__ParamYear) ' adDBTimeStamp
            >>
            >>the sql for the query is:
            >>
            >>SELECT *
            >>FROM tblCerts
            >>WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
            >>LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE ParamYear)
            >>
            >>I can put in a default year and click the test button in the recordset
            >>dialog box and the correct records are returned. But on the page itself
            >> I get the error message.
            >>
            >>Please could someone tell me where I've gone wrong.
            >>Thanks
            >>
            >>Vix
            >
            >
            >
            • 3. Re: need help with a recordset query
              Level 7
              Why do you have this in your sql
              AND ((Year([EventDate]))
              Year is a reserved word in Access so if you change the field name Year to
              EventYear
              This should work
              SELECT *
              FROM tblCerts
              WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
              LIKE %ParamFirstNames% AND EventYear LIKE #ParamYear#

              Dave

              "Vix" <user@example.net> wrote in message
              news:eas8n0$fjc$1@forums.macromedia.com...
              > It doesn't like that. Tells me I have a syntax error.
              >
              > Baxter wrote:
              > > Try this in your sql statement,
              > >
              > > SELECT *
              > > FROM tblCerts
              > > WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND
              FirstNames
              > > LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE #ParamYear#)
              > >
              > > Dave
              > >
              > >
              > > "Vix" <user@example.net> wrote in message
              > > news:eapsjo$e7c$1@forums.macromedia.com...
              > >
              > >>I'm building a search page in ASP which will look at a specific table in
              > >>an Access database. The users can search with different criteria.
              > >>
              > >>The table has a date field, which will contain people's date of birth.
              > >>
              > >>In the search box I'd like the user to be able to search for anyone that
              > >>was born in 1930 for example. They'll just type a year into the form
              > >
              > > field.
              > >
              > >>However on my results page I'm getting the error message "Application
              > >>uses a value of the wrong type for the current operation." The line it
              > >>refers to is
              > >>
              > >>rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param4",
              > >>135, 1, -1, rsResults__ParamYear) ' adDBTimeStamp
              > >>
              > >>the sql for the query is:
              > >>
              > >>SELECT *
              > >>FROM tblCerts
              > >>WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
              > >>LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE ParamYear)
              > >>
              > >>I can put in a default year and click the test button in the recordset
              > >>dialog box and the correct records are returned. But on the page itself
              > >> I get the error message.
              > >>
              > >>Please could someone tell me where I've gone wrong.
              > >>Thanks
              > >>
              > >>Vix
              > >
              > >
              > >


              • 4. Re: need help with a recordset query
                Level 7
                I didn't realise that Year was an Access only word - thanks.

                Baxter wrote:
                > Why do you have this in your sql
                > AND ((Year([EventDate]))
                > Year is a reserved word in Access so if you change the field name Year to
                > EventYear
                > This should work
                > SELECT *
                > FROM tblCerts
                > WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
                > LIKE %ParamFirstNames% AND EventYear LIKE #ParamYear#
                >
                > Dave
                >
                > "Vix" <user@example.net> wrote in message
                > news:eas8n0$fjc$1@forums.macromedia.com...
                >
                >>It doesn't like that. Tells me I have a syntax error.
                >>
                >>Baxter wrote:
                >>
                >>>Try this in your sql statement,
                >>>
                >>> SELECT *
                >>> FROM tblCerts
                >>> WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND
                >
                > FirstNames
                >
                >>> LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE #ParamYear#)
                >>>
                >>>Dave
                >>>
                >>>
                >>>"Vix" <user@example.net> wrote in message
                >>>news:eapsjo$e7c$1@forums.macromedia.com...
                >>>
                >>>
                >>>>I'm building a search page in ASP which will look at a specific table in
                >>>>an Access database. The users can search with different criteria.
                >>>>
                >>>>The table has a date field, which will contain people's date of birth.
                >>>>
                >>>>In the search box I'd like the user to be able to search for anyone that
                >>>>was born in 1930 for example. They'll just type a year into the form
                >>>
                >>>field.
                >>>
                >>>
                >>>>However on my results page I'm getting the error message "Application
                >>>>uses a value of the wrong type for the current operation." The line it
                >>>>refers to is
                >>>>
                >>>>rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param4",
                >>>>135, 1, -1, rsResults__ParamYear) ' adDBTimeStamp
                >>>>
                >>>>the sql for the query is:
                >>>>
                >>>>SELECT *
                >>>
                >>>>FROM tblCerts
                >>>
                >>>>WHERE Type LIKE ParamType AND Surname LIKE %ParamSurname% AND FirstNames
                >>>>LIKE %ParamFirstNames% AND ((Year([EventDate])) LIKE ParamYear)
                >>>>
                >>>>I can put in a default year and click the test button in the recordset
                >>>>dialog box and the correct records are returned. But on the page itself
                >>>> I get the error message.
                >>>>
                >>>>Please could someone tell me where I've gone wrong.
                >>>>Thanks
                >>>>
                >>>>Vix
                >>>
                >>>
                >>>
                >
                >