Howto Get TOP to select 1 record!

[Thom]

Hi all! What's wrong with this SQL statement using the TOP element?

mySQL = "SELECT TOP 1 Used FROM TestNumbers WHERE Used = No ORDER BY TestNumber"

What I'm trying to do is get the FIRST Testnumber that is NOT being used(ie Used field = No) in the "TestNumbers" Table and then assign the current test that number! Can't we use TOP in Access?

The error I'm getting is "Syntax error in FROM statement". Any help would be appreciated! Thanks!

[Thom]

A further followup:

SQL = "SELECT MIN(Number) FROM Available WHERE Used = 'N'"

rs.Open SQL, g_objConnection, , , adCmdTable

Why won't THIS work in Access97? I'm trying to get the FIRST record(ie the lowest numbered one-Where Number is the Primary Key)that has NOT been used(ie = N)

I keep getting the USELESS "Syntax error in FROM clause." whenever I try this. Is this a problem with using SQL in Access, or with the recordset?

[Thom]

THANKS for the code JHausmann!! It works like a champ-but ONLY if I set the following code as well:

With rs
.ActiveConnection = g_objConnection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
End With


Boy! I'm getting REAL tired of Access/Jet-I REALLY need to convince the higher-ups to go with SQL Server before I go crazy! Thanks again for the help!

[Thom]

Spoke too soon!(at least partially! <sg>). The following code:

mySQL3 = "UPDATE Tests SET Used = 'Y', SET TestName = '" & TestName & "' WHERE TestNumber = '55'"

WON'T work...it gets the ever-so-helpful 'Syntax error in UPDATE statement! BUT if I do 2 SEPARATE SQL statements, 1 for each part, & then rs.Open each individual SQL statement-they work like magic! Huh? I've gone looking for the stray comma or other grammar problem(taking Tom's advice that 99% of SQL errors are this sort) but I can't find it!

[JHausmann]

You only use the word "SET" once in an update statement.

[Thom]

ARRGH! NOW you tell me! <sg> That pertinent little fact is NOWHERE to be found in ANY of the 6 books that I've read thru! It finally works the way I wanted it to at the beginning! Now all that's left is to figure out some basic reports to build from the test results & I'm FINALLY going to be done. Thanks a million for the sage advice JHausmann!