Help with SQL query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Help with SQL query

  1. #1
    Billy Guest

    Help with SQL query


    Could someone show me the sql query that would show the highest
    number in an Access database within a field called "number".

    I have an Access database called video, and a field within that
    database called Number. I want whatever the highest number (record) that
    is found within that field to be listed in a text box called text4.text.

    I can't seem to get the right combination of "select".

    thanks for any help.



  2. #2
    Arthur Wood Guest

    Re: Help with SQL query


    How about

    Select Max(Number) from Video

    Arthur Wood


    "Billy" <skateworks@mindspring.com> wrote:
    >
    >Could someone show me the sql query that would show the highest
    >number in an Access database within a field called "number".
    >
    >I have an Access database called video, and a field within that
    >database called Number. I want whatever the highest number (record) that
    >is found within that field to be listed in a text box called text4.text.
    >
    >I can't seem to get the right combination of "select".
    >
    >thanks for any help.
    >
    >



  3. #3
    billy Guest

    Re: Help with SQL query


    Thanks, I think that may work, but how do i get it to print that result in
    a textbox called text4.txt. I guess I have to put the SQL code in the
    command button code window, right?

    I have a command button called "command1" and a textbox called text4.txt"
    I want to click on the command button and have it print the highest
    number in the text box......

    thanks for your help

    billy

    "Arthur Wood" <wooda@nospam.com> wrote:
    >
    >How about
    >
    >Select Max(Number) from Video
    >
    >Arthur Wood
    >
    >
    >"Billy" <skateworks@mindspring.com> wrote:
    >>
    >>Could someone show me the sql query that would show the highest
    >>number in an Access database within a field called "number".
    >>
    >>I have an Access database called video, and a field within that
    >>database called Number. I want whatever the highest number (record) that
    >>is found within that field to be listed in a text box called text4.text.
    >>
    >>I can't seem to get the right combination of "select".
    >>
    >>thanks for any help.
    >>
    >>

    >



  4. #4
    Arthur Wood Guest

    Re: Help with SQL query


    Are you using ADO or DAO to connect to your database?

    you will need to use the SQL that I showed to create a recordset (will only
    have one record), and then set the Texct of the Text box to the value in
    the Recordset's Field(0)

    using ADO, with an open Connection Object:

    Dim rs as ADODB.Recordset
    Dim cnn as ADODB.Connection

    Set cnn = New Connection
    cnn.ConnectionString = <Connection string for your database> (replace here
    as appropriate)
    cnn.Open

    Set rs = New Recordset

    rs.OPen "Select Max(Number) from Video", cnn

    if not rs.Eof then

    Text4.Text = rs.Field(0)
    end if




    there would be equivalent code if you wnat to use DAO instead.

    Arthur Wood


    "billy" <skateworks@mindspring.com> wrote:
    >
    >Thanks, I think that may work, but how do i get it to print that result

    in
    >a textbox called text4.txt. I guess I have to put the SQL code in the
    >command button code window, right?
    >
    >I have a command button called "command1" and a textbox called text4.txt"
    >I want to click on the command button and have it print the highest
    >number in the text box......
    >
    >thanks for your help
    >
    >billy
    >
    >"Arthur Wood" <wooda@nospam.com> wrote:
    >>
    >>How about
    >>
    >>Select Max(Number) from Video
    >>
    >>Arthur Wood
    >>
    >>
    >>"Billy" <skateworks@mindspring.com> wrote:
    >>>
    >>>Could someone show me the sql query that would show the highest
    >>>number in an Access database within a field called "number".
    >>>
    >>>I have an Access database called video, and a field within that
    >>>database called Number. I want whatever the highest number (record) that
    >>>is found within that field to be listed in a text box called text4.text.
    >>>
    >>>I can't seem to get the right combination of "select".
    >>>
    >>>thanks for any help.
    >>>
    >>>

    >>

    >



  5. #5
    billy Guest

    Re: Help with SQL query


    Thanks.
    I am using ADO. At least I think I am. Still confused. What I do is put
    a
    control on the form and it always say ADODC1 from the components list.
    I use that to connect to my Access database. Now in regards to the code

    you have below, do I place that in the code window of the command button,
    or the code window for the form itself?

    Billy
    =============



    "Arthur Wood" <wooda@nospam.com> wrote:
    >
    >Are you using ADO or DAO to connect to your database?
    >
    >you will need to use the SQL that I showed to create a recordset (will only
    >have one record), and then set the Texct of the Text box to the value in
    >the Recordset's Field(0)
    >
    >using ADO, with an open Connection Object:
    >
    >Dim rs as ADODB.Recordset
    >Dim cnn as ADODB.Connection
    >
    >Set cnn = New Connection
    >cnn.ConnectionString = <Connection string for your database> (replace here
    >as appropriate)
    >cnn.Open
    >
    >Set rs = New Recordset
    >
    >rs.OPen "Select Max(Number) from Video", cnn
    >
    >if not rs.Eof then
    >
    > Text4.Text = rs.Field(0)
    >end if
    >
    >
    >
    >
    >there would be equivalent code if you wnat to use DAO instead.
    >
    >Arthur Wood
    >
    >
    >"billy" <skateworks@mindspring.com> wrote:
    >>
    >>Thanks, I think that may work, but how do i get it to print that result

    >in
    >>a textbox called text4.txt. I guess I have to put the SQL code in the


    >>command button code window, right?
    >>
    >>I have a command button called "command1" and a textbox called text4.txt"
    >>I want to click on the command button and have it print the highest
    >>number in the text box......
    >>
    >>thanks for your help
    >>
    >>billy
    >>
    >>"Arthur Wood" <wooda@nospam.com> wrote:
    >>>
    >>>How about
    >>>
    >>>Select Max(Number) from Video
    >>>
    >>>Arthur Wood
    >>>
    >>>
    >>>"Billy" <skateworks@mindspring.com> wrote:
    >>>>
    >>>>Could someone show me the sql query that would show the highest
    >>>>number in an Access database within a field called "number".
    >>>>
    >>>>I have an Access database called video, and a field within that
    >>>>database called Number. I want whatever the highest number (record)

    that
    >>>>is found within that field to be listed in a text box called text4.text.
    >>>>
    >>>>I can't seem to get the right combination of "select".
    >>>>
    >>>>thanks for any help.
    >>>>
    >>>>
    >>>

    >>

    >



  6. #6
    Billy Guest

    Re: Help with SQL query


    It works, with the execption of one thing......it doesn't bring up the
    highest number in the list......any ideas? The last video number tape
    I entered into the Database was 339. It pulls up the number 99.

    In the SQL line of:

    rs.Open "Select Max(number) from Video, cnn

    is there a sort or something I could do to get it to truly
    reflect the highest number?

    billy

  7. #7
    Dean Earley Guest

    Re: Help with SQL query

    > It works, with the execption of one thing......it doesn't bring up the
    > highest number in the list......any ideas? The last video number tape
    > I entered into the Database was 339. It pulls up the number 99.
    >
    > In the SQL line of:
    >
    > rs.Open "Select Max(number) from Video, cnn
    >
    > is there a sort or something I could do to get it to truly
    > reflect the highest number?


    Your 'number' is not a number, it is a string which makes 99 higher than 339 (think about it alphabetically)

    See my earlier post about changing the data type. AutoNumber will do away with the need to work out the next highest.

    --
    Dean Earley (dean.earley@icode.co.uk)
    i-Catcher Development Team

    iCode Systems



  8. #8
    challis Guest

    Re: Help with SQL query


    SELECT MAX(CAST(Number as int)) HIGHEST
    FROM TABLE


    "Dean Earley" <dean.earley@icode.co.uk> wrote:
    >> It works, with the execption of one thing......it doesn't bring up the
    >> highest number in the list......any ideas? The last video number tape
    >> I entered into the Database was 339. It pulls up the number 99.
    >>
    >> In the SQL line of:
    >>
    >> rs.Open "Select Max(number) from Video, cnn
    >>
    >> is there a sort or something I could do to get it to truly
    >> reflect the highest number?

    >
    >Your 'number' is not a number, it is a string which makes 99 higher than

    339 (think
    >about it alphabetically)
    >
    >See my earlier post about changing the data type. AutoNumber will do away

    with the need
    >to work out the next highest.
    >
    >--
    >Dean Earley (dean.earley@icode.co.uk)
    >i-Catcher Development Team
    >
    >iCode Systems
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center