Help with SQL query

[Billy]

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.

[Arthur Wood]

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.
>
>

[billy]

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.
>>
>>
>

[Arthur Wood]

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.
>>>
>>>
>>
>

[billy]

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.
>>>>
>>>>
>>>
>>
>

[Billy]

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

[Dean Earley]

> 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

[challis]

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
>
>